Message-ID: <422E0A1E.1080804@archonet.com>
Date: Tue, 08 Mar 2005 20:25:02 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Gaetano Mendola <mendola@bigfoot.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: bad plan
References: <422D7F23.7000109@bigfoot.com> <422D8F0E.8000906@archonet.com>
	<422D9904.5010904@bigfoot.com> <13634.1110312075@sss.pgh.pa.us>
In-Reply-To: <13634.1110312075@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit

Tom Lane wrote:
> 
> Putting ORDER BYs in views that you intend to use as components of other
> views is a bad practice from a performance perspective...

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?

Secondly, in the original plan we have:
->  Nested Loop Left Join  (cost=1478.82..1716.37 rows=1 width=201) 
(actual time=3254.483..52847.064 rows=31 loops=1)

Now, we've got 31 rows instead of 1 here. The one side of the join ends 
up as:
->  Subquery Scan vsp  (cost=985.73..1016.53 rows=1103 width=12) (actual 
time=25.328..1668.754 rows=493 loops=31)
->  Merge Join  (cost=985.73..1011.01 rows=1103 width=130) (actual 
time=25.321..1666.666 rows=493 loops=31)

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.

Is there any mileage in the idea of a "lazy" planner that keeps some 
alternative paths around in case they're needed? Or a reactive one that 
can re-plan nodes when assumptions turn out to be wrong?

--
   Richard Huxton
   Archonet Ltd