Обсуждение: 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@singlebrook.com
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=85459loops=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.4instances have the same autovac settings ? Maybe one is reacting better to daily traffic ? Might be some new part of
theplanner 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
checkall such values (string to int i particular).
HTH,
Greg W.
----- Original Message ----
From: Jared Beck <jared@singlebrook.com>
To: pgsql-performance@postgresql.org
Cc: Leon Miller-Out <leon@singlebrook.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@singlebrook.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Jared Beck <jared@singlebrook.com> writes:
> 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.
One thing that is hobbling the performane on 8.4 is that you have
work_mem set to only 1MB (you had it considerably higher on 8.1).
This is causing that sort step to spill to disk, which isn't helping
its rescan performance one bit.
Other things you might try include increasing join_collapse_limit
to 12 or so, and reducing random_page_cost. The fact that the 8.1
plan didn't completely suck indicates that your database must be
mostly in cache, so the default random_page_cost is probably too high
to model its behavior well.
Another thing to look into is whether you can't get it to make a
better estimate for this:
-> Index Scan using index_tbldata_variableid on tbldata dv118488y0 (cost=0.00..5914.49 rows=8
width=22)(actual time=1.555..209.856 rows=16193 loops=1)
Index Cond: (variableid = 118488)
Filter: (castbooltoint((((value)::text ~
'^-?[0-9]*([0-9]+.|.[0-9]+)?[0-9]*([Ee][-+]d*)?$'::text)AND ((value)::text <> '-'::text))) = 1)
Being off by a factor of 2000 on a first-level rowcount estimate is
almost inevitably a ticket to a bad join plan. I doubt that the
condition on variableid is being that badly estimated; the problem is
the filter condition. Whatever possessed you to take a perfectly good
boolean condition and wrap it in "castbooltoint(condition) = 1"?
I'm not sure how good the estimate would be anyway for the LIKE
condition, but that bit of obscurantism isn't helping.
regards, tom lane
On Wed, Sep 23, 2009 at 10:35 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > One thing that is hobbling the performane on 8.4 is that you have > work_mem set to only 1MB > > Other things you might try include increasing join_collapse_limit > to 12 or so, and reducing random_page_cost. > > Another thing to look into is whether you can't get it to make a > better estimate for this: > > -> Index Scan using index_tbldata_variableid on tbldata dv118488y0 (cost=0.00..5914.49 rows=8 width=22)(actual time=1.555..209.856 rows=16193 loops=1) > Index Cond: (variableid = 118488) > Filter: (castbooltoint((((value)::text ~ '^-?[0-9]*([0-9]+.|.[0-9]+)?[0-9]*([Ee][-+]d*)?$'::text)AND ((value)::text <> '-'::text))) = 1) > > Being off by a factor of 2000 on a first-level rowcount estimate is > almost inevitably a ticket to a bad join plan. I doubt that the > condition on variableid is being that badly estimated; the problem is > the filter condition. Whatever possessed you to take a perfectly good > boolean condition and wrap it in "castbooltoint(condition) = 1"? > I'm not sure how good the estimate would be anyway for the LIKE > condition, but that bit of obscurantism isn't helping. > > regards, tom lane > After following all of Tom's suggestions, the query is now executing in about one minute instead of seventeen minutes. Thanks, Tom. In case you were curious, after removing the confusing call to castbooltoint() the row estimate increased from the vastly incorrect 8 rows to the moderately incorrect 1000 rows (compared to the actual 16193 rows) Should we try to improve statistics collection for that column (variableid) by using ALTER TABLE ... ALTER COLUMN ... SET STATISTICS? In other words, if the row estimate were perfect would we be likely to get a better plan? Or is that impossible to speculate on? Thanks again. Already you've been a big help. We love postgres and are very happy with our upgrade to 8.4 so far! -Jared -- ------------------ Jared Beck Web Developer Singlebrook Technology (607) 330-1493 jared@singlebrook.com
On Thu, Sep 24, 2009 at 8:22 AM, Jared Beck <jared@singlebrook.com> wrote: > Should we try to improve statistics collection for that column > (variableid) by using ALTER TABLE ... ALTER COLUMN ... SET STATISTICS? It's worth a try, but I'm not sure it's going to help much. The LIKE condition is hard for the planner to estimate at present. > In other words, if the row estimate were perfect would we be likely > to get a better plan? Or is that impossible to speculate on? Good row estimates are the key to happiness, but I don't know whether it will actually change the plan in this instance. > Thanks again. Already you've been a big help. We love postgres and > are very happy with our upgrade to 8.4 so far! Glad to hear it. ...Robert