> -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general- > owner@xxxxxxxxxxxxxx] On Behalf Of Chris Hanks > Sent: Thursday, July 10, 2014 5:02 AM > To: PostgreSQL General > Subject: [GENERAL] Joining on a view containing a UNION ALL produces a > suboptimal plan on 9.3.4 > > Hi everyone - > > I have a slow query issue in an app I'm working on. I'm unfortunately not at > liberty to share the query/schema details, but I've put together a very similar > reproduction of the issue: > > ----- > > CREATE TABLE a (id integer primary key, col integer); CREATE TABLE b (id > integer primary key, col integer); CREATE TABLE c (id integer primary key, col > integer); CREATE TABLE d (id integer primary key, col integer); CREATE TABLE > e (id integer primary key, col integer); > > INSERT INTO a (id, col) SELECT i, floor(random() * 100000) FROM > generate_series(1, 100000, 2) i; INSERT INTO b (id, col) SELECT i, > floor(random() * 100000) FROM generate_series(1, 100000, 2) i; INSERT INTO > c (id, col) SELECT i, floor(random() * 100000) FROM generate_series(2, > 100000, 2) i; INSERT INTO d (id, col) SELECT i, floor(random() * 100000) FROM > generate_series(2, 100000, 2) i; INSERT INTO e (id, col) SELECT i, > floor(random() * 100000) FROM generate_series(1, 100000, 1) i; > > ANALYZE; > > CREATE VIEW tables AS > SELECT a.*, b.col AS other_col > FROM a > LEFT JOIN b ON a.id = b.id > UNION ALL > SELECT c.*, d.col AS other_col > FROM c > LEFT JOIN d ON c.id = d.id; > > EXPLAIN ANALYZE > SELECT * > FROM tables > WHERE id = 89; -- Index scans, as expected. > > EXPLAIN ANALYZE > SELECT * > FROM e > JOIN tables ON e.col = tables.id > WHERE e.id = 568; -- Big merge joins, when simple index scans should be > possible? > > ----- > > Would this be considered a deficiency in the optimizer? Is there a simple fix? > > Thanks! > Chris > Chris, " JOIN tables ON e.col = tables.id" - is this a typo? Shouldn't it be " JOIN tables ON e.id = tables.id" ? Or, you need it the way it is? Regards, Igor Neyman
