Re: Joining on a view containing a UNION ALL produces a suboptimal plan on 9.3.4

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] 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