Search Postgresql Archives

Re: Wildly erratic query performance

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Dann,

Thanks for your response. I thought I'd covered most of what your are
asking in my first message, but these results are weird enough that I
can understand you might not give me the benefit of the doubt and
without very explicit confirmation. To answer your questions:

YES the query each time is IDENTICAL. I am not changing a single
character, I am simply clicking the execute button in pgadmin for each run.

This is my personal laptop, so of course there are other processes
running, like say, a web browser, an email client, etc. And of course,
as on any such machine there may be minor processes that execute in the
background without my awareness. But I am not actively doing anything
else (like running a large compile, yeesh!) while the query is
executing. This is why I noted that I was monitoring the CPU usage (and
processes), so that I can be confident that something major is not
suddenly running in the background without my initiating it directly.
And to make the difference between 2 seconds and a minute, let alone 10
minutes, would take a pretty major and hard not to notice process.

As I explained already (no pun intended) running the query using EXPLAIN
makes the wild variation go away. So I cannot get explain results for a
fast and for a slow execution.

I did not include schema information and such because I am not clear I
am allowed to make them public, and because I'm not looking for a highly
specific answer, merely are there ANY conditions where the SAME EXACT
QUERY should perform so radically differently. If the query planner, for
example, used a random number generator to choose the order in which it
performed my joins, such that the join order would be different each
time, this would explain it--that possibility would seem bizarre to me,
but it would certainly answer my question.

Eric

Dann Corbit wrote:
>> -----Original Message-----
>> From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-
>> owner@xxxxxxxxxxxxxx] On Behalf Of Eric Schwarzenbach
>> Sent: Friday, October 31, 2008 12:35 PM
>> To: pgsql-general@xxxxxxxxxxxxxx
>> Subject:  Wildly erratic query performance
>>
>> 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.
>>     
>
> Something is missing from your descriptions.
> An explain analyze on the query and a list of the schema for the
> relevant tables would be helpful.
> Are the queries identical?  Just changing the where clause a bit can
> cause big differences in query speed.
>
> Consider:
> SELECT age, status, phone FROM work_force WHERE state IN ('NY', 'CA',
> 'TX');
> May run more slowly than:
> SELECT age, status, phone FROM work_force WHERE state IN ('ID', 'MT',
> 'NV');
> Because the first three states have large populations and the last three
> states have smaller populations.
>
> Does the database machine run solely as a database server or are there
> other things going on?  E.g. If you are doing a compile and link of
> 10,000 source files during one query and the machine is otherwise idle
> during a different one, we will expect different results.
>
> There will be (of course) a logical explanation for the query time
> differences.
>
> I suggest the following:
> 1.  Do an explain analyze on a query that is slow
> 2.  Do an explain analyze on a query that is fast (unless the queries
> are literally identical in every way)
> I guess that (along with the schema) will be enough to get an idea what
> is happening.
>
>   


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux