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 по дате отправления:

Предыдущее
От: Justin Clift
Дата:
Сообщение: Re: [GENERAL] PostgreSQL Licence: GNU/GPL
Следующее
От: Matthew Kirkwood
Дата:
Сообщение: Re: [GENERAL] PostgreSQL Licence: GNU/GPL