[PERFORM] ...WHERE TRUE" condition in union results in bad query pla

[Claus Stadler]

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, 
100);
CREATE TABLE b AS SELECT generate_series id FROM generate_series(1, 
100);

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=2000 width=4)
 ->  Merge Append  (cost=0.01..1123362.70 rows=2000 width=4)
   Sort Key: a.id
   ->  Index Scan using idx_a on a  (cost=0.00..436681.35 
rows=1000 width=4)
   ->  Index Scan using idx_b on b  (cost=0.00..436681.35 
rows=1000 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=2000 width=4)
 ->  Merge Append  (cost=460344.41..1172025.76 rows=2000 
width=4)

   Sort Key: a.id
   ->  Index Scan using idx_a on a  (cost=0.00..436681.35 
rows=1000 width=4)

   ->  Sort  (cost=460344.40..485344.40 rows=1000 width=4)
 Sort Key: b.id
 ->  Seq Scan on b  (cost=0.00..144248.00 
rows=1000 width=4)




--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] ...WHERE TRUE" condition in union results in bad query pla

[Tom Lane]

Claus Stadler  writes:
> Query optimizer glitch: "...WHERE TRUE" condition in union results in 
> bad query plan ...

Yeah, this is because a nonempty WHERE clause defeats simplifying the
UNION ALL into a simple "append relation" (cf is_safe_append_member()).
The planner will eventually figure out that WHERE TRUE is a no-op,
but that doesn't happen till later (and there are good reasons to do
things in that order).

Sooner or later I'd like to relax the restriction that appendrel members
can't have extra WHERE clauses, but don't hold your breath waiting...

regards, tom lane

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance