Re: Slow select performance despite seemingly reasonable query plan

From: Matthew Wakeling
Subject: Re: Slow select performance despite seemingly reasonable query plan
Date: ,
Msg-id: alpine.DEB.2.00.0905071545570.2341@aragorn.flymine.org
(view: Whole thread, Raw)
In response to: Slow select performance despite seemingly reasonable query plan  (David Brain)
Responses: Re: Slow select performance despite seemingly reasonable query plan  (David Brain)
List: pgsql-performance


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


pgsql-performance by date:

From: Tom Lane
Date:
Subject: Re: Bad Plan for Questionnaire-Type Query
From: David Blewett
Date:
Subject: Re: Bad Plan for Questionnaire-Type Query