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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
