We have Postgres 7.0.2 .
There is a query in TPC-H Benchmark that produces this execution plan:
Aggregate (cost=698221486855.00..698221486855.00 rows=1 width=72)
-> Nested Loop (cost=0.00..698221486855.00 rows=1 width=72)
-> Seq Scan on part (cost=0.00..6855.00 rows=200000 width=32)
-> Seq Scan on lineitem (cost=0.00..190439.15 rows=6001215
width=40)
The functional definition of this query (Q19) is :
select
sum(l_extendedprice* (1 - l_discount)) as revenue
from
lineitem,
part
where
(
p_partkey = l_partkey
and p_brand = 'Brand#12'
and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
and l_quantity >= 1 and l_quantity <= 1 + 10
and p_size between 1 and 5
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
)
or
(
p_partkey = l_partkey
and p_brand = 'Brand#23'
and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
and l_quantity >= 10 and l_quantity <= 10 + 10
and p_size between 1 and 10
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
)
or
(
p_partkey = l_partkey
and p_brand = 'Brand#34'
and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
and l_quantity >= 20 and l_quantity <= 20 + 10
and p_size between 1 and 15
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
);
There is an xjoin (p_partkey = l_partkey) so, why Postgres utilize
Nestloop??,
Would not be the HashJoin more useful??. I have tried to put the
variable ENABLE_NESTLOOP to OFF but it continues utilising NestLoop.
With this plan the
time execution of this query is eternal.
Can anybody explain me the reason the reason because Postgres utilize
NestLoop in this query?
Thanks in advance.
