On Wed, 29 Jul 2020 at 03:33, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> PG Bug reporting form <noreply@postgresql.org> writes:
> > Consider the following setup of empty tables partitioned first by `key1` and
> > then by `key2`:
> > ...
> > EXPLAIN ANALYZE
> > SELECT *
> > FROM demo1
> > JOIN demo2 ON demo1.key2 = demo2.key2
> > WHERE demo1.key2 = 123
> > AND demo2.key2 = 123
> > AND FALSE;
>
> What seems to be happening here is that expression simplification reduces
> the WHERE clause to just constant-false, ie
>
> SELECT * FROM demo1 JOIN demo2 ON demo1.key2 = demo2.key2 WHERE FALSE;
I wonder if we could maybe cheaply do something better for this case.
There's a paragraph of comment in distribute_qual_to_rels() which
reads:
* When the clause contains no volatile functions either, it is actually a
* pseudoconstant clause that will not change value during any one
* execution of the plan, and hence can be used as a one-time qual in a
* gating Result plan node. We put such a clause into the regular
* RestrictInfo lists for the moment, but eventually createplan.c will
* pull it out and make a gating Result node immediately above whatever
* plan node the pseudoconstant clause is assigned to. It's usually best
* to put a gating node as high in the plan tree as possible. If we are
* not below an outer join, we can actually push the pseudoconstant qual
* all the way to the top of the tree. If we are below an outer join, we
* leave the qual at its original syntactic level (we could push it up to
* just below the outer join, but that seems more complex than it's
* worth).
I guess that's so we generate the gating qual as high as possible in
the plan tree, which is good for saving in the executor and saves work
in createplan.c when the gating qual is const-false since we won't
need to generate any plan below that. However, it's not very good for
the planner as we still must create paths etc for the base relations.
I wonder if we can do a bit better and make push const-false quals to
the base-rels too.
The attached is roughly what I mean. It's absent of any comment
updates. Just aimed as a conversation aid at the moment.
It does have a side-effect of having remove_rel_from_query() transfer
the join-level gating qual to the baserel when a join is removed which
does double up on the gating qual and turns "One-Time Filter: false"
into "One-Time Filter: (false AND false)". We could check for that in
remove_rel_from_query(), but it's a bit ugly... or maybe the whole
thing is ugly, but I"m happy to hear thoughts on it.
FWIW, Marcin's example case with the patch does:
QUERY PLAN
------------------------------------------------------------------------------------
Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.001..0.002
rows=0 loops=1)
One-Time Filter: false
Planning Time: 0.077 ms
Execution Time: 0.015 ms
(4 rows)
David