Skip site navigation (1) Skip section navigation (2)

Re: Slow query after upgrade to 8.4

From: Greg Williamson <gwilliamson39(at)yahoo(dot)com>
To: Jared Beck <jared(at)singlebrook(dot)com>, pgsql-performance(at)postgresql(dot)org
Cc: Leon Miller-Out <leon(at)singlebrook(dot)com>
Subject: Re: Slow query after upgrade to 8.4
Date: 2009-09-23 23:22:45
Message-ID: 194856.38498.qm@web46102.mail.sp1.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-performance
Jared --

Forgive the top-posting -- a challenged reader.

I see this in the 8.4 analyze:
               Merge Cond: (cli.clientid = dv118488y0.clientid)
               Join Filter: ((dv118488y0.variableid = v118488y0.variableid) AND (dv118488y0.cycleid = c1.cycleid) AND (dv118488y0.unitid = u.unitid))
               ->  Nested Loop Left Join  (cost=33.20..9756.43 rows=731 width=38) (actual time=0.922..1215.702 rows=85459 loops=1)
                     Join Filter: (dv118482y0.clientid = cli.clientid)
                     ->  Nested Loop  (cost=33.20..697.60 rows=731 width=36) (actual time=0.843..124.942 rows=85459 loops=1)

And am wondering about the divergent estimates vs real numbers - and you say you analyze regularly ? Do both 8.1 and 8.4 instances have the same autovac settings ? Maybe one is reacting better to daily traffic ? Might be some new part of the planner which is being wonky, I suppose, but I don't understand enough about it to say.

Might also be some automatic casts that were eliminated between 8.1 and 8.4 -- I don't see any offhand but you should check all such values (string to int i particular).

HTH,

Greg W.




----- Original Message ----
From: Jared Beck <jared(at)singlebrook(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Cc: Leon Miller-Out <leon(at)singlebrook(dot)com>
Sent: Wednesday, September 23, 2009 12:53:15 PM
Subject: [PERFORM] Slow query after upgrade to 8.4

Hello postgres wizards,

We recently upgraded from 8.1.5 to 8.4
We have a query (slow_query.sql) which took about 9s on 8.1.5
On 8.4, the same query takes 17.7 minutes.

The code which generated this query is written to support the
calculation of arbitrary arithmetic expressions across "variables" and
"data" within our application.  The example query is a sum of three
"variables", but please note that because the code supports arbitrary
arithmetic, we do not use an aggregate function like sum()

We have collected as much information as we could and zipped it up here:

http://pgsql.privatepaste.com/download/a3SdI8j2km

Thank you very much in advance for any suggestions you may have,
Jared Beck

--
------------------
Jared Beck
Web Developer
Singlebrook Technology
(607) 330-1493
jared(at)singlebrook(dot)com

In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2009-09-24 02:35:15
Subject: Re: Slow query after upgrade to 8.4
Previous:From: Bill KirtleyDate: 2009-09-23 22:28:46
Subject: Use of sequence rather than index scan for one text column on one instance of a database

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group