GiST, caching, and consistency
От | Matthew Wakeling |
---|---|
Тема | GiST, caching, and consistency |
Дата | |
Msg-id | alpine.DEB.2.00.0908041659410.18938@aragorn.flymine.org обсуждение исходный текст |
Ответы |
Re: GiST, caching, and consistency
|
Список | pgsql-performance |
I'm seeing an interesting phenomenon while I'm trying to performance-optimise a GiST index. Basically, running a performance test appears to be the same thing as running a random number generator. For example, here I'm running the same statement eight times in quick succession: > modmine_overlap_test=# \timing > Timing is on. > modmine_overlap_test=# select count(*) from (select * FROM > locatedsequencefeatureoverlappingfeatures limit 1000000) AS a; > count > --------- > 1000000 > (1 row) > > Time: 138583.140 ms > > Time: 153769.152 ms > > Time: 127518.574 ms > > Time: 49629.036 ms > > Time: 70926.034 ms > > Time: 7625.034 ms > > Time: 7382.609 ms > > Time: 7985.379 ms "locatedsequencefeatureoverlappingfeatures" is a view, which performs a join with a GiST index. The machine was otherwise idle, and has plenty of RAM free. Shouldn't the data be entirely in cache the second time I run the statement? However, it's worse than that, because while the long-running statements were running, I saw significant CPU usage in top - more than eight seconds worth. Again, one one test there was no io-wait, but on a subsequent test there was lots of io-wait. How can this be so inconsistent? Matthew -- "Interwoven alignment preambles are not allowed." If you have been so devious as to get this message, you will understand it, and you deserve no sympathy. -- Knuth, in the TeXbook
В списке pgsql-performance по дате отправления: