Re: [HACKERS] subselect and optimizer

>
> 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.

This is very helpful, and what I suspected.

Two issues.  First, I have heard reports that the optimizer in 6.3.2 is
better than 6.2.1, where indexes are used in 6.3.2 that were not used in
6.2.1.  In your case, you are seeing the opposite, but that may be OK
too.

Second, using an index to join two large tables is not always a good
thing.  The index can be scanned quickly, but it must find the heap for
every index entry, and that can cause the system to scan all over the
heap getting pages.  Sometimes, it is better to just scan through the
heap, and make your own hash index, which is the plan that it is being
used.

> 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.

This is also very helpful.  I had forgotten these options existed.

Hopefully we don't have a bug here.

--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)