Recently, we find PG fails to generate an effective plan for following SQL:
    select * from (select * from table1 execpt select * from table2) as foo 
where foo.a > 0;
    Because PG does not pushdown qual to the none of the subquery. And I check 
the source code, find some comments in src/backend/optimizer/path/allpaths.c, 
which says "If the     subquery contains EXCEPT or EXCEPT ALL set ops we cannot 
push quals into it, because that could change the results".
    However, for this case, I think we can pushdown qual to the  left most 
subquery of EXCEPT, just like other database does. And we can get an more 
effective plan such as:
postgres=# explain select * from (select * from table1 except select * from 
table2) as foo where foo.a > 0;
                                       QUERY PLAN                               
 Subquery Scan on foo  (cost=0.00..118.27 rows=222 width=8)
   ->  HashSetOp Except  (cost=0.00..116.05 rows=222 width=12)
         ->  Append  (cost=0.00..100.98 rows=3013 width=12)
               ->  Subquery Scan on "*SELECT* 1"  (cost=0.00..45.78 rows=753 
                     ->  Seq Scan on table1  (cost=0.00..38.25 rows=753 width=8)
                           Filter: (a > 0)
               ->  Subquery Scan on "*SELECT* 2"  (cost=0.00..55.20 rows=2260 
                     ->  Seq Scan on table2  (cost=0.00..32.60 rows=2260 
(8 rows)

   And the attached patch is a draft, it works for this case.

Jerry Yu

Attachment: push_qual_to_except.diff
Description: Binary data

Sent via pgsql-hackers mailing list (
To make changes to your subscription:

Reply via email to