Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] Reminder: Indices are not used

From: "Boersenspielteam" <boersenspiel(at)vocalweb(dot)de>
To: "Vadim B(dot) Mikheev" <vadim(at)sable(dot)krasnoyarsk(dot)su>
Cc: PostgreSQL Hackers <hackers(at)postgreSQL(dot)org>
Subject: Re: [HACKERS] Reminder: Indices are not used
Date: 1998-03-31 12:30:35
Message-ID: 199803311034.MAA03631@binky.de.uu.net (view raw or flat)
Thread:
Lists: pgsql-hackers
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*
---------------------------------------------------------------------------

In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 1998-03-31 15:28:29
Subject: Re: indexing words
Previous:From: Maurice GittensDate: 1998-03-31 09:12:07
Subject: StrNCpy

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group