Re: [HACKERS] subselect and optimizer

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

I have tested with 6.3.2 beta. (Sorry test data is not same as my
original posting) Here are results:

"postal" table holds ~110k records. "prefecture" table has 47 records.
query is as follows:

select * from prefecture,postal where prefecture.pid = postal.pid and
postal.town in (select town from postal where newcode = '1040061');

All of columns that appear above have btree index.

No options to backend produced a nested loop plan.

Nested Loop (cost=98.90 size=11888 width=92)
-> Seq Scan on prefecture (cost=2.55 size=47 width=26)
-> Index Scan on postal (cost=2.05 size=11888 width=66)
SubPlan
-> Index Scan on postal (cost=2.05 size=2 width=12)

> 26.78 real 22.35 user 0.58 sys

Next I gave -fn to the backend.

Hash Join (cost=6246.48 size=11888 width=92)
-> Seq Scan on postal (cost=5842.97 size=11888 width=66)
SubPlan
-> Index Scan on postal (cost=2.05 size=2 width=12)
-> Hash (cost=0.00 size=0 width=0)
-> Seq Scan on prefecture (cost=2.55 size=47 width=26)

> 24.97 real 21.30 user 0.50 sys

Finally I tried merge join.

Merge Join (cost=8580.86 size=11888 width=92)
-> Seq Scan (cost=2.55 size=0 width=0)
-> Sort (cost=2.55 size=0 width=0)
-> Seq Scan on prefecture (cost=2.55 size=47 width=26)
-> Index Scan on postal (cost=8181.90 size=11888 width=66)
SubPlan
-> Index Scan on postal (cost=2.05 size=2 width=12)

>> 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)
> 25.63 real 22.13 user 0.51 sys

So in my case Merge Join was the fastest, Hash Join and Nested Loop
(PostgreSQL decides this was the best) were almost same.
I tried for several times and the tendency seemed not changed.
Anyway the differences were not so big.
--
Tatsuo Ishii
t-ishii(at)sra(dot)co(dot)jp