Re: [PERFORM] 8.1 -> 8.4 regression
Ben Chobot writes: > Awesome, that did the trick. Thanks Tom! So I understand better, why is my > case not the normal, better case? Well, the short answer is that the 8.4 changes here are in the nature of two steps forward and one step back. The long-term goal is to increase the planner's ability to choose among different join orders; but we're getting rid of one restriction at a time, and sometimes the interactions of those restrictions produce unwanted results like the older code being able to find a better plan than the new code can. > (I assume the long-term fix is post-9.0, right?) Yeah, fraid so. I've been mostly buried in non-planner work in the 9.0 cycle, but hope to get back to this and other problems in the next cycle. regards, tom lane -- 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] 8.1 -> 8.4 regression
Awesome, that did the trick. Thanks Tom! So I understand better, why is my case not the normal, better case? (I assume the long-term fix is post-9.0, right?) On Feb 15, 2010, at 9:26 AM, Tom Lane wrote: > Ben Chobot writes: >> On Feb 15, 2010, at 7:59 AM, Kevin Grittner wrote: >>> Could you show the query, along with table definitions (including >>> indexes)? > >> Oh, yeah, I suppose that would help. :) > >> http://wood.silentmedia.com/bench/query_and_definitions > > It looks like the problem is that the EXISTS sub-query is getting > converted into a join; which is usually a good thing but in this case it > interferes with letting the users table not be scanned completely. > The long-term fix for that is to support nestloop inner indexscans where > the index key comes from more than one join level up, but making that > happen isn't too easy. > > In the meantime, I think you could defeat the "optimization" by > inserting LIMIT 1 in the EXISTS sub-query. > > regards, tom lane -- 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] 8.1 -> 8.4 regression
Ben Chobot writes: > On Feb 15, 2010, at 7:59 AM, Kevin Grittner wrote: >> Could you show the query, along with table definitions (including >> indexes)? > Oh, yeah, I suppose that would help. :) > http://wood.silentmedia.com/bench/query_and_definitions It looks like the problem is that the EXISTS sub-query is getting converted into a join; which is usually a good thing but in this case it interferes with letting the users table not be scanned completely. The long-term fix for that is to support nestloop inner indexscans where the index key comes from more than one join level up, but making that happen isn't too easy. In the meantime, I think you could defeat the "optimization" by inserting LIMIT 1 in the EXISTS sub-query. regards, tom lane -- 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] 8.1 -> 8.4 regression
On Feb 15, 2010, at 7:59 AM, Kevin Grittner wrote: > Could you show the query, along with table definitions (including > indexes)? Oh, yeah, I suppose that would help. :) http://wood.silentmedia.com/bench/query_and_definitions (I'd paste them here for posterity but I speculate the reason my first few attempts to ask this question never went through were because of the size of the email.) -- 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] 8.1 -> 8.4 regression
Ben Chobot wrote: > Here is the plan on 8.4.2: > Here is the very much less compact plan for the same query on > 8.1.19: Could you show the query, along with table definitions (including indexes)? -Kevin -- 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] 8.1 -> 8.4 regression
Can you force 8.4 to generate the same plan as 8.1? For example by running SET enable_hashjoin = off; before you run EXPLAIN on the query? If so, then we can compare the numbers from the forced plan with the old plan and maybe figure out why it didn't use the same old plan in 8.4 as it did in 8.1. Note that the solution is not to force the plan, but it can give us more information. /Dennis > is at least one query which has degraded in performance quite a bit. Here > is the plan on 8.4.2: > http://wood.silentmedia.com/bench/842 > > Here is the very much less compact plan for the same query on 8.1.19: > http://wood.silentmedia.com/bench/8119 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] 8.1 -> 8.4 regression
(Apologies if this ends up coming through multiple times - my first attempts seem to have gotten stuck.) We recently took the much needed step in moving from 8.1.19 to 8.4.2. We took the downtime opportunity to also massively upgrade our hardware. Overall, this has been the major improvement you would expect, but there is at least one query which has degraded in performance quite a bit. Here is the plan on 8.4.2: http://wood.silentmedia.com/bench/842 Here is the very much less compact plan for the same query on 8.1.19: http://wood.silentmedia.com/bench/8119 I think the problem might be that 8.1.19 likes to use a few indexes which 8.4.2 doesn't seem to think would be worthwhile. Perhaps that's because on the new hardware almost everything fits into ram, but even so, it would be better if those indexes were used. The other differences I can think of are random_page_cost (2 on the new hardware vs. 2.5 on the old), a ten-fold increase in effective_cache_size, doubling work_mem from 8MB to 16MB, and that we analyze up to 100 samples per attribute on 8.4.2, while our 8.1.19 install does 10 at most. Still, the estimates for both plans seem fairly accurate, at least where there are differences in which indexes are getting used. Everything has been analyzed recently, and given that 8.4.2 already has 10x more analysis samples than 8.1.19, I'm not sure what to do to coax it towards using those indexes. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
