To: Richard Huxton <dev@archonet.com>
Cc: Gaetano Mendola <mendola@bigfoot.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: bad plan 
In-reply-to: <422E0A1E.1080804@archonet.com> 
References: <422D7F23.7000109@bigfoot.com> <422D8F0E.8000906@archonet.com>
	<422D9904.5010904@bigfoot.com> <13634.1110312075@sss.pgh.pa.us>
	<422E0A1E.1080804@archonet.com>
Comments: In-reply-to Richard Huxton <dev@archonet.com>
	message dated "Tue, 08 Mar 2005 20:25:02 +0000"
Date: Tue, 08 Mar 2005 15:42:03 -0500
Message-ID: <13880.1110314523@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Richard Huxton <dev@archonet.com> 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