Tom Lane wrote:
> It appears that join_clause_is_redundant() is rejecting the clause as
> redundant. I suppose some part of that machinery gets confused by the
> fact that the RHS of the clause references both relations. The
> EquivalenceClass rewrite cleaned this whole area up greatly, so no
> surprise that the bug is gone in HEAD. No time to look at it more now.
Yeah, that's it.
I don't understand that code very well. Why does it think that the right
pathkeys of "test1.id = test2.id" and "test1.id = test1.id+test2.id" are
equal?
Checking for overlapping left and right relation sets in
join_clause_is_redundant seems to fix the problem, though I'm not sure
if there's some other cases that that doesn't catch.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Index: src/backend/optimizer/util/restrictinfo.c
===================================================================
RCS file: /home/hlinnaka/pgcvsrepository/pgsql/src/backend/optimizer/util/restrictinfo.c,v
retrieving revision 1.49
diff -c -r1.49 restrictinfo.c
*** src/backend/optimizer/util/restrictinfo.c 4 Oct 2006 00:29:55 -0000 1.49
--- src/backend/optimizer/util/restrictinfo.c 31 Jul 2007 08:58:40 -0000
***************
*** 651,656 ****
--- 651,659 ----
* of the latter, even though they might seem redundant by the pathkey
* membership test.
*
+ * The above logic only works for normal join clauses with non-overlapping
+ * left and right relation sets.
+ *
* Weird special case: if we have two clauses that seem redundant
* except one is pushed down into an outer join and the other isn't,
* then they're not really redundant, because one constrains the
***************
*** 673,685 ****
return refrinfo;
}
! /* check for redundant merge clauses */
! if (rinfo->mergejoinoperator != InvalidOid)
{
- /* do the cheap test first: is it a "var = const" clause? */
- if (bms_is_empty(rinfo->left_relids) ||
- bms_is_empty(rinfo->right_relids))
- return NULL; /* var = const, so not redundant */
cache_mergeclause_pathkeys(root, rinfo);
--- 676,689 ----
return refrinfo;
}
! /*
! * check for redundant merge clauses by comparing the pathkeys as
! * explained above. The check for can_join handles the special case
! * of "var = const", as well as any clauses with overlapping left
! * and right rel sets.
! */
! if (rinfo->mergejoinoperator != InvalidOid && rinfo->can_join)
{
cache_mergeclause_pathkeys(root, rinfo);
***************
*** 687,693 ****
{
RestrictInfo *refrinfo = (RestrictInfo *) lfirst(refitem);
! if (refrinfo->mergejoinoperator != InvalidOid)
{
cache_mergeclause_pathkeys(root, refrinfo);
--- 691,697 ----
{
RestrictInfo *refrinfo = (RestrictInfo *) lfirst(refitem);
! if (refrinfo->mergejoinoperator != InvalidOid && refrinfo->can_join)
{
cache_mergeclause_pathkeys(root, refrinfo);
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
