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@sra.co.jp