From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Huxton <dev@archonet.com>
Cc: Gaetano Mendola <mendola@bigfoot.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: bad plan
Date: Tue, 08 Mar 2005 15:42:03 -0500
Message-ID: <13880.1110314523@sss.pgh.pa.us> (raw)
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>
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
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: tgl@sss.pgh.pa.us, dev@archonet.com, mendola@bigfoot.com
Subject: Re: bad plan
In-Reply-To: <13880.1110314523@sss.pgh.pa.us>
* 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