From: | Claus Stadler <cstadler(at)informatik(dot)uni-leipzig(dot)de> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | ...WHERE TRUE" condition in union results in bad query pla |
Date: | 2012-03-03 22:43:17 |
Message-ID: | 4F529E85.8010506@informatik.uni-leipzig.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hi,
(Sorry about double post, I just registered on the performance mailing
list, but sent the mail from the wrong account - if anyone responds,
please respond to this address)
Another issue I have encountered :)
Query optimizer glitch: "...WHERE TRUE" condition in union results in
bad query plan when sorting the union on a column where for each
union-member there exists an index.
Find minimal example below.
Cheers,
Claus
PostgreSQL 9.1.3 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real
(Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit
DROP TABLE a;
DROP TABLE b;
CREATE TABLE a AS SELECT generate_series id FROM generate_series(1,
1000000);
CREATE TABLE b AS SELECT generate_series id FROM generate_series(1,
1000000);
CREATE INDEX idx_a ON a(id);
CREATE INDEX idx_b ON b(id);
Q1: Returns immediately:
SELECT c.id FROM (SELECT a.id FROM a UNION ALL SELECT b.id FROM b) c
ORDER BY c.id LIMIT 10;
Q2: Takes a while:
SELECT c.id FROM (SELECT a.id FROM a UNION ALL SELECT b.id FROM b WHERE
TRUE) c ORDER BY c.id LIMIT 10;
Good plan of Q1:
EXPLAIN SELECT c.id FROM (SELECT a.id FROM a UNION ALL SELECT b.id FROM
b) c ORDER BY c.id LIMIT 10;
Limit (cost=0.01..0.57 rows=10 width=4)
-> Result (cost=0.01..1123362.70 rows=20000000 width=4)
-> Merge Append (cost=0.01..1123362.70 rows=20000000 width=4)
Sort Key: a.id
-> Index Scan using idx_a on a (cost=0.00..436681.35
rows=10000000 width=4)
-> Index Scan using idx_b on b (cost=0.00..436681.35
rows=10000000 width=4)
Bad plan of Q2: Does sorting although index scan would be sufficient
EXPLAIN SELECT c.id FROM (SELECT a.id FROM a UNION ALL SELECT b.id FROM
b WHERE TRUE) c ORDER BY c.id LIMIT 10;
Limit (cost=460344.41..460344.77 rows=10 width=4)
-> Result (cost=460344.41..1172025.76 rows=20000000 width=4)
-> Merge Append (cost=460344.41..1172025.76 rows=20000000
width=4)
Sort Key: a.id
-> Index Scan using idx_a on a (cost=0.00..436681.35
rows=10000000 width=4)
-> Sort (cost=460344.40..485344.40 rows=10000000 width=4)
Sort Key: b.id
-> Seq Scan on b (cost=0.00..144248.00
rows=10000000 width=4)
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2012-03-04 03:03:29 | Re: ...WHERE TRUE" condition in union results in bad query pla |
Previous Message | Tomas Vondra | 2012-03-03 00:30:18 | Re: [PERFORM] Re: [PERFORM] Re: [PERFORM] Re: [PERFORM] Re: 回复: [PERFORM] PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory? |