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 width=12)
-> 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 width=12)
-> Seq Scan on table2 (cost=0.00..32.60 rows=2260 width=8)
(8 rows)
And the attached patch is a draft, it works for this case.
------------------
Jerry Yu
https://github.com/scarbrofair