Thread

Re: [HACKERS] Reminder: Indices are not used

Vadim B. Mikheev <vadim@sable.krasnoyarsk.su> — 1998-03-31T08:20:55Z

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