how to force planner to do nestloops instead of merge joins
От | Luis Amigo |
---|---|
Тема | how to force planner to do nestloops instead of merge joins |
Дата | |
Msg-id | 3C4BEFCF.F4BD27BC@atc.unican.es обсуждение исходный текст |
Список | pgsql-hackers |
we have the following plan: Aggregate (cost=40018.00..40048.08 rows=401 width=41) (actual time=44807.96..44931.81 rows=2 loops=1) -> Group (cost=40018.00..40028.02 rows=4011 width=41) (actual time=44677.19..44803.74 rows=3131 loops=1) -> Sort (cost=40018.00..40018.00 rows=4011 width=41) (actual time=44677.13..44700.16 rows=3131 loops=1) -> Merge Join (cost=0.00..39777.96 rows=4011 width=41) (actual time=2.80..44460.70 rows=3131 loops=1) -> Index Scan using orders_pkey on orders (cost=0.00..8916.15 rows=150000 width=23) (actual time=0.22..8180.93 rows=149966 loops=1) -> Index Scan using lineitem_pkey on lineitem (cost=0.00..30426.65 rows=4011 width=18) (actual time=1.87..33570.50 rows=3131 loops=1) Total runtime: 44936.77 msec if we set enable_mergejoin=false, plan is this: Aggregate (cost=42802.29..42832.37 rows=401 width=41) (actual time=34705.28..34828.93 rows=2 loops=1) -> Group (cost=42802.29..42812.32 rows=4011 width=41) (actual time=34574.73..34700.84 rows=3131 loops=1) -> Sort (cost=42802.29..42802.29 rows=4011 width=41) (actual time=34574.66..34597.68 rows=3131 loops=1) -> Nested Loop (cost=0.00..42562.25 rows=4011 width=41) (actual time=2.25..34359.35 rows=3131 loops=1) -> Index Scan using lineitem_pkey on lineitem (cost=0.00..30426.65 rows=4011 width=18) (actual time=1.93..33448.35 rows=3131 loops=1) -> Index Scan using orders_pkey on orders (cost=0.00..3.01 rows=1 width=23) (actual time=0.19..0.22 rows=1 loops=3131) Total runtime: 34833.54 msec which is better the question is: Is there any way to force planner to use nested loops instead of setting variable? Thanks and regards
Вложения
В списке pgsql-hackers по дате отправления: