Thread: enable_nestloop
we're trying to optimize running time on a query on postgresql 7.1.3
if we explain the result is:
Limit (cost=1882.42..1882.42 rows=100 width=120)
-> Sort (cost=1882.42..1882.42 rows=100 width=120)
-> Nested Loop (cost=0.00..1879.10 rows=100 width=120)
-> Nested Loop (cost=0.00..1653.10 rows=10 width=120)
-> Nested Loop (cost=0.00..1644.84 rows=1
width=104)
-> Nested Loop (cost=0.00..1636.58 rows=1
width=36)
-> Seq Scan on part (cost=0.00..25.00
rows=1 width=16)
-> Index Scan using partsupp_pkey on
partsupp (cost=0.00..8.14 rows=10 width=20)
SubPlan
-> Aggregate (cost=160.33..160.33
rows=1 width=36)
-> Merge Join
(cost=157.58..160.08 rows=100 width=36)
-> Sort
(cost=85.97..85.97 rows=100 width=12)
-> Hash Join
(cost=22.52..82.65 rows=100 width=12)
-> Seq Scan
on nation (cost=0.00..20.00 rows=1000 width=8)
-> Hash
(cost=22.50..22.50 rows=10 width=4)
-> Seq
Scan on region (cost=0.00..22.50 rows=10 width=4)
-> Sort
(cost=71.61..71.61 rows=100 width=24)
-> Hash Join
(cost=8.16..68.29 rows=100 width=24)
-> Seq Scan
on supplier (cost=0.00..20.00 rows=1000 width=8)
-> Hash
(cost=8.14..8.14 rows=10 width=16)
->
Index Scan using partsupp_pkey on partsupp (cost=0.00..8.14 rows=10
width=16)
-> Index Scan using supplier_pkey on
supplier (cost=0.00..8.14 rows=10 width=68)
-> Index Scan using nation_pkey on nation
(cost=0.00..8.14 rows=10 width=16)
-> Seq Scan on region (cost=0.00..22.50 rows=10 width=0)
if we use set enable_nestloop=off the result is:
Limit (cost=100160926.76..100160926.76 rows=100 width=120)
-> Sort (cost=100160926.76..100160926.76 rows=100 width=120)
-> Nested Loop (cost=100160637.42..100160923.43 rows=100
width=120)
-> Hash Join (cost=160637.42..160697.43 rows=10
width=120)
-> Seq Scan on nation (cost=0.00..20.00 rows=1000
width=16)
-> Hash (cost=160637.42..160637.42 rows=1
width=104)
-> Hash Join (cost=160577.41..160637.42
rows=1 width=104)
-> Seq Scan on supplier
(cost=0.00..20.00 rows=1000 width=68)
-> Hash (cost=160577.40..160577.40
rows=1 width=36)
-> Hash Join
(cost=25.00..160577.40 rows=1 width=36)
-> Seq Scan on partsupp
(cost=0.00..20.00 rows=1000 width=20)
-> Hash (cost=25.00..25.00
rows=1 width=16)
-> Seq Scan on part
(cost=0.00..25.00 rows=1 width=16)
SubPlan
-> Aggregate
(cost=160.33..160.33 rows=1 width=36)
-> Merge Join
(cost=157.58..160.08 rows=100 width=36)
-> Sort
(cost=85.97..85.97 rows=100 width=12)
-> Hash
Join (cost=22.52..82.65 rows=100 width=12)
-> Seq Scan on nation (cost=0.00..20.00 rows=1000 width=8)
-> Hash (cost=22.50..22.50 rows=10 width=4)
-> Seq Scan on region (cost=0.00..22.50 rows=10 width=4)
-> Sort
(cost=71.61..71.61 rows=100 width=24)
-> Hash
Join (cost=8.16..68.29 rows=100 width=24)
-> Seq Scan on supplier (cost=0.00..20.00 rows=1000 width=8)
-> Hash (cost=8.14..8.14 rows=10 width=16)
-> Index Scan using partsupp_pkey on partsupp (cost=0.00..8.14 rows=10
width=16)
-> Seq Scan on region (cost=0.00..22.50 rows=10 width=0)
if we try to exec them result times are:
0.004u 0.005s 0:03.05 in first case
0.004u 0.005s 0:03.07 in second case
is it a known bug?? or are we doing something wrong
thanks in advance
Luis Amigo <lamigo@atc.unican.es> writes:
> if we use set enable_nestloop=off the result is:
> Limit (cost=100160926.76..100160926.76 rows=100 width=120)
> -> Sort (cost=100160926.76..100160926.76 rows=100 width=120)
> -> Nested Loop (cost=100160637.42..100160923.43 rows=100
> width=120)
It's choosing a nestloop at the top level despite the large penalty
added to the cost, presumably because it cannot find any other way to
do the top-level join. You won't get any more useful answer than that
as long as you're not showing us the query and table schemas involved...
regards, tom lane