Re: [PERFORM] Slow query after upgrade to 8.4

2009-09-27 Thread Robert Haas
On Thu, Sep 24, 2009 at 8:22 AM, Jared Beck 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 e

Re: [PERFORM] Slow query after upgrade to 8.4

2009-09-24 Thread Jared Beck
On Wed, Sep 23, 2009 at 10:35 PM, Tom Lane 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 w

Re: [PERFORM] Slow query after upgrade to 8.4

2009-09-24 Thread tv
> 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 acros

Re: [PERFORM] Slow query after upgrade to 8.4

2009-09-23 Thread Tom Lane
Jared Beck 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

[PERFORM] Slow query after upgrade to 8.4

2009-09-23 Thread Jared Beck
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"

Re: [PERFORM] Slow query after upgrade to 8.4

2009-09-23 Thread Greg Williamson
essage From: Jared Beck To: pgsql-performance@postgresql.org Cc: Leon Miller-Out 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

[PERFORM] Slow query after upgrade to 8.4

2009-09-23 Thread Jared Beck
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"