From: Tom Lane <tgl@sss.pgh.pa.us>
To: Josh Berkus <josh@agliodbs.com>
Cc: Eugene Fokin <elf@solvo.ru>
Cc: Victor Yegorov <viktors.jegorovs@nordlb.lv>
Cc: pgsql-performance@postgresql.org
Subject: Re: 7.3.2 vs 7.1.2
Date: Tue, 20 May 2003 13:33:40 -0400
Message-ID: <193.1053452020@sss.pgh.pa.us> (raw)
In-Reply-To: <200305200935.25766.josh@agliodbs.com>
References: <20030520122838.GA28654@solvo.ru>
<20030520130913.GM1976@nordlb.lv>
<20030520132842.GA29262@solvo.ru>
<200305200935.25766.josh@agliodbs.com>
Josh Berkus <josh@agliodbs.com> writes:
> Another question ... given that 7.3.2's estimates are very similar to 7.2.1's
> estimates, but the real execution time is much slower, is it possible that
> the 7.3.2 copy of the database is being loaded on a much slower disk?
Nah, the big reason for the discrepancy is that 7.3 is unable to prune
away evaluation of all those sub-selects in the view's target list, per
my previous response.
Up till just recently (post-7.3) the planner didn't bother to charge any
evaluation cost for targetlist expressions, and so the estimated costs
don't reflect the difference.
(The reasoning behind that behavior was that the planner couldn't affect
the evaluation costs of targetlist expressions by choosing a different
plan, since the number of rows they'll be computed for will be the same
in every correct plan. But now that we allow arbitrarily complex stuff
in sub-selects, that reasoning doesn't hold water anymore --- it's
important to propagate a good estimate of the cost up to the enclosing
plan. So as of 7.4 we expend the cycles to add in tlist execution time
estimates.)
I am still interested in the apparent difference in the time taken for
that bottom indexscan, though. The width difference that you noticed is
because the unflattened view needs to fetch many more columns of the
table than the flattened query needs. But the same number of rows get
fetched, and approximately the same number of disk blocks ought to get
read, so it's hard to see why there'd be such a large difference.
regards, tom lane
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, josh@agliodbs.com, elf@solvo.ru, viktors.jegorovs@nordlb.lv
Subject: Re: 7.3.2 vs 7.1.2
In-Reply-To: <193.1053452020@sss.pgh.pa.us>
References: <20030520122838.GA28654@solvo.ru> <20030520130913.GM1976@nordlb.lv> <20030520132842.GA29262@solvo.ru> <200305200935.25766.josh@agliodbs.com> <193.1053452020@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