I wrote:
> I guess we missed something about when it's safe to do this optimization.
I've applied the attached patch to fix this.
regards, tom lane
Index: joinpath.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/optimizer/path/joinpath.c,v
retrieving revision 1.126
diff -c -r1.126 joinpath.c
*** joinpath.c 19 Sep 2009 17:48:09 -0000 1.126
--- joinpath.c 25 Dec 2009 17:02:40 -0000
***************
*** 228,233 ****
--- 228,238 ----
* We can't remove the join if any inner-rel attributes are used above
* the join.
*
+ * Note that this test only detects use of inner-rel attributes in
+ * higher join conditions and the target list. There might be such
+ * attributes in pushed-down conditions at this join, too. We check
+ * that case below.
+ *
* As a micro-optimization, it seems better to start with max_attr and
* count down rather than starting with min_attr and counting up, on the
* theory that the system attributes are somewhat less likely to be wanted
***************
*** 253,265 ****
RestrictInfo *restrictinfo = (RestrictInfo *) lfirst(l);
/*
! * We are always considering an outer join here, so ignore pushed-down
! * clauses. Also ignore anything that doesn't have a mergejoinable
! * operator.
*/
if (restrictinfo->is_pushed_down)
! continue;
if (!restrictinfo->can_join ||
restrictinfo->mergeopfamilies == NIL)
continue; /* not mergejoinable */
--- 258,273 ----
RestrictInfo *restrictinfo = (RestrictInfo *) lfirst(l);
/*
! * If we find a pushed-down clause, it must have come from above the
! * outer join and it must contain references to the inner rel. (If
! * it had only outer-rel variables, it'd have been pushed down into
! * the outer rel.) Therefore, we can conclude that join removal
! * is unsafe without any examination of the clause contents.
*/
if (restrictinfo->is_pushed_down)
! return false;
+ /* Ignore if it's not a mergejoinable clause */
if (!restrictinfo->can_join ||
restrictinfo->mergeopfamilies == NIL)
continue; /* not mergejoinable */
