Re: Slow select performance despite seemingly reasonable query plan

On Thu, May 7, 2009 at 11:19 AM, Matthew Wakeling <matthew(at)flymine(dot)org>wrote:

> On Thu, 7 May 2009, David Brain wrote:
>
>> Certainly random access like this index scan can be extremely slow. 2-4
>>> MB/s
>>> is quite reasonable if you're fetching one 8kB block per disc seek - no
>>> more
>>> than 200 per second.
>>>
>>
>> We have read ahead set pretty aggressively high as the SAN seems to
>> 'like' this, given some testing we did:
>>
>> /sbin/blockdev --getra /dev/sdb
>> 16384
>>
>
> Read-ahead won't really help with completely random access.

Thats a shame because it would be really nice to get the entire index into
shared memory or OS cache. Most of the time queries are on data in the past
few months. All of the indexes in the past few months should fit in cache.

Did something happen to get those indexes flushed from the cache? Were they
in the cache before?

> I think a much more interesting line of enquiry will be trying to work out
> what has changed, and why it was fast before.
>
> How much of the data you're accessing are you expecting to be in the OS
> cache?
>
> Is the table you're index scanning on ordered at all? Could that have
> changed recently?

I wrote the application that puts data in that table. Its sort of ordered
by that timestamp. Every five minutes it adds rows in no particular order
that need to be added. The rows that need to be added every five minutes
are ordered by another timestamp that is correlated to but not the same as
the indexed timestamp.

>
>
> That's a thought, I doubt the option is set (I didn't set it and I
>> don't _think_ rhel does by default), however the 'base' directory only
>> contains ~5500 items total, so it's not getting too out of hand.
>>
>
> I think quite a few systems do set it by default now.
>
> Matthew
>
> --
> Me... a skeptic? I trust you have proof?
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>