Postgres Pro
Downloads
Home   >   mailing lists

Thread: ...WHERE TRUE" condition in union results in bad query pla

...WHERE TRUE" condition in union results in bad query pla

From
Claus Stadler
Date:
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)

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

From
Tom Lane
Date:
Claus Stadler <cstadler@informatik.uni-leipzig.de> 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

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

From
Robert Haas
Date:
On Sat, Mar 3, 2012 at 10:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Claus Stadler <cstadler@informatik.uni-leipzig.de> 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...

Does this comment need updating?

 * Note: the data structure assumes that append-rel members are single
 * baserels.  This is OK for inheritance, but it prevents us from pulling
 * up a UNION ALL member subquery if it contains a join.  While that could
 * be fixed with a more complex data structure, at present there's not much
 * point because no improvement in the plan could result.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

From
Tom Lane
Date:
Robert Haas <robertmhaas@gmail.com> writes:
> On Sat, Mar 3, 2012 at 10:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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...

> Does this comment need updating?

>  * Note: the data structure assumes that append-rel members are single
>  * baserels.  This is OK for inheritance, but it prevents us from pulling
>  * up a UNION ALL member subquery if it contains a join.  While that could
>  * be fixed with a more complex data structure, at present there's not much
>  * point because no improvement in the plan could result.

No, that's a different restriction.

            regards, tom lane