Re: 7.3.2 vs 7.1.2 - Mailing list pgsql-performance

From Tom Lane
Subject Re: 7.3.2 vs 7.1.2
Date
Msg-id 29699.1053448821@sss.pgh.pa.us
Whole thread Raw
In response to Re: 7.3.2 vs 7.1.2  (Victor Yegorov <viktors.jegorovs@nordlb.lv>)
Responses Re: 7.3.2 vs 7.1.2  (Eugene Fokin <elf@solvo.ru>)
List pgsql-performance
Victor Yegorov <viktors.jegorovs@nordlb.lv> writes:
> As you can see, in 7.2.1 index scan on loads (load_rcn_id_idx) takes
> 0.04..786.13, but in 7.3.2 - 0.07..4486.76.

That seems very odd, doesn't it?  Is it reproducible?  I'm wondering if
the 7.2 table was clustered on the index while the 7.3 wasn't.

Most of the cost differential, however, is coming from the fact that 7.3
doesn't flatten the view and thus fails to realize that it doesn't need
to evaluate any of the view's targetlist expressions.  Note the lack of
any "SubPlans" in the 7.2 plan, whereas they're accounting for a good
deal of time in the 7.3 plan.

The reason that the view isn't flattened is that pulling up targetlists
containing sub-selects turned out to break some obscure cases involving
join alias variables, and by the time we discovered this (after 7.3
release) there was no practical way to fix it except to disable the
optimization.  It's fixed properly in CVS tip (7.4 branch) but 7.3.* is
just going to be slow on such cases; the fix is much too complex to risk
back-porting.

I'm not sure whether selecting the count from this view is really all
that important to Eugene, but if it is he could make an alternate view
that has the same FROM clause and nothing interesting in its select
list, and then count that.

            regards, tom lane

pgsql-performance by date:

Previous
From: Josh Berkus
Date:
Subject: Re: 7.3.2 vs 7.1.2
Next
From: Tom Lane
Date:
Subject: Re: 7.3.2 vs 7.1.2