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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
