Teach planner to short-circuit EXCEPT/INTERSECT with dummy inputs
When either inputs of an INTERSECT [ALL] operator are proven not to return
any results (a dummy rel), then mark the entire INTERSECT operation as
dummy.
Likewise, if an EXCEPT [ALL] operation's left input is proven empty, then
mark the entire operation as dummy.
With EXCEPT ALL, we can easily handle the right input being dummy as
we can return the left input without any processing. That can lead to
significant performance gains during query execution. We can't easily
handle dummy right inputs for EXCEPT (without ALL), as that would require
deduplication of the left input. Wiring up those Paths is likely more
complex than it's worth as the gains during execution aren't that great,
so let's leave that one to be handled by the normal Path generation code.
Author: David Rowley <dgrowleyml@gmail.com>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: https://postgr.es/m/CAApHDvri53PPF76c3M94_QNWbJfXjyCnjXuj_2=LYM-0m8WZtw@mail.gmail.com
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/9c9d41af4db7e7f81d0f9abc7dc16402386091b0
Modified Files
--------------
src/backend/optimizer/prep/prepunion.c | 63 +++++++++++++++++++++++++
src/test/regress/expected/union.out | 86 +++++++++++++++++++++++++++++++++-
src/test/regress/sql/union.sql | 38 ++++++++++++++-
3 files changed, 185 insertions(+), 2 deletions(-)
