Re: [HACKERS] Reminder: Indices are not used

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