propose to pushdown qual into EXCEPT

From: Armor <yupengstone(at)qq(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: propose to pushdown qual into EXCEPT
Date: 2016-12-23 14:01:45
Message-ID: tencent_423CD7A4633009A46075A3ED@qq.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
push_qual_to_except.diff application/octet-stream 1.1 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Fabien COELHO 2016-12-23 14:28:51 Re: Remove lower limit on checkpoint_timeout?
Previous Message Peter Eisentraut 2016-12-23 13:47:14 Re: Clarifying "server starting" messaging in pg_ctl start without --wait