Small problem with PlaceHolderVar mechanism
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=50 width=244)
-> HashAggregate (cost=483.12..483.62 rows=50 width=4)
-> Seq Scan on tenk1 b (cost=0.00..483.00 rows=50 width=4)
Filter: (0 = 1)
-> Index Scan using tenk1_unique1 on tenk1 a (cost=0.00..6.27 rows=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
On Tue, Apr 28, 2009 at 4:41 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Another place where planner regression tests might've helped :-(
I would suggest we start gathering up such tests in an sql file now
and worry about how to compare the output later. There are a lot of
people who can put together some perl hackery to filter the results
but only a relatively few people who can design good targeted sql
tests.
And later we can always reuse the same sql tests with equivalent xml
hackery to filter the desired features or whatever other interface we
come up with.
--
greg
Greg Stark wrote:
On Tue, Apr 28, 2009 at 4:41 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Another place where planner regression tests might've helped :-(
I would suggest we start gathering up such tests in an sql file now
and worry about how to compare the output later.
At the very least, we could run them just make sure that they run with
no weird errors.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Greg Stark <stark@enterprisedb.com> writes:
On Tue, Apr 28, 2009 at 4:41 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Another place where planner regression tests might've helped :-(
I would suggest we start gathering up such tests in an sql file now
and worry about how to compare the output later.
If anyone feels like doing the legwork, there are interesting examples
in the CVS commit logs. I happened to notice the current problem while
I was re-reading the logs whilst checking the release notes. For no
particularly good reason I retried the examples mentioned in this item,
and behold it wasn't what I expected ...
2008-08-17 15:40 tgl
* src/backend/optimizer/path/joinrels.c: Add some defenses against
constant-FALSE outer join conditions. Since eval_const_expressions
will generally throw away anything that's ANDed with constant
FALSE, what we're left with given an example like
select * from tenk1 a where (unique1,0) in (select unique2,1 from
tenk1 b);
is a cartesian product computation, which is really not acceptable.
This is a regression in CVS HEAD compared to previous releases,
which were able to notice the impossible join condition in this
case --- though not in some related cases that are also improved by
this patch, such as
select * from tenk1 a left join tenk1 b on (a.unique1=b.unique2 and
0=1);
Fix by skipping evaluation of the appropriate side of the outer
join in cases where it's demonstrably unnecessary.
regards, tom lane
Alvaro Herrera <alvherre@commandprompt.com> writes:
Greg Stark wrote:
On Tue, Apr 28, 2009 at 4:41 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Another place where planner regression tests might've helped :-(
I would suggest we start gathering up such tests in an sql file now
and worry about how to compare the output later.
At the very least, we could run them just make sure that they run with
no weird errors.
Well, the cases where you get a weird error more often than not do get
memorialized in regular regression tests. The hard thing to test for
(in our current framework) is where you get the right answer but the
plan is stupider than it's supposed to be.
regards, tom lane