pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Victor Yegorov <viktors.jegorovs@nordlb.lv>
Cc: Eugene Fokin <elf@solvo.ru>
Cc: pgsql-performance@postgresql.org
Subject: Re: 7.3.2 vs 7.1.2
Date: Tue, 20 May 2003 12:40:21 -0400
Message-ID: <29699.1053448821@sss.pgh.pa.us> (raw)
In-Reply-To: <20030520140713.GN1976@nordlb.lv>
References: <20030520122838.GA28654@solvo.ru>
	<20030520130913.GM1976@nordlb.lv>
	<20030520132842.GA29262@solvo.ru>
	<20030520140713.GN1976@nordlb.lv>

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



view thread (14+ messages)  latest in thread

Message-ID: <29699.1053448821@sss.pgh.pa.us>
Permalink:  ../29699.1053448821@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/29699.1053448821@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, viktors.jegorovs@nordlb.lv, elf@solvo.ru
  Subject: Re: 7.3.2 vs 7.1.2
  In-Reply-To: <29699.1053448821@sss.pgh.pa.us>
  References: <20030520122838.GA28654@solvo.ru> <20030520130913.GM1976@nordlb.lv> <20030520132842.GA29262@solvo.ru> <20030520140713.GN1976@nordlb.lv> <29699.1053448821@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox