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