I noticed that queries involving constant-false join conditions are a
lot dumber than they were a couple of months ago. For instance
regression=# explain select * from tenk1 a where (unique1,0) in (select unique2,1 from tenk1 b);
QUERY PLAN
-------------------------------------------------------------------------------------Nested Loop (cost=483.12..797.68
rows=50width=244) -> HashAggregate (cost=483.12..483.62 rows=50 width=4) -> Seq Scan on tenk1 b
(cost=0.00..483.00rows=50 width=4) Filter: (0 = 1) -> Index Scan using tenk1_unique1 on tenk1 a
(cost=0.00..6.27rows=1 width=244) Index Cond: (a.unique1 = b.unique2)
(6 rows)
CVS HEAD from mid-February produces
QUERY PLAN
------------------------------------------Result (cost=0.00..0.01 rows=1 width=0) One-Time Filter: false
(2 rows)
The reason this isn't working so well anymore is that initial pullup of
the IN sub-select produces a join condition that includes not "0 = 1"
but "0 = PlaceHolderVar(1)", which of course fails to simplify to a
constant. In fact, since the PlaceHolderVar is treated like a Var, it
ends up being a relation scan qualifier on "b" and not a one-time filter
at all.
On reflection I think the error here is that we should not blindly
insert the PlaceHolderVar() wrapper around *every* expression pulled up
from a subselect. We only need it for references that appear above the
lowest outer join that could null the subselect outputs. In examples
such as this one, the reference we are interested in is not above but
within the join condition of that outer join, so it doesn't need a
PlaceHolderVar.
I haven't finished working out a patch for this, but it looks like it's
fixable with relatively localized hacking in pull_up_simple_subquery
and resolvenew_in_jointree --- we can track exactly which part of the
query we are doing substitutions in, and insert substitutes with or
without PlaceHolderVar accordingly.
Another place where planner regression tests might've helped :-(
regards, tom lane