Message-ID: <513E5AB9.4010809@optionshouse.com>
Date: Mon, 11 Mar 2013 17:29:13 -0500
From: Shaun Thomas <sthomas@optionshouse.com>
Reply-To: <sthomas@optionshouse.com>
Organization: Peak6
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:17.0) Gecko/20130221 Thunderbird/17.0.3
MIME-Version: 1.0
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Slow query when used in a view
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
content-transfer-encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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