Thread

Re: [HACKERS] subselect and optimizer

Vadim B. Mikheev <vadim@sable.krasnoyarsk.su> — 1998-04-14T07:28:39Z

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