> > > 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'::bpc
h!
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::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 = '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::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 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_shipm
o!
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::double
precision) 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.24 rows=200062 width=36)
-> Seq Scan on part (cost=0.00..6757.62 rows=200062 width=36)
(6 rows)
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
