From: | Chris Hanks <christopher(dot)m(dot)hanks(at)gmail(dot)com> |
---|---|
To: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Joining on a view containing a UNION ALL produces a suboptimal plan on 9.3.4 |
Date: | 2014-07-10 09:01:31 |
Message-ID: | CAK7KUdAvLZRCH_YqY3wWJ08QHYiG351XMmTb2J=1jDobU5s8oA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Sebastian Fiedler - Nexst4 GmbH | 2014-07-10 09:23:16 | Standby Server and Barman Backup on production system |
Previous Message | Scott Marlowe | 2014-07-10 03:21:39 | Re: php password authentication failed for user ... |