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