Slow query when used in a view

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