Re: [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 Tom Lane already replied, so I'm posting just parsed explain plans - I've created that before noticing the reply, and I think it might be useful. good (8.1): http://explain.depesz.com/s/1dT bad (8.4): http://explain.depesz.com/s/seT As you can see, the real problem is the 'index scan / sort'. regards Tomas -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow query after upgrade to 8.4
On Wed, Sep 23, 2009 at 10:35 PM, Tom Lane t...@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: (castbooltointvalue)::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 ja...@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
[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 ja...@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
Re: [PERFORM] Slow query after upgrade to 8.4
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 ja...@singlebrook.com To: pgsql-performance@postgresql.org Cc: Leon Miller-Out l...@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 ja...@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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[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 ja...@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
