Re: Hash Join vs Nested Loops in 7.2.1 ...
От | Ed Loehr |
---|---|
Тема | Re: Hash Join vs Nested Loops in 7.2.1 ... |
Дата | |
Msg-id | 3CB34FC7.2040103@bluepolka.net обсуждение исходный текст |
Ответ на | Hash Join vs Nested Loops in 7.2.1 ... (Ed Loehr <pggeneral@bluepolka.net>) |
Список | pgsql-general |
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)
В списке pgsql-general по дате отправления: