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
