From: Tom Lane <tgl@sss.pgh.pa.us>
To: sthomas@optionshouse.com
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Slow query when used in a view
Date: Mon, 11 Mar 2013 19:56:59 -0400
Message-ID: <1454.1363046219@sss.pgh.pa.us> (raw)
In-Reply-To: <513E5AB9.4010809@optionshouse.com>
References: <513E5AB9.4010809@optionshouse.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Shaun Thomas <sthomas@optionshouse.com> writes:
> A developer was complaining about a view he created to abstract an added
> column in a left join. ...
> Curious, I whipped up this test case:
> CREATE VIEW v_slow_view AS
> SELECT foo.*, tf.small_label IS NOT NULL AS has_small_label
> FROM foo
> LEFT JOIN tiny_foo tf USING (small_label);
> SELECT bar.*, foo.*, tf.small_label IS NOT NULL AS has_small_label
> FROM bar
> LEFT JOIN foo ON (foo.id = bar.foo_id)
> LEFT JOIN tiny_foo tf USING (small_label)
> WHERE bar.id IN (750, 1750, 2750)
> ORDER BY bar.id;
> SELECT bar.*, sv.*
> FROM bar
> LEFT JOIN v_slow_view sv ON (sv.id = bar.foo_id)
> WHERE bar.id IN (750, 1750, 2750)
> ORDER BY bar.id;
These queries are not actually equivalent. In the first one, it is
impossible for "has_small_label" to read out as NULL: it will either be
true or false. However, in the second one, the IS NOT NULL is evaluated
below the LEFT JOIN to "sv", and therefore it is required that the query
return NULL for "has_small_label" in any row where bar.foo_id lacks a
join partner.
To implement that behavior correctly, we're forced to form the
foo-to-tiny_foo join first, then do the left join with bar (which'll
replace RHS columns by nulls where necessary).
And that means that you get the inefficient plan wherein the
foo-to-tiny_foo join is computed in its entirety.
9.2 does this case better, by virtue of the "parameterized plan" stuff,
which exists specifically to let us use nestloop-with-inner-indexscan
plans even when there are some join order restrictions complicating
matters.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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, sthomas@optionshouse.com
Subject: Re: Slow query when used in a view
In-Reply-To: <1454.1363046219@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