Thread: enable_nestloop

enable_nestloop

From
Luis Amigo
Date:
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


Re: enable_nestloop

From
Tom Lane
Date:
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