From: Tom Lane
Subject: Re: bad plan
Date: ,
(view: Whole thread, Raw)
In response to: Re: bad plan  (Richard Huxton)
List: pgsql-performance

Tree view

bad plan  (Gaetano Mendola, )
 Re: bad plan  (Richard Huxton, )
  Re: bad plan  (Gaetano Mendola, )
   Re: bad plan  (Richard Huxton, )
    Re: bad plan  (Gaetano Mendola, )
     Re: bad plan  (Richard Huxton, )
   Re: bad plan  (Tom Lane, )
    Re: bad plan  (Richard Huxton, )
     Re: bad plan  (Tom Lane, )
    Re: bad plan  (Gaetano Mendola, )

Richard Huxton <> writes:
> There are also a lot of views involved here for very few output columns.
> Tom - is the planner smart enough to optimise-out unneeded columns from
> a SELECT * view if it's part of a join/subquery and you only use one or
> two columns?

If the view gets flattened, yes, but I believe that it's not bright
enough to do so when it can't flatten the view.  You could tell easily
enough by looking at the row-width estimates at various levels of the
plan.  (Let's see ... in Gaetano's plan the SubqueryScan is returning
12-byte rows where its input MergeJoin is returning 130-byte rows,
so sure enough the view is computing a lot of stuff that then gets
thrown away.)

> Would I be right in thinking the planner doesn't materialise the
> subquery because it's expecting 1 loop not 31? If there were 1 row the
> plan would seem OK to me.

Right; it doesn't see any predicted gain from the extra cost of
materializing.  But to me the main problem here is not that, it is that
the entire shape of the plan would likely be different if it weren't for
the "optimization fence" that the Subquery Scan node represents.  I
suspect too that the use of mergejoin as opposed to anything else within
the vsp subplan is driven more by the need to produce sorted output than
by what is the cheapest way to get the rows.

            regards, tom lane

pgsql-performance by date:

From: Michael Fuhr
Subject: Re: vacuum full, why multiple times ?
From: David B
Subject: 64bit Opteron multi drive raid. Help with best config settings