From: Richard Huxton <dev@archonet.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Gaetano Mendola <mendola@bigfoot.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: bad plan
Date: Tue, 08 Mar 2005 20:25:02 +0000
Message-ID: <422E0A1E.1080804@archonet.com> (raw)
In-Reply-To: <13634.1110312075@sss.pgh.pa.us>
References: <422D7F23.7000109@bigfoot.com>
<422D8F0E.8000906@archonet.com>
<422D9904.5010904@bigfoot.com>
<13634.1110312075@sss.pgh.pa.us>
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
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dev@archonet.com, tgl@sss.pgh.pa.us, mendola@bigfoot.com
Subject: Re: bad plan
In-Reply-To: <422E0A1E.1080804@archonet.com>
* 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