Thread: Reminder: Indices are not used
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
> 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)
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
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
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*
---------------------------------------------------------------------------
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);
}
/*
> 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)
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*
---------------------------------------------------------------------------
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.