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 |
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 |