Re: Hash Join vs Nested Loops in 7.2.1 ...

Tom Lane wrote:

>
>>What I neglected to mention was that the planner was *choosing* the
>>slower hashjoin plan over the much faster nested loop plan without any
>>PGOPTIONS set or any postgresql.conf changes to enable_*, thus the
>>motivation for a "thumb on the scales." After upping the number of
>>shared buffers, it has begun choosing the smart plan 1-second plan,
>
> Interesting. The estimated cost of indexscans is dependent on
> shared_buffers, but not so dependent that I'd have expected it to make a
> difference here. What were the EXPLAIN numbers you were getting, again?

The default plan looked like the "-fn" plan below.

I guess I should also mention there are a number of columns in the 'story'
table that are not involved in the query or plans, but would add to the
'weight' of a row if that makes a difference to the planner. I omitted them
from my earlier listings thinking they were superfluous to this discussion.

Plan with PGOPTIONS = "-fn":

Limit (cost=15409053054.71..15409053054.73 rows=1 width=12)
-> Unique (cost=15409053054.71..15409053054.73 rows=1 width=12)
-> Sort (cost=15409053054.71..15409053054.71 rows=9 width=12)
-> Nested Loop (cost=100000000.00..15409053054.57 rows=9 width=12)
-> Index Scan using word_idx on dict d1 (cost=0.00..5.98 rows=1 width=4)
-> Index Scan using story_dict_pkey on story_dict ftd1
(cost=0.00..15309052993.63 rows=4398 width=8)
SubPlan
-> Merge Join (cost=429157.62..435130.62 rows=1 width=16)
-> Sort (cost=13.11..13.11 rows=1 width=12)
-> Hash Join (cost=5.98..13.10 rows=1 width=12)
-> Index Scan using story_dict_tk_idx on story_dict ftd2
(cost=0.00..6.59 rows=106 width=8)
-> Hash (cost=5.98..5.98 rows=1 width=4)
-> Index Scan using word_idx on dict d2 (cost=0.00..5.98 rows=1
width=4)
-> Sort (cost=429144.50..429144.50 rows=2389196 width=4)
-> Seq Scan on story ft2 (cost=0.00..86701.96 rows=2389196 width=4)
-> Merge Join (cost=429157.62..435130.62 rows=1 width=16)
-> Sort (cost=13.11..13.11 rows=1 width=12)
-> Hash Join (cost=5.98..13.10 rows=1 width=12)
-> Index Scan using story_dict_tk_idx on story_dict ftd3
(cost=0.00..6.59 rows=106 width=8)
-> Hash (cost=5.98..5.98 rows=1 width=4)
-> Index Scan using word_idx on dict d3 (cost=0.00..5.98 rows=1
width=4)
-> Sort (cost=429144.50..429144.50 rows=2389196 width=4)
-> Seq Scan on story ft3 (cost=0.00..86701.96 rows=2389196 width=4)

Plan with PGOPTIONS = "-fh":

Limit (cost=635283.31..635283.33 rows=1 width=12)
-> Unique (cost=635283.31..635283.33 rows=1 width=12)
-> Sort (cost=635283.31..635283.31 rows=9 width=12)
-> Nested Loop (cost=0.00..635283.17 rows=9 width=12)
-> Index Scan using word_idx on dict d1 (cost=0.00..5.98 rows=1 width=4)
-> Index Scan using story_dict_pkey on story_dict ftd1
(cost=0.00..635222.22 rows=4398 width=8)
SubPlan
-> Nested Loop (cost=0.00..16.07 rows=1 width=16)
-> Nested Loop (cost=0.00..12.00 rows=1 width=12)
-> Index Scan using word_idx on dict d2 (cost=0.00..5.98 rows=1
width=4)
-> Index Scan using story_dict_pkey on story_dict ftd2
(cost=0.00..6.01 rows=1 width=8)
-> Index Scan using story_pkey on story ft2 (cost=0.00..4.06 rows=1
width=4)
-> Nested Loop (cost=0.00..16.07 rows=1 width=16)
-> Nested Loop (cost=0.00..12.00 rows=1 width=12)
-> Index Scan using word_idx on dict d3 (cost=0.00..5.98 rows=1
width=4)
-> Index Scan using story_dict_pkey on story_dict ftd3
(cost=0.00..6.01 rows=1 width=8)
-> Index Scan using story_pkey on story ft3 (cost=0.00..4.06 rows=1
width=4)