how to force planner to do nestloops instead of merge joins

From: Luis Amigo <lamigo(at)atc(dot)unican(dot)es>
To: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: how to force planner to do nestloops instead of merge joins
Date: 2002-01-21 10:39:11
Message-ID: 3C4BEFCF.F4BD27BC@atc.unican.es
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Attachment Content-Type Size
lamigo.vcf text/x-vcard 290 bytes

Browse pgsql-hackers by date

  From Date Subject
Next Message Matthew Kirkwood 2002-01-21 10:39:46 Re: [GENERAL] PostgreSQL Licence: GNU/GPL
Previous Message Justin Clift 2002-01-21 10:39:05 Re: [HACKERS] PostgreSQL Licence: GNU/GPL