From: | "Vadim B(dot) Mikheev" <vadim(at)sable(dot)krasnoyarsk(dot)su> |
---|---|
To: | boersenspiel(at)vocalweb(dot)de |
Cc: | Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us>, hackers(at)postgreSQL(dot)org |
Subject: | Re: [HACKERS] subselect and optimizer |
Date: | 1998-04-14 07:28:39 |
Message-ID: | 35331027.AC58006A@sable.krasnoyarsk.su |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Boersenspielteam wrote:
>
> No, but in the more general case of a simple join over two tables
> with fields with an index declared on them.
>
> say: Select * from Trans, Spieler where
> Spieler.spieler_nr=Trans.spieler_nr
>
> Uses indices in 6.2.1, doesn't use them in 6.3.1 (two seq scans).
Sorry, old mail from you is lost - what was execution plan in 6.2.1 ?
In current I see that
Hash Join (cost=5905.62 size=3343409 width=8)
-> Seq Scan on trans (cost=3154.70 size=71112 width=4)
-> Hash (cost=0.00 size=0 width=0)
-> Seq Scan on kurse (cost=238.61 size=4958 width=4)
IS FASTEST plan ! Result is returned in ~ 56 sec.
Nested Loop (cost=148934.30 size=3343409 width=8)
-> Seq Scan on trans (cost=3154.70 size=71112 width=4)
-> Index Scan on kurse (cost=2.05 size=4958 width=4)
returns result in ~ 80 sec.
Merge Join (cost=7411.81 size=3343409 width=8)
-> Index Scan on kurse (cost=337.90 size=4958 width=4)
-> Index Scan on trans (cost=4563.60 size=71112 width=4)
is SLOWEST plan (~200 sec).
Please don't think that using indices is the best way in all cases...
BTW, you can use -fX _backend_ option to forbid some join methods -
I used '-o -fh' to get MJ plan and '-o -fh -fm' to test NL plan.
Vadim
From | Date | Subject | |
---|---|---|---|
Next Message | Maarten Boekhold | 1998-04-14 08:42:00 | Re: [HACKERS] still getting FATAL errors on btree's... |
Previous Message | Bruce Momjian | 1998-04-14 02:40:08 | Re: Async I/O |