Execution plans for tpc-h

Поиск
Список
Период
Сортировка
От Victor Muntes Mutero
Тема Execution plans for tpc-h
Дата
Msg-id 3AAE1A61.41C6@ac.upc.es
обсуждение исходный текст
Ответы Re: Execution plans for tpc-h
Список pgsql-general
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.

В списке pgsql-general по дате отправления:

Предыдущее
От: "Gregory Wood"
Дата:
Сообщение: Re: Create trigger problem :
Следующее
От: "Richard Huxton"
Дата:
Сообщение: Re: Re: Is this a bug in 7.1?