[MonetDB-users] Problem with running TPC-H query No.5

Konstantinos Krikellas K.Krikellas at sms.ed.ac.uk
Tue Dec 18 13:09:13 CET 2007


> For this query to be quick you need all keys, ie primary and foreign.
> Does you tpch-h schema include those?
> 
> Niels


Yes, I have correctly set up the database schema. The population
parameter was set to 1 during table initialization. The strange thing is
that the other tested queries are executed as expected. i suspect that
this is an optimization problem, as if we look at the query:

select
         n_name,
         sum(l_extendedprice * (1 - l_discount)) as revenue
from
         customer,
         orders,
         lineitem,
         supplier,
         nation,
         region
where
         c_custkey = o_custkey
         and l_orderkey = o_orderkey
         and l_suppkey = s_suppkey
         and c_nationkey = s_nationkey
         and s_nationkey = n_nationkey
         and n_regionkey = r_regionkey
         and r_name = 'ASIA'
         and o_orderdate >= date '1994-01-01'
         and o_orderdate < date '1994-01-01' + interval '1' year
group by
         n_name
order by
         revenue desc;

, there is a chain among the joined tables, as supplier joins with
nation and lineitem, while lineitem joins with customer and customer
joins with supplier again. So, if the firstly executed join is customer
with supplier on nationkey, the result will be much bigger than the main
memory capacity. Still, the selection predicates on tables orders make
joining first orders with lineitem and then the result with customer a
more reasonable choice, with much smaller needs in memory. 
I have also attached the EXPLAIN output. Could that be the case?

Thanks.


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.monetdb.org/pipermail/users-list/attachments/20071218/833abaa8/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: explain
Type: text/x-matlab
Size: 9913 bytes
Desc: not available
URL: <http://www.monetdb.org/pipermail/users-list/attachments/20071218/833abaa8/attachment.bin>


More information about the users-list mailing list