Re: [HACKERS] subselect and optimizer - Mailing list pgsql-hackers

> Yep, exactly. The query with the where clause is fixed after
> applying Vadim's prune.c patch, simple join still uses two seq scans
> :-(
>
> I uploaded test data and Vadim fixed one file, but asked you
> (Bruce) to look over other files of the optimizer code. There seem
> to be other bugs in the optimizer code, which were introduced between
> 6.2.1 and 6.3. We have seen about 5-6 error reports from different
> people, from the simpliest queries like my simple join to rather
> complex subqueries. But when a simple join doesn't work (ok, it
> works, but kind of crawls), this error is supposed to pop up under
> other circumstances too.
>
> Hope you can find this nasty little bug, cause it makes postgres
> unusable. Especially before going into development again.
>
> See the mailinglist archives for a post of mine. There is a link in
> it,where you can download the test data, it should still be
> there. (don't have access to this from home)
>
> I greatly appreciate all the time and hard work all you
> PostgreSQL-hackers and contributors put into this fantastic freeware
> product. Just to let you know.

Here is the prune.c file from 6.2.1.  Please try it and let me know if
it fixes the problem:

---------------------------------------------------------------------------

/*-------------------------------------------------------------------------
 *
 * prune.c--
 *      Routines to prune redundant paths and relations
 *
 * Copyright (c) 1994, Regents of the University of California
 *
 *
 * IDENTIFICATION
 *      $Header: /usr/local/cvsroot/pgsql/src/backend/optimizer/path/prune.c,v 1.6 1997/09/08 21:45:08 momjian Exp $
 *
 *-------------------------------------------------------------------------
 */
#include "postgres.h"

#include "nodes/pg_list.h"
#include "nodes/relation.h"

#include "optimizer/internal.h"
#include "optimizer/cost.h"
#include "optimizer/paths.h"
#include "optimizer/pathnode.h"

#include "utils/elog.h"


static List *prune_joinrel(Rel *rel, List *other_rels);

/*
 * prune-joinrels--
 *      Removes any redundant relation entries from a list of rel nodes
 *      'rel-list'.
 *
 * Returns the resulting list.
 *
 */
List       *
prune_joinrels(List *rel_list)
{
    List       *temp_list = NIL;

    if (rel_list != NIL)
    {
        temp_list = lcons(lfirst(rel_list),
                   prune_joinrels(prune_joinrel((Rel *) lfirst(rel_list),
                                                lnext(rel_list))));
    }
    return (temp_list);
}

/*
 * prune-joinrel--
 *      Prunes those relations from 'other-rels' that are redundant with
 *      'rel'.  A relation is redundant if it is built up of the same
 *      relations as 'rel'.  Paths for the redundant relation are merged into
 *      the pathlist of 'rel'.
 *
 * Returns a list of non-redundant relations, and sets the pathlist field
 * of 'rel' appropriately.
 *
 */
static List *
prune_joinrel(Rel *rel, List *other_rels)
{
    List       *i = NIL;
    List       *t_list = NIL;
    List       *temp_node = NIL;
    Rel           *other_rel = (Rel *) NULL;

    foreach(i, other_rels)
    {
        other_rel = (Rel *) lfirst(i);
        if (same(rel->relids, other_rel->relids))
        {
            rel->pathlist = add_pathlist(rel,
                                         rel->pathlist,
                                         other_rel->pathlist);
            t_list = nconc(t_list, NIL);        /* XXX is this right ? */
        }
        else
        {
            temp_node = lcons(other_rel, NIL);
            t_list = nconc(t_list, temp_node);
        }
    }
    return (t_list);
}

/*
 * prune-rel-paths--
 *      For each relation entry in 'rel-list' (which corresponds to a join
 *      relation), set pointers to the unordered path and cheapest paths
 *      (if the unordered path isn't the cheapest, it is pruned), and
 *      reset the relation's size field to reflect the join.
 *
 * Returns nothing of interest.
 *
 */
void
prune_rel_paths(List *rel_list)
{
    List       *x = NIL;
    List       *y = NIL;
    Path       *path = NULL;
    Rel           *rel = (Rel *) NULL;
    JoinPath   *cheapest = (JoinPath *) NULL;

    foreach(x, rel_list)
    {
        rel = (Rel *) lfirst(x);
        rel->size = 0;
        foreach(y, rel->pathlist)
        {
            path = (Path *) lfirst(y);

            if (!path->p_ordering.ord.sortop)
            {
                break;
            }
        }
        cheapest = (JoinPath *) prune_rel_path(rel, path);
        if (IsA_JoinPath(cheapest))
        {
            rel->size = compute_joinrel_size(cheapest);
        }
        else
            elog(WARN, "non JoinPath called");
    }
}


/*
 * prune-rel-path--
 *      Compares the unordered path for a relation with the cheapest path. If
 *      the unordered path is not cheapest, it is pruned.
 *
 *      Resets the pointers in 'rel' for unordered and cheapest paths.
 *
 * Returns the cheapest path.
 *
 */
Path       *
prune_rel_path(Rel *rel, Path *unorderedpath)
{
    Path       *cheapest = set_cheapest(rel, rel->pathlist);

    /* don't prune if not pruneable  -- JMH, 11/23/92 */
    if (unorderedpath != cheapest
        && rel->pruneable)
    {

        rel->unorderedpath = (Path *) NULL;
        rel->pathlist = lremove(unorderedpath, rel->pathlist);
    }
    else
    {
        rel->unorderedpath = (Path *) unorderedpath;
    }

    return (cheapest);
}

/*
 * merge-joinrels--
 *      Given two lists of rel nodes that are already
 *      pruned, merge them into one pruned rel node list
 *
 * 'rel-list1' and
 * 'rel-list2' are the rel node lists
 *
 * Returns one pruned rel node list
 */
List       *
merge_joinrels(List *rel_list1, List *rel_list2)
{
    List       *xrel = NIL;

    foreach(xrel, rel_list1)
    {
        Rel           *rel = (Rel *) lfirst(xrel);

        rel_list2 = prune_joinrel(rel, rel_list2);
    }
    return (append(rel_list1, rel_list2));
}

/*
 * prune_oldrels--
 *      If all the joininfo's in a rel node are inactive,
 *      that means that this node has been joined into
 *      other nodes in all possible ways, therefore
 *      this node can be discarded.  If not, it will cause
 *      extra complexity of the optimizer.
 *
 * old_rels is a list of rel nodes
 *
 * Returns a new list of rel nodes
 */
List       *
prune_oldrels(List *old_rels)
{
    Rel           *rel;
    List       *joininfo_list,
               *xjoininfo;

    if (old_rels == NIL)
        return (NIL);

    rel = (Rel *) lfirst(old_rels);
    joininfo_list = rel->joininfo;
    if (joininfo_list == NIL)
        return (lcons(rel, prune_oldrels(lnext(old_rels))));

    foreach(xjoininfo, joininfo_list)
    {
        JInfo       *joininfo = (JInfo *) lfirst(xjoininfo);

        if (!joininfo->inactive)
            return (lcons(rel, prune_oldrels(lnext(old_rels))));
    }
    return (prune_oldrels(lnext(old_rels)));
}


--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)