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

Re: another 8.1->8.4 regression

From: Ben Chobot <bench(at)silentmedia(dot)com>
To: PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: another 8.1->8.4 regression
Date: 2010-02-16 23:39:08
Message-ID: DA405EE2-F1FF-44E5-87DC-520EC52D9977@silentmedia.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Feb 16, 2010, at 1:29 PM, Ben Chobot wrote:

> I'm having problems with another one of my queries after moving from 8.1.19 to 8.4.2. On 8.1.19, the plan looked like this:
> 
> http://wood.silentmedia.com/bench/8119
> 
> That runs pretty well. On 8.4.2, the same query looks like this:
> 
> http://wood.silentmedia.com/bench/842_bad
> 
> If I turn off mergejoin and hashjoin, I can get 8.4.2 to spit out this:
> 
> http://wood.silentmedia.com/bench/842_better
> 
> ...which it thinks is going to suck but which does not. 
> 
> The query and relevant table definitions are here:
> 
> http://wood.silentmedia.com/bench/query_and_definitions
> 
> 
> Any suggestions? I'm guessing the problem is with the absurd over-estimation on the nested loop under the sort node, but I'm not sure why it's so bad. 


After looking at this some more, I'm pretty confused at both of 8.4.2's plans. They both have a Nested Loop node in them where the expected row count is a bit over 2 million, and yet the inner nodes have expected row counts of 1 and 152. I was under the impression that a nested loop between R and S would return no more than R*S?

In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2010-02-17 05:34:37
Subject: Re: 8.1 -> 8.4 regression
Previous:From: Jeff DavisDate: 2010-02-16 21:34:11
Subject: Re: Linux I/O tuning: CFQ vs. deadline

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