DBT-3 v1.5 Q19 (Re: Proposed Query Planner TODO items) - Mailing list pgsql-hackers
| From | Tatsuo Ishii |
|---|---|
| Subject | DBT-3 v1.5 Q19 (Re: Proposed Query Planner TODO items) |
| Date | |
| Msg-id | 20041104.184524.26271633.t-ishii@sra.co.jp Whole thread Raw |
| In response to | Re: Proposed Query Planner TODO items (Tatsuo Ishii <t-ishii@sra.co.jp>) |
| Responses |
Re: DBT-3 v1.5 Q19 (Re: Proposed Query Planner TODO items)
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| List | pgsql-hackers |
> > > Hi Tatsuo, > > > > > > I've made a new release: > > > http://prdownloads.sourceforge.net/osdldbt/dbt3-v1.5.tar.gz?download > > > > > > Let me know if there are any problems. > > > > Thanks! > > Just for quick note, it seems query 19 takes forever. Have you > successfully run Q19? Here is the more detailed info. The query was not finished within 3 days and was canceled on a Dual Xeon 2.8GHz with 2.5GB RAM running Linux. PostgreSQL is 7.4.5 with default postgresql.conf. An explain output is attatched. -- Tatsuo Ishii ! ! ! ! QUERY PLAN ! ! ! ! ! -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------! -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------! -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------! -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------! -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------! -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------! -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------! -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------! -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------! --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Aggregate (cost=680584790148.25..680584790148.26 rows=1 width=8) -> Nested Loop (cost=8223.62..680584790148.08 rows=68 width=8) Join Filter: ((("outer".l_shipmode = 'AIR'::bpchar) AND ("inner".p_container = 'SM CASE'::bpchar) AND ("inner".p_partkey= "outer".l_partkey) AND ("inner".p_brand = 'Brand#51'::bpchar) AND ("outer".l_quantity >= 2::double precision)AND ("outer".l_quantity <= 12::double precision) AND ("inner".p_size >= 1) AND ("inner".p_size <= 5) AND ("outer".l_shipinstruct= 'DELIVER IN PERSON'::bpchar)) OR (("outer".l_shipmode = 'AIR REG'::bpchar) AND ("inner".p_container= 'SM CASE'::bpchar) AND ("inner".p_partkey = "outer".l_partkey) AND ("inner".p_brand = 'Brand#51'::bpchar)AND ("outer".l_quantity >= 2::double precision) AND ("outer".l_quantity <= 12::double precision) AND ("inner".p_size>= 1) AND ("inner".p_size <= 5) AND ("outer".l_shipinstruct = 'DELIVER IN PERSON'::bpchar)) OR (("outer".l_shipmode= 'AIR'::bpchar) AND ("inner".p_container = 'SM BOX'::bpchar) AND ("inner".p_partkey = "outer".l_partkey)AND ("inner".p_brand = 'Brand#51'::bpchar) AND ("outer".l_quantity >= 2! ::double precision) AND ("outer".l_quantity <= 12::double precision) AND ("inner".p_size >= 1) AND ("inner".p_size <= 5)AND ("outer".l_shipinstruct = 'DELIVER IN PERSON'::bpchar)) OR (("outer".l_shipmode = 'AIR REG'::bpchar) AND ("inner".p_container= 'SM BOX'::bpchar) AND ("inner".p_partkey = "outer".l_partkey) AND ("inner".p_brand = 'Brand#51'::bpchar)AND ("outer".l_quantity >= 2::double precision) AND ("outer".l_quantity <= 12::double precision) AND ("inner".p_size>= 1) AND ("inner".p_size <= 5) AND ("outer".l_shipinstruct = 'DELIVER IN PERSON'::bpchar)) OR (("outer".l_shipmode= 'AIR'::bpchar) AND ("inner".p_container = 'SM PACK'::bpchar) AND ("inner".p_partkey = "outer".l_partkey)AND ("inner".p_brand = 'Brand#51'::bpchar) AND ("outer".l_quantity >= 2::double precision) AND ("outer".l_quantity<= 12::double precision) AND ("inner".p_size >= 1) AND ("inner".p_size <= 5) AND ("outer".l_shipinstruct= 'DELIVER IN PERSON'::bpchar)) OR (("outer".l_shipmode = 'AIR REG'::bpch! ar) AND ("inner".p_container = 'SM PACK'::bpchar) AND ("inner".p_partkey = "outer".l_partkey) AND ("inner".p_brand = 'Brand#51'::bpchar)AND ("outer".l_quantity >= 2::double precision) AND ("outer".l_quantity <= 12::double precision) AND ("inner".p_size>= 1) AND ("inner".p_size <= 5) AND ("outer".l_shipinstruct = 'DELIVER IN PERSON'::bpchar)) OR (("outer".l_shipmode= 'AIR'::bpchar) AND ("inner".p_container = 'SM PKG'::bpchar) AND ("inner".p_partkey = "outer".l_partkey)AND ("inner".p_brand = 'Brand#51'::bpchar) AND ("outer".l_quantity >= 2::double precision) AND ("outer".l_quantity<= 12::double precision) AND ("inner".p_size >= 1) AND ("inner".p_size <= 5) AND ("outer".l_shipinstruct= 'DELIVER IN PERSON'::bpchar)) OR (("outer".l_shipmode = 'AIR REG'::bpchar) AND ("inner".p_container= 'SM PKG'::bpchar) AND ("inner".p_partkey = "outer".l_partkey) AND ("inner".p_brand = 'Brand#51'::bpchar)AND ("outer".l_quantity >= 2::double precision) AND ("outer".l_quantity <= 12::double ! precision) AND ("inner".p_size >= 1) AND ("inner".p_size <= 5) AND ("outer".l_shipinstruct = 'DELIVER IN PERSON'::bpchar))OR (("outer".l_shipmode = 'AIR'::bpchar) AND ("inner".p_container = 'MED BAG'::bpchar) AND ("inner".p_partkey= "outer".l_partkey) AND ("inner".p_brand = 'Brand#42'::bpchar) AND ("outer".l_quantity >= 15::double precision)AND ("outer".l_quantity <= 25::double precision) AND ("inner".p_size >= 1) AND ("inner".p_size <= 10) AND ("outer".l_shipinstruct= 'DELIVER IN PERSON'::bpchar)) OR (("outer".l_shipmode = 'AIR REG'::bpchar) AND ("inner".p_container= 'MED BAG'::bpchar) AND ("inner".p_partkey = "outer".l_partkey) AND ("inner".p_brand = 'Brand#42'::bpchar)AND ("outer".l_quantity >= 15::double precision) AND ("outer".l_quantity <= 25::double precision) AND("inner".p_size >= 1) AND ("inner".p_size <= 10) AND ("outer".l_shipinstruct = 'DELIVER IN PERSON'::bpchar)) OR (("outer".l_shipmode= 'AIR'::bpchar) AND ("inner".p_container = 'MED BOX'::bpchar) AND ("i! nner".p_partkey = "outer".l_partkey) AND ("inner".p_brand = 'Brand#42'::bpchar) AND ("outer".l_quantity >= 15::double precision)AND ("outer".l_quantity <= 25::double precision) AND ("inner".p_size >= 1) AND ("inner".p_size <= 10) AND ("outer".l_shipinstruct= 'DELIVER IN PERSON'::bpchar)) OR (("outer".l_shipmode = 'AIR REG'::bpchar) AND ("inner".p_container= 'MED BOX'::bpchar) AND ("inner".p_partkey = "outer".l_partkey) AND ("inner".p_brand = 'Brand#42'::bpchar)AND ("outer".l_quantity >= 15::double precision) AND ("outer".l_quantity <= 25::double precision) AND("inner".p_size >= 1) AND ("inner".p_size <= 10) AND ("outer".l_shipinstruct = 'DELIVER IN PERSON'::bpchar)) OR (("outer".l_shipmode= 'AIR'::bpchar) AND ("inner".p_container = 'MED PKG'::bpchar) AND ("inner".p_partkey = "outer".l_partkey)AND ("inner".p_brand = 'Brand#42'::bpchar) AND ("outer".l_quantity >= 15::double precision) AND ("outer".l_quantity<= 25::double precision) AND ("inner".p_size >= 1) AND ("inner".! p_size <= 10) AND ("outer".l_shipinstruct = 'DELIVER IN PERSON'::bpchar)) OR (("outer".l_shipmode = 'AIR REG'::bpchar) AND("inner".p_container = 'MED PKG'::bpchar) AND ("inner".p_partkey = "outer".l_partkey) AND ("inner".p_brand = 'Brand#42'::bpchar)AND ("outer".l_quantity >= 15::double precision) AND ("outer".l_quantity <= 25::double precision) AND("inner".p_size >= 1) AND ("inner".p_size <= 10) AND ("outer".l_shipinstruct = 'DELIVER IN PERSON'::bpchar)) OR (("outer".l_shipmode= 'AIR'::bpchar) AND ("inner".p_container = 'MED PACK'::bpchar) AND ("inner".p_partkey = "outer".l_partkey)AND ("inner".p_brand = 'Brand#42'::bpchar) AND ("outer".l_quantity >= 15::double precision) AND ("outer".l_quantity<= 25::double precision) AND ("inner".p_size >= 1) AND ("inner".p_size <= 10) AND ("outer".l_shipinstruct= 'DELIVER IN PERSON'::bpchar)) OR (("outer".l_shipmode = 'AIR REG'::bpchar) AND ("inner".p_container= 'MED PACK'::bpchar) AND ("inner".p_partkey = "outer".l_partkey) AND ("! inner".p_brand = 'Brand#42'::bpchar) AND ("outer".l_quantity >= 15::double precision) AND ("outer".l_quantity <= 25::doubleprecision) AND ("inner".p_size >= 1) AND ("inner".p_size <= 10) AND ("outer".l_shipinstruct = 'DELIVER IN PERSON'::bpchar))OR (("outer".l_shipmode = 'AIR'::bpchar) AND ("inner".p_container = 'LG CASE'::bpchar) AND ("inner".p_partkey= "outer".l_partkey) AND ("inner".p_brand = 'Brand#53'::bpchar) AND ("outer".l_quantity >= 28::double precision)AND ("outer".l_quantity <= 38::double precision) AND ("inner".p_size >= 1) AND ("inner".p_size <= 15) AND ("outer".l_shipinstruct= 'DELIVER IN PERSON'::bpchar)) OR (("outer".l_shipmode = 'AIR REG'::bpchar) AND ("inner".p_container= 'LG CASE'::bpchar) AND ("inner".p_partkey = "outer".l_partkey) AND ("inner".p_brand = 'Brand#53'::bpchar)AND ("outer".l_quantity >= 28::double precision) AND ("outer".l_quantity <= 38::double precision) AND("inner".p_size >= 1) AND ("inner".p_size <= 15) AND ("outer".l_shipinstruct =!'DELIVER IN PERSON'::bpchar)) OR (("outer".l_shipmode= 'AIR'::bpchar) AND ("inner".p_container = 'LG BOX'::bpchar) AND ("inner".p_partkey = "outer".l_partkey)AND ("inner".p_brand = 'Brand#53'::bpchar) AND ("outer".l_quantity >= 28::double precision) AND ("outer".l_quantity<= 38::double precision) AND ("inner".p_size >= 1) AND ("inner".p_size <= 15) AND ("outer".l_shipinstruct= 'DELIVER IN PERSON'::bpchar)) OR (("outer".l_shipmode = 'AIR REG'::bpchar) AND ("inner".p_container= 'LG BOX'::bpchar) AND ("inner".p_partkey = "outer".l_partkey) AND ("inner".p_brand = 'Brand#53'::bpchar)AND ("outer".l_quantity >= 28::double precision) AND ("outer".l_quantity <= 38::double precision) AND("inner".p_size >= 1) AND ("inner".p_size <= 15) AND ("outer".l_shipinstruct = 'DELIVER IN PERSON'::bpchar)) OR (("outer".l_shipmode= 'AIR'::bpchar) AND ("inner".p_container = 'LG PACK'::bpchar) AND ("inner".p_partkey = "outer".l_partkey)AND ("inner".p_brand = 'Brand#53'::bpchar) AND ("outer".l! _quantity >= 28::double precision) AND ("outer".l_quantity <= 38::double precision) AND ("inner".p_size >= 1) AND ("inner".p_size<= 15) AND ("outer".l_shipinstruct = 'DELIVER IN PERSON'::bpchar)) OR (("outer".l_shipmode = 'AIR REG'::bpchar)AND ("inner".p_container = 'LG PACK'::bpchar) AND ("inner".p_partkey = "outer".l_partkey) AND ("inner".p_brand= 'Brand#53'::bpchar) AND ("outer".l_quantity >= 28::double precision) AND ("outer".l_quantity <= 38::doubleprecision) AND ("inner".p_size >= 1) AND ("inner".p_size <= 15) AND ("outer".l_shipinstruct = 'DELIVER IN PERSON'::bpchar))OR (("outer".l_shipmode = 'AIR'::bpchar) AND ("inner".p_container = 'LG PKG'::bpchar) AND ("inner".p_partkey= "outer".l_partkey) AND ("inner".p_brand = 'Brand#53'::bpchar) AND ("outer".l_quantity >= 28::double precision)AND ("outer".l_quantity <= 38::double precision) AND ("inner".p_size >= 1) AND ("inner".p_size <= 15) AND ("outer".l_shipinstruct= 'DELIVER IN PERSON'::bpchar)) OR (("outer".l_shipmo! de = 'AIR REG'::bpchar) AND ("inner".p_container = 'LG PKG'::bpchar) AND ("inner".p_partkey = "outer".l_partkey) AND ("inner".p_brand= 'Brand#53'::bpchar) AND ("outer".l_quantity >= 28::double precision) AND ("outer".l_quantity <= 38::doubleprecision) AND ("inner".p_size >= 1) AND ("inner".p_size <= 15) AND ("outer".l_shipinstruct = 'DELIVER IN PERSON'::bpchar))) -> Seq Scan on lineitem (cost=0.00..187587.02 rows=5996302 width=59) -> Materialize (cost=8223.62..11690.24rows=200062 width=36) -> Seq Scan on part (cost=0.00..6757.62 rows=200062 width=36) (6 rows)
pgsql-hackers by date: