Re: GiST, caching, and consistency

On Tue, Aug 4, 2009 at 12:06 PM, Matthew Wakeling<matthew(at)flymine(dot)org> wrote:
>
> 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?

Beats me. It looks like the first few queries are pulling stuff into
cache, and then after that it settles down, but I'm not sure why it
takes 5 repetitions to do that. Is the plan changing?

...Robert