Re: 7.3.2 vs 7.1.2

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Victor Yegorov <viktors(dot)jegorovs(at)nordlb(dot)lv>
Cc: Eugene Fokin <elf(at)solvo(dot)ru>, pgsql-performance(at)postgresql(dot)org
Subject: Re: 7.3.2 vs 7.1.2
Date: 2003-05-20 16:40:21
Message-ID: 29699.1053448821@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Victor Yegorov <viktors(dot)jegorovs(at)nordlb(dot)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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2003-05-20 17:33:40 Re: 7.3.2 vs 7.1.2
Previous Message Josh Berkus 2003-05-20 16:35:25 Re: 7.3.2 vs 7.1.2