Thread: Reminder: Indices are not used

Reminder: Indices are not used

From
Ulrich Voss
Date:
Hi Hackers,

I (and at least four others) reported strange behaviour of PG 6.3(.1),
which under certain circumstances doesn't use indices like the versions
before.

So we still have to use 6.2.1 (now with the Massimo patches). For us
6.2.1 is three times faster than 6.3.

I have narrowed the problem down a bit, so please take a look:

We have two tables:

CREATE TABLE trans (spieler_nr int4, wpk_nr int4, state char, anzahl
int4, buyprice float8, buydate date, sellprice float8, selldate date,
mail char) archive = none;
CREATE TABLE kurse (wpk_nr int4, name text, curr char4, kurs float8,
datum date, art char, high float8, low float8, open float8, old float8)
archive = none;

with three indices

CREATE  INDEX i_kurse_wpk_nr on kurse using btree ( wpk_nr int4_ops );
CREATE  INDEX i_trans_wpk_nr on trans using btree ( wpk_nr int4_ops );
CREATE  INDEX i_trans_spieler_nr on trans using btree ( spieler_nr
int4_ops );

If I do this select:

test=> explain SELECT * from Trans, Kurse where
Kurse.wpk_nr=Trans.wpk_nr and Trans.spieler_nr=3;
NOTICE:  QUERY PLAN:

Hash Join  (cost=408.60 size=1364 width=103)
  ->  Seq Scan on kurse  (cost=238.61 size=4958 width=65)
  ->  Hash  (cost=0.00 size=0 width=0)
        ->  Index Scan on trans  (cost=3.41 size=29 width=38)

I get the seq scan, which slows the query down tremendously compared to
6.2.

With the query:

test=> explain SELECT * from Trans, Kurse where
Kurse.wpk_nr=Trans.wpk_nr;
NOTICE:  QUERY PLAN:

Merge Join  (cost=7411.81 size=3343409 width=103)
  ->  Index Scan on kurse  (cost=337.90 size=4958 width=65)
  ->  Index Scan on trans  (cost=4563.60 size=71112 width=38)

everything is fine.

For your convenience I have a dump of the database with some real world
data und the selects (and some vacuums of course) on our web server.

You can download it via HTTP

http://www.vocalweb.de/test_index.dump.gz

It's around 1 Mb.

Please take a look at this, cause this seems to be a major bug in
optimizer/analyzer code somewhere and we are not the only ones who see
this problem.

TIA

Ulrich

Re: [HACKERS] Reminder: Indices are not used

From
Bruce Momjian
Date:
> test=> explain SELECT * from Trans, Kurse where
> Kurse.wpk_nr=Trans.wpk_nr and Trans.spieler_nr=3;
> NOTICE:  QUERY PLAN:
>
> Hash Join  (cost=408.60 size=1364 width=103)
>   ->  Seq Scan on kurse  (cost=238.61 size=4958 width=65)
>   ->  Hash  (cost=0.00 size=0 width=0)
>         ->  Index Scan on trans  (cost=3.41 size=29 width=38)
>
> I get the seq scan, which slows the query down tremendously compared to
> 6.2.

This does help.  Vadim, can you check this?

--
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)

Re: [HACKERS] Reminder: Indices are not used

From
"Vadim B. Mikheev"
Date:
Bruce Momjian wrote:
>
> > test=> explain SELECT * from Trans, Kurse where
> > Kurse.wpk_nr=Trans.wpk_nr and Trans.spieler_nr=3;
> > NOTICE:  QUERY PLAN:
> >
> > Hash Join  (cost=408.60 size=1364 width=103)
> >   ->  Seq Scan on kurse  (cost=238.61 size=4958 width=65)
> >   ->  Hash  (cost=0.00 size=0 width=0)
> >         ->  Index Scan on trans  (cost=3.41 size=29 width=38)
> >
> > I get the seq scan, which slows the query down tremendously compared to
> > 6.2.
>
> This does help.  Vadim, can you check this?

Ok.

Vadim

Re: [HACKERS] Reminder: Indices are not used

From
"Vadim B. Mikheev"
Date:
Could you post EXPLAINs from 6.2 for the _same_ data/schema ?

As for 6.3 - I just added

CREATE INDEX i_trans on trans (spieler_nr, wpk_nr);

and see near the same performance for all possible plans (NestLoop,
MergeJoin & HashJoin) - you are able to restrict possible plans
using -fX backend' option... NestLoop is slowest (I used -fh -fm to
get it).

My recommendation is to don't create 1-key indices - trans(spieler_nr) &
trans(wpk_nr), - but create 2-key indices - trans (spieler_nr, wpk_nr) &
trans (wpk_nr, spieler_nr).

Nevertheless, I'm interested in 6.2(.1 ?) EXPLAIN..

Vadim

Ulrich Voss wrote:
>
> Hi Hackers,
>
> I (and at least four others) reported strange behaviour of PG 6.3(.1),
> which under certain circumstances doesn't use indices like the versions
> before.
>
> So we still have to use 6.2.1 (now with the Massimo patches). For us
> 6.2.1 is three times faster than 6.3.
>
> I have narrowed the problem down a bit, so please take a look:
>
> We have two tables:
>
> CREATE TABLE trans (spieler_nr int4, wpk_nr int4, state char, anzahl
> int4, buyprice float8, buydate date, sellprice float8, selldate date,
> mail char) archive = none;
> CREATE TABLE kurse (wpk_nr int4, name text, curr char4, kurs float8,
> datum date, art char, high float8, low float8, open float8, old float8)
> archive = none;
>
> with three indices
>
> CREATE  INDEX i_kurse_wpk_nr on kurse using btree ( wpk_nr int4_ops );
> CREATE  INDEX i_trans_wpk_nr on trans using btree ( wpk_nr int4_ops );
> CREATE  INDEX i_trans_spieler_nr on trans using btree ( spieler_nr
> int4_ops );
>
> If I do this select:
>
> test=> explain SELECT * from Trans, Kurse where
> Kurse.wpk_nr=Trans.wpk_nr and Trans.spieler_nr=3;
> NOTICE:  QUERY PLAN:
>
> Hash Join  (cost=408.60 size=1364 width=103)
>   ->  Seq Scan on kurse  (cost=238.61 size=4958 width=65)
>   ->  Hash  (cost=0.00 size=0 width=0)
>         ->  Index Scan on trans  (cost=3.41 size=29 width=38)
>
> I get the seq scan, which slows the query down tremendously compared to
> 6.2.
>
> With the query:
>
> test=> explain SELECT * from Trans, Kurse where
> Kurse.wpk_nr=Trans.wpk_nr;
> NOTICE:  QUERY PLAN:
>
> Merge Join  (cost=7411.81 size=3343409 width=103)
>   ->  Index Scan on kurse  (cost=337.90 size=4958 width=65)
>   ->  Index Scan on trans  (cost=4563.60 size=71112 width=38)
>
> everything is fine.
>
> For your convenience I have a dump of the database with some real world
> data und the selects (and some vacuums of course) on our web server.
>
> You can download it via HTTP
>
> http://www.vocalweb.de/test_index.dump.gz
>
> It's around 1 Mb.
>
> Please take a look at this, cause this seems to be a major bug in
> optimizer/analyzer code somewhere and we are not the only ones who see
> this problem.
>
> TIA
>
> Ulrich

Re: [HACKERS] Reminder: Indices are not used

From
"Boersenspielteam"
Date:
Hi,

boersenspiel=> explain SELECT * from Trans, Kurse where
Kurse.wpk_nr=Trans.wpk_nr and Trans.spieler_nr=3; NOTICE: QUERY PLAN:

Nested Loop  (cost=6.15 size=2 width=103)
  ->   Index Scan on trans  (cost=2.05 size=2 width=38)
  ->   Index Scan on kurse  (cost=2.05 size=14307 width=65)

EXPLAIN

(Funny, the query which uses indices the right way in 6.3 is wrong in
6.2.1, but who cares if multi-key-indices get used ...

boersenspiel=> explain SELECT * from Trans, Kurse where
Kurse.wpk_nr=Trans.wpk_n r; NOTICE: QUERY PLAN:

Hash Join  (cost=18425.21 size=175546 width=103)
  ->   Seq Scan on trans  (cost=8134.02 size=175546 width=38)
  ->   Hash  (cost=0.00 size=0 width=0)
    ->     Seq Scan on kurse  (cost=712.13 size=14307 width=65)

EXPLAIN
)

> Could you post EXPLAINs from 6.2 for the _same_ data/schema ?
>
> As for 6.3 - I just added
>
> CREATE INDEX i_trans on trans (spieler_nr, wpk_nr);
>
> and see near the same performance for all possible plans (NestLoop,
> MergeJoin & HashJoin) - you are able to restrict possible plans
> using -fX backend' option... NestLoop is slowest (I used -fh -fm to
> get it).
>
> My recommendation is to don't create 1-key indices - trans(spieler_nr) &
> trans(wpk_nr), - but create 2-key indices - trans (spieler_nr, wpk_nr) &
> trans (wpk_nr, spieler_nr).
>
> Nevertheless, I'm interested in 6.2(.1 ?) EXPLAIN..
>
> Vadim
>
> Ulrich Voss wrote:
> >
> > Hi Hackers,
> >
> > I (and at least four others) reported strange behaviour of PG 6.3(.1),
> > which under certain circumstances doesn't use indices like the versions
> > before.
> >
> > So we still have to use 6.2.1 (now with the Massimo patches). For us
> > 6.2.1 is three times faster than 6.3.
> >
> > I have narrowed the problem down a bit, so please take a look:
> >
> > We have two tables:
> >
> > CREATE TABLE trans (spieler_nr int4, wpk_nr int4, state char, anzahl
> > int4, buyprice float8, buydate date, sellprice float8, selldate date,
> > mail char) archive = none;
> > CREATE TABLE kurse (wpk_nr int4, name text, curr char4, kurs float8,
> > datum date, art char, high float8, low float8, open float8, old float8)
> > archive = none;
> >
> > with three indices
> >
> > CREATE  INDEX i_kurse_wpk_nr on kurse using btree ( wpk_nr int4_ops );
> > CREATE  INDEX i_trans_wpk_nr on trans using btree ( wpk_nr int4_ops );
> > CREATE  INDEX i_trans_spieler_nr on trans using btree ( spieler_nr
> > int4_ops );
> >
> > If I do this select:
> >
> > test=> explain SELECT * from Trans, Kurse where
> > Kurse.wpk_nr=Trans.wpk_nr and Trans.spieler_nr=3;
> > NOTICE:  QUERY PLAN:
> >
> > Hash Join  (cost=408.60 size=1364 width=103)
> >   ->  Seq Scan on kurse  (cost=238.61 size=4958 width=65)
> >   ->  Hash  (cost=0.00 size=0 width=0)
> >         ->  Index Scan on trans  (cost=3.41 size=29 width=38)
> >
> > I get the seq scan, which slows the query down tremendously compared to
> > 6.2.
> >
> > With the query:
> >
> > test=> explain SELECT * from Trans, Kurse where
> > Kurse.wpk_nr=Trans.wpk_nr;
> > NOTICE:  QUERY PLAN:
> >
> > Merge Join  (cost=7411.81 size=3343409 width=103)
> >   ->  Index Scan on kurse  (cost=337.90 size=4958 width=65)
> >   ->  Index Scan on trans  (cost=4563.60 size=71112 width=38)
> >
> > everything is fine.
> > [...]

Ciao

Das Boersenspielteam.

---------------------------------------------------------------------------
                          http://www.boersenspiel.de
                           Das Boersenspiel im Internet
             *Realitaetsnah*  *Kostenlos*  *Ueber 6000 Spieler*
---------------------------------------------------------------------------

Re: [HACKERS] Reminder: Indices are not used

From
"Vadim B. Mikheev"
Date:
Thanks for help!

This is patch for src/backend/optimizer/path/prune.c.
Access pathes of pruned joinrels were not merged and better
pathes were lost, sometimes...

Vadim
*** prune.c.orig    Thu Apr  2 14:56:54 1998
--- prune.c    Thu Apr  2 15:16:17 1998
***************
*** 61,99 ****
  static List *
  prune_joinrel(Rel *rel, List *other_rels)
  {
!     List       *cur = NIL;
!     List       *return_list = NIL;

!     /* find first relation that doesn't match */
!     foreach(cur, other_rels)
      {
!         Rel           *other_rel = (Rel *) lfirst(cur);
!
!         if (!same(rel->relids, other_rel->relids))
!             break;
!     }
!
!     /* we now know cur doesn't match, or is NIL */
!     return_list = cur;
!
!     /*
!      * remove relations that do match, we use lnext so we can remove
!      * easily
!      */
!     while (cur != NIL && lnext(cur) != NIL)
!     {
!         Rel           *other_rel = (Rel *) lfirst(lnext(cur));
!
          if (same(rel->relids, other_rel->relids))
          {
              rel->pathlist = add_pathlist(rel,
                                           rel->pathlist,
                                           other_rel->pathlist);
-             lnext(cur) = lnext(lnext(cur));        /* delete it */
          }
!         cur = lnext(cur);
      }
!     return return_list;
  }

  /*
--- 61,85 ----
  static List *
  prune_joinrel(Rel *rel, List *other_rels)
  {
!     List       *i = NIL;
!     List       *result = NIL;

!     foreach(i, other_rels)
      {
!         Rel       *other_rel = (Rel *) lfirst(i);
!
          if (same(rel->relids, other_rel->relids))
          {
              rel->pathlist = add_pathlist(rel,
                                           rel->pathlist,
                                           other_rel->pathlist);
          }
!         else
!         {
!             result = nconc(result, lcons(other_rel, NIL));
!         }
      }
!     return (result);
  }

  /*

Re: [HACKERS] Reminder: Indices are not used

From
Bruce Momjian
Date:
> Thanks for help!
>
> This is patch for src/backend/optimizer/path/prune.c.
> Access pathes of pruned joinrels were not merged and better
> pathes were lost, sometimes...

Gee, I am sorry Vadim.  This is one of the optimizer functions I tried
to clean up, but obviously broke it.

>
> *** prune.c.orig    Thu Apr  2 14:56:54 1998
> --- prune.c    Thu Apr  2 15:16:17 1998
> ***************
> *** 61,99 ****


--
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)

Re: [HACKERS] Reminder: Indices are not used

From
"Boersenspielteam"
Date:
Thanks Vadim!

This patch does help.

The simple join still uses two sequential scans though.

Is your tip regarding single-key vs. multi-key-indices still valid?
I don't see any performance difference between these two.

BTW: Has the spinlock code, that was discussed some days ago, made it
into 6.3.1.?

Ciao

Ulrich

> Thanks for help!
>
> This is patch for src/backend/optimizer/path/prune.c.
> Access pathes of pruned joinrels were not merged and better
> pathes were lost, sometimes...
>
> Vadim
>

Ciao

Das Boersenspielteam.

---------------------------------------------------------------------------
                          http://www.boersenspiel.de
                           Das Boersenspiel im Internet
             *Realitaetsnah*  *Kostenlos*  *Ueber 6000 Spieler*
---------------------------------------------------------------------------

Re: [HACKERS] Reminder: Indices are not used

From
dg@illustra.com (David Gould)
Date:
Das Boersenspielteam:
>
> BTW: Has the spinlock code, that was discussed some days ago, made it
> into 6.3.1.?

I have not sent in the patch yet, so it is not in 6.3.1. I have a couple of
other tasks related to my real life that have cut into my time lately. I
hope to free up and finish the spinlock patch next week.

Thanks
-dg

David Gould            dg@illustra.com           510.628.3783 or 510.305.9468
Informix Software  (No, really)         300 Lakeside Drive  Oakland, CA 94612
 - Linux. Not because it is free. Because it is better.