enable_nestloop

From: Luis Amigo <lamigo(at)atc(dot)unican(dot)es>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: enable_nestloop
Date: 2001-11-23 19:36:21
Message-ID: 3BFEA535.D0245255@bonito.atc.unican.es
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Doug McNaught 2001-11-23 19:55:15 Re: explain analyze
Previous Message Tim Clinton 2001-11-23 19:26:23 PostgreSQL on Mac OS X --with-perl