Thread

Re: [HACKERS] subselect and optimizer

Boersenspielteam <boersenspiel@vocalweb.de> — 1998-04-14T14:22:19Z

Hi,

then I think this one is solved.

I'll try to reproduce it on my machine, if I get the same results, I 
will be a quiet and happy Postgres user again ;-)

I don't have the message, that originated this thread, but is the 
slow subselect  from Tatsuo fixed?

Tatsua, can you test queries with the abckend options suggested by 
Vadim?

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

Ciao

Das Boersenspielteam.

---------------------------------------------------------------------------
                          http://www.boersenspiel.de
              	         Das Boersenspiel im Internet
             *Realitaetsnah*  *Kostenlos*  *Ueber 6000 Spieler*
---------------------------------------------------------------------------