From: Shaun Thomas <sthomas@optionshouse.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Slow query when used in a view
Date: Mon, 11 Mar 2013 17:29:13 -0500
Message-ID: <513E5AB9.4010809@optionshouse.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Hey everyone!
A developer was complaining about a view he created to abstract an added
column in a left join. He was contemplating denormalizing the added
value into the parent table and using a trigger to maintain it instead,
and I obviously looked into the problem. I noticed the view was
incurring a sequence scan on an obvious index condition, but the regular
join version was not.
Curious, I whipped up this test case:
CREATE TABLE foo (id BIGINT, small_label VARCHAR);
INSERT INTO foo (id) VALUES (generate_series(1, 10000));
ALTER TABLE foo ADD CONSTRAINT pk_foo_id PRIMARY KEY (id);
CREATE TABLE bar (id BIGINT, foo_id BIGINT);
INSERT INTO bar (id, foo_id)
SELECT a, a%10000
FROM generate_series(1, 100000) a;
ALTER TABLE bar ADD CONSTRAINT pk_bar_id PRIMARY KEY (id);
CREATE TABLE tiny_foo (small_label VARCHAR NOT NULL PRIMARY KEY);
INSERT INTO tiny_foo (small_label)
VALUES (('yes', 'we', 'have', 'no', 'bananas'));
UPDATE foo SET small_label = 'bananas' WHERE id=750;
ANALYZE foo;
ANALYZE bar;
ANALYZE tiny_foo;
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);
Now, this is with PostgreSQL 9.1.8, basically default everything in a
base Ubuntu install. So, the good query plan using all tables directly:
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;
does this:
Index Scan using pk_foo_id on foo (cost=0.00..8.27 rows=1 width=16)
Index Cond: (id = bar.foo_id)
The bad one using the view:
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;
Mysteriously, does this:
Seq Scan on foo (cost=0.00..145.00 rows=10000 width=16)
I'm... perplexed. This test case is way too shallow to be affected by
join_collapse_limit and its ilk, so I'm not sure what's going on here. I
sense an optimization fence, but I can't see where.
Thanks in advance!
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com
______________________________________________
See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
--
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: sthomas@optionshouse.com
Subject: Re: Slow query when used in a view
In-Reply-To: <513E5AB9.4010809@optionshouse.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