Hello,
I posted a similar problem here on Saturday. Now I had a little time
looking at the queries we send.
This is the output of PG 6.2.1
--
boersenspiel=> explain SELECT DISTINCT spieler_nr, Trans.wpk_nr,
state, anzahl, buyprice, buydate, sellprice, selldate, Kurse.wpk_nr,
name, curr, kurs, datum , Trans.oid from Trans, Kurse WHERE
Trans.wpk_nr=Kurse.wpk_nr AND spieler_nr=3 ORDER BY Trans.wpk_nr ,
selldate USING >; NOTICE:QUERY PLAN:
Unique (cost=0.00 size=0 width=0)
-> Sort (cost=4.10 size=0 width=0)
-> Nested Loop (cost=4.10 size=1 width=73)
-> Index Scan on trans (cost=2.05 size=1 width=41)
-> Index Scan on kurse (cost=2.05 size=14305 width=32)
Now the same query in 6.3:
Unique (cost=1164.21 size=0 width=0)
-> Sort (cost=1164.21 size=0 width=0)
-> Hash Join (cost=1164.21 size=1 width=73)
-> Seq Scan on kurse (cost=688.07 size=14305 width=32)
-> Hash (cost=0.00 size=0 width=0)
-> Index Scan on trans (cost=2.05 size=1
width=41)
All indices are created (all btrees), but the index on kurse doesn't
seem to be used.
> Guess what - it magically fixed itself.
>
> If you want to talk about things that *bother* me, this one tops the pack.
>
> The same query now returns an index hash query plan, which executes in a few
> seconds and requires little memory (as opposed to looped sequential scans
> requiring 500MB on the server).
>
> This is really, really, odd.
Dito.
BTW.: I tried to apply the patches from Massimo, as the only major
problem for us in 6.2.1p6 is the buggy deadlock code. Anybody managed
to get it working?
Ciao
Ulrich
Ulrich Voss \ \ / /__ / ___|__ _| |
VoCal web publishing \ \ / / _ \| | / _` | |
voss@vocalweb.de \ V / (_) | |__| (_| | |
http://www.vocalweb.de \_/ \___/ \____\__,_|_|
Tel: 0203-306-1560 web publishing