Message-ID: <4F529E85.8010506@informatik.uni-leipzig.de>
Date: Sat, 03 Mar 2012 23:43:17 +0100
From: Claus Stadler <cstadler@informatik.uni-leipzig.de>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:10.0.2) Gecko/20120216 Thunderbird/10.0.2
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: ...WHERE TRUE" condition in union results in bad query pla
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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)