Re: Slow select performance despite seemingly reasonable query plan

On Thu, 7 May 2009, David Brain wrote:
> This has been working reasonably well, however in the last few days
> I've been seeing extremely slow performance on what are essentially
> fairly simple 'index hitting' selects on this data.  From the host
> side I see that the postgres query process is mostly in IO wait,
> however there is very little data actually being transferred (maybe
> 2-4 MB/s) - when a different query (say a select count(*) form
> datatable) will yield a sustained 150+ MB/s.

Has there been a performance *change*, or are you just concerned about a
query which doesn't seem to use "enough" disc bandwidth?

> 1. Problem with random versus sequential reads on storage system.

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.

> 3. Problem with the host environment - one suspicion I have here is
> that we are >90% full on the storage drives (ext3), I'm not sure if
> that is impacting performance.

One concern I might have with a big setup like that is how big the
database directory has got, and whether directory lookups are taking time.
Check to see if you have the directory_index option enabled on your ext3
filesystem.

Matthew

--
 The third years are wandering about all worried at the moment because they
 have to hand in their final projects. Please be sympathetic to them, say
 things like "ha-ha-ha", but in a sympathetic tone of voice
                                        -- Computer Science Lecturer