Thread: 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
massivelyupgrade our hardware. Overall, this has been the major improvement you would expect, but there is at least one
querywhich 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
ifthose indexes were used. The other differences I can think of are random_page_cost (2 on the new hardware vs. 2.5 on
theold), a ten-fold increase in effective_cache_size, doubling work_mem from 8MB to 16MB, and that we analyze up to 100
samplesper attribute on 8.4.2, while our 8.1.19 install does 10 at most. Still, the estimates for both plans seem
fairlyaccurate, 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
surewhat to do to coax it towards using those indexes. 

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

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

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
throughwere because of the size of the email.) 

Ben Chobot <bench@silentmedia.com> 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

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 <bench@silentmedia.com> 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

Ben Chobot <bench@silentmedia.com> 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