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 по дате отправления: