Re: [HACKERS] Reminder: Indices are not used
От | Boersenspielteam |
---|---|
Тема | Re: [HACKERS] Reminder: Indices are not used |
Дата | |
Msg-id | 199803311034.MAA03631@binky.de.uu.net обсуждение исходный текст |
Ответ на | Re: [HACKERS] Reminder: Indices are not used ("Vadim B. Mikheev" <vadim@sable.krasnoyarsk.su>) |
Список | 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* ---------------------------------------------------------------------------
В списке pgsql-hackers по дате отправления: