Wildly erratic query performance

From: Eric Schwarzenbach <subscriber(at)blackbrook(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Wildly erratic query performance
Date: 2008-10-31 19:34:48
Message-ID: 490B5DD8.3060905@blackbrook.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I've got a particular query that is giving me ridiculously erratic query
performance. I have the SQL in a pgadmin query window, and from one
execution to another, with no changes, the time it takes varies from
half a second to, well, at least 10 minutes or so at which point I give
up an cancel the query. A typical time is 2-3 seconds, but it's all over
the map. I've seen numbers like 112 seconds for one which returns
without exceeding my patience. In every half a dozen or so execution
there will be one time which is an order of magnitude bigger than the
others. A typical series of executions might be something like 2
seconds, 5 seconds, 8 seconds, 0.5 seconds, 80 seconds, 5 seconds.

Note that the database is running on my local machine, the same machine
I'm running the queries from, and nothing else is using this postgresql
installation. The data in the database is also not changing--there are
no inserts or updates happening between queries. I ran a vaccuum (full,
analyze) just before I trying these queries. I do monitor my CPU usage
and there is definitely not some other process on my machine sucking up
all the cpu cycles now and then to explain this.

This is postgreslq 8.3, on Windows XP. The query joins about 17 tables
(without an explicit JOIN, just using the WHERE criteria) with a few
further predicates. One thing which distinguishes it from other similar
queries I've been doing where I haven't seen this odd erraticness is
there are 2 predicates ORred together (and then ANDed with all the other
conditions which are all ANDed) which effectively divides 2 subsets of
joined tables which are not joined to each other, but both joined to
another set of tables. (I don't know if that was a comprehensible way of
explaining this...but I don't know if it's relevant enough to be worth
explaining in more detail).

I've tried running explain, however the wild erraticness seems to go
away when I use explain, taking in the ballpark of 1.5 seconds every
time. This is faster than my average query time using a plain execute,
even if I don't discount all the unusually long times.

Is there any reasonable explanation for this phenomena?

I do realize I could help the query planner with explicit JOINs, however
I have not yet embarked on this optimization, and might not bother if
the query performance is acceptable without doing so. I don't expect the
execution plan to be optimal, however I do expect it to be deterministic.

Thanks,
Eric

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dann Corbit 2008-10-31 20:09:23 Re: Wildly erratic query performance
Previous Message Joseph S 2008-10-31 19:21:38 Re: harddisk configuration