Re: Optimizer problem with subselect.c?

"Daniel O'Neill" <daniel(at)fatport(dot)com> writes:
> [ complex test case ]

Got it. The patch against 7.4.* is attached and will appear in 7.4.2.

regards, tom lane

Index: createplan.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/optimizer/plan/createplan.c,v
retrieving revision 1.157.2.2
diff -c -r1.157.2.2 createplan.c
*** createplan.c 18 Jan 2004 00:31:53 -0000 1.157.2.2
--- createplan.c 29 Feb 2004 17:29:48 -0000
***************
*** 101,107 ****
static Sort *make_sort(Query *root, List *tlist, Plan *lefttree, int numCols,
AttrNumber *sortColIdx, Oid *sortOperators);
static Sort *make_sort_from_pathkeys(Query *root, Plan *lefttree,
! Relids relids, List *pathkeys);


/*
--- 101,107 ----
static Sort *make_sort(Query *root, List *tlist, Plan *lefttree, int numCols,
AttrNumber *sortColIdx, Oid *sortOperators);
static Sort *make_sort_from_pathkeys(Query *root, Plan *lefttree,
! List *pathkeys);


/*
***************
*** 1015,1021 ****
outer_plan = (Plan *)
make_sort_from_pathkeys(root,
outer_plan,
- best_path->jpath.outerjoinpath->parent->relids,
best_path->outersortkeys);
}

--- 1015,1020 ----
***************
*** 1025,1031 ****
inner_plan = (Plan *)
make_sort_from_pathkeys(root,
inner_plan,
- best_path->jpath.innerjoinpath->parent->relids,
best_path->innersortkeys);
}

--- 1024,1029 ----
***************
*** 1793,1799 ****
* Create sort plan to sort according to given pathkeys
*
* 'lefttree' is the node which yields input tuples
- * 'relids' is the set of relids represented by the input node
* 'pathkeys' is the list of pathkeys by which the result is to be sorted
*
* We must convert the pathkey information into arrays of sort key column
--- 1791,1796 ----
***************
*** 1806,1813 ****
* adding a Result node just to do the projection.
*/
static Sort *
! make_sort_from_pathkeys(Query *root, Plan *lefttree,
! Relids relids, List *pathkeys)
{
List *tlist = lefttree->targetlist;
List *sort_tlist;
--- 1803,1809 ----
* adding a Result node just to do the projection.
*/
static Sort *
! make_sort_from_pathkeys(Query *root, Plan *lefttree, List *pathkeys)
{
List *tlist = lefttree->targetlist;
List *sort_tlist;
***************
*** 1852,1863 ****
}
if (!resdom)
{
! /* No matching Var; look for an expression */
foreach(j, keysublist)
{
pathkey = lfirst(j);
! if (bms_is_subset(pull_varnos(pathkey->key), relids))
! break;
}
if (!j)
elog(ERROR, "could not find pathkey item to sort");
--- 1848,1869 ----
}
if (!resdom)
{
! /* No matching Var; look for a computable expression */
foreach(j, keysublist)
{
+ List *exprvars;
+ List *k;
+
pathkey = lfirst(j);
! exprvars = pull_var_clause(pathkey->key, false);
! foreach(k, exprvars)
! {
! if (!tlist_member(lfirst(k), tlist))
! break;
! }
! freeList(exprvars);
! if (!k)
! break; /* found usable expression */
}
if (!j)
elog(ERROR, "could not find pathkey item to sort");