Re: Queries 15 times slower on 8.1 beta 2 than on 8.0 - Mailing list pgsql-performance

From Tom Lane
Subject Re: Queries 15 times slower on 8.1 beta 2 than on 8.0
Date
Msg-id 18133.1127433002@sss.pgh.pa.us
Whole thread Raw
In response to Re: Queries 15 times slower on 8.1 beta 2 than on 8.0  (Michael Fuhr <mike@fuhr.org>)
List pgsql-performance
Michael Fuhr <mike@fuhr.org> writes:
> Tom Lane says he's found the problem; I expect he'll be committing
> a fix shortly.

The attached patch allows it to generate the expected plan, at least
in the test case I tried.

            regards, tom lane

*** src/backend/optimizer/path/indxpath.c.orig    Sun Aug 28 18:47:20 2005
--- src/backend/optimizer/path/indxpath.c    Thu Sep 22 19:17:41 2005
***************
*** 955,969 ****
      /*
       * Examine each joinclause in the joininfo list to see if it matches any
       * key of any index.  If so, add the clause's other rels to the result.
-      * (Note: we consider only actual participants, not extraneous rels
-      * possibly mentioned in required_relids.)
       */
      foreach(l, rel->joininfo)
      {
          RestrictInfo *joininfo = (RestrictInfo *) lfirst(l);
          Relids    other_rels;

!         other_rels = bms_difference(joininfo->clause_relids, rel->relids);
          if (matches_any_index(joininfo, rel, other_rels))
              outer_relids = bms_join(outer_relids, other_rels);
          else
--- 955,967 ----
      /*
       * Examine each joinclause in the joininfo list to see if it matches any
       * key of any index.  If so, add the clause's other rels to the result.
       */
      foreach(l, rel->joininfo)
      {
          RestrictInfo *joininfo = (RestrictInfo *) lfirst(l);
          Relids    other_rels;

!         other_rels = bms_difference(joininfo->required_relids, rel->relids);
          if (matches_any_index(joininfo, rel, other_rels))
              outer_relids = bms_join(outer_relids, other_rels);
          else

pgsql-performance by date:

Previous
From: Josh Berkus
Date:
Subject: Re: Queries 15 times slower on 8.1 beta 2 than on 8.0
Next
From: "Anu Kucharlapati"
Date:
Subject: FW: Deadlock Issue with PostgreSQL