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
Mon, 21 Jan 2002 11:39:11 +0100
Luis Amigo <lamigo@atc.unican.es>
how to force planner to do nestloops instead of merge joins