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

18 Dec 2007


      On Tue, Dec 18, 2007 at 12:09:13PM +0000, Konstantinos Krikellas wrote:
...
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
I did the same explain and found 7 idx's used. You only have 4, ie
I don't think you have all foreign keys defined.
See sql/src/benchmark/tpch/c.sql-dec-primary-foreign
(also included);
Niels
START TRANSACTION;
CREATE TABLE region (r_regionkey INT NOT NULL, r_name VARCHAR(25) NOT NULL, r_comment VARCHAR(152) NOT NULL, PRIMARY KEY (r_regionkey)) ;
CREATE TABLE nation (n_nationkey INT NOT NULL, n_name VARCHAR(25) NOT NULL, n_regionkey INT NOT NULL, n_comment VARCHAR(152) NOT NULL, PRIMARY KEY (n_nationkey), FOREIGN KEY (n_regionkey) REFERENCES region (r_regionkey)) ;
CREATE TABLE supplier (s_suppkey INT NOT NULL, s_name VARCHAR(25) NOT NULL, s_address VARCHAR(40) NOT NULL, s_nationkey INT NOT NULL, s_phone VARCHAR(15) NOT NULL, s_acctbal DECIMAL(15,2) NOT NULL, s_comment VARCHAR(101) NOT NULL, PRIMARY KEY (s_suppkey), FOREIGN KEY (s_nationkey) REFERENCES nation (n_nationkey)) ;
CREATE TABLE customer (c_custkey INT NOT NULL, c_name VARCHAR(25) NOT NULL, c_address VARCHAR(40) NOT NULL, c_nationkey INT NOT NULL, c_phone VARCHAR(15) NOT NULL, c_acctbal DECIMAL(15,2) NOT NULL, c_mktsegment VARCHAR(10) NOT NULL, c_comment VARCHAR(117) NOT NULL, PRIMARY KEY (c_custkey), FOREIGN KEY (c_nationkey) REFERENCES nation (n_nationkey)) ;
CREATE TABLE part (p_partkey INT NOT NULL, p_name VARCHAR(55) NOT NULL, p_mfgr VARCHAR(25) NOT NULL, p_brand VARCHAR(10) NOT NULL, p_type VARCHAR(25) NOT NULL, p_size INT NOT NULL, p_container VARCHAR(10) NOT NULL, p_retailprice DECIMAL(15,2) NOT NULL, p_comment VARCHAR(23) NOT NULL, PRIMARY KEY (p_partkey)) ;
CREATE TABLE partsupp (ps_partkey INT NOT NULL, ps_suppkey INT NOT NULL, ps_availqty INT NOT NULL, ps_supplycost DECIMAL(15,2) NOT NULL, ps_comment VARCHAR(199) NOT NULL, PRIMARY KEY (ps_partkey, ps_suppkey), FOREIGN KEY (ps_partkey) REFERENCES part (p_partkey), FOREIGN KEY (ps_suppkey) REFERENCES supplier (s_suppkey)) ;
CREATE TABLE orders (o_orderkey INT NOT NULL, o_custkey INT NOT NULL, o_orderstatus VARCHAR(1) NOT NULL, o_totalprice DECIMAL(15,2) NOT NULL, o_orderdate DATE NOT NULL, o_orderpriority VARCHAR(15) NOT NULL, o_clerk VARCHAR(15) NOT NULL, o_shippriority INT NOT NULL, o_comment VARCHAR(79) NOT NULL, PRIMARY KEY (o_orderkey), FOREIGN KEY (o_custkey) REFERENCES customer (c_custkey)) ;
CREATE TABLE lineitem (l_orderkey INT NOT NULL, l_partkey INT NOT NULL, l_suppkey INT NOT NULL, l_linenumber INT NOT NULL, l_quantity INTEGER NOT NULL, l_extendedprice DECIMAL(15,2) NOT NULL, l_discount DECIMAL(15,2) NOT NULL, l_tax DECIMAL(15,2) NOT NULL, l_returnflag VARCHAR(1) NOT NULL, l_linestatus VARCHAR(1) NOT NULL, l_shipdate DATE NOT NULL, l_commitdate DATE NOT NULL, l_receiptdate DATE NOT NULL, l_shipinstruct VARCHAR(25) NOT NULL, l_shipmode VARCHAR(10) NOT NULL, l_comment VARCHAR(44) NOT NULL, PRIMARY KEY (l_orderkey,l_linenumber), FOREIGN KEY (l_orderkey) REFERENCES orders (o_orderkey), FOREIGN KEY (l_partkey) REFERENCES part (p_partkey), FOREIGN KEY (l_suppkey) REFERENCES supplier (s_suppkey), FOREIGN KEY (l_partkey,l_suppkey) REFERENCES partsupp (ps_partkey,ps_suppkey)) ;
COMMIT;
...
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.
...
function user.s0_1():void;
    _1:bat[:oid,:int]{rows=6001215:lng,bid=5468}  := sql.bind("sys","lineitem","l_orderkey",0);
    _6:bat[:oid,:int]{rows=0:lng,bid=9790}  := sql.bind("sys","lineitem","l_orderkey",1);
    constraints.emptySet(_6);
    _6:bat[:oid,:int]{rows=0:lng,bid=9790}  := nil;
    _8:bat[:oid,:int]{rows=0:lng,bid=9791}  := sql.bind("sys","lineitem","l_orderkey",2);
    constraints.emptySet(_8);
    _8:bat[:oid,:int]{rows=0:lng,bid=9791}  := nil;
    _10:bat[:oid,:int]{rows=1500000:lng,bid=5374}  := sql.bind("sys","orders","o_orderkey",0);
    _13:bat[:oid,:int]{rows=0:lng,bid=9765}  := sql.bind("sys","orders","o_orderkey",1);
    constraints.emptySet(_13);
    _13:bat[:oid,:int]{rows=0:lng,bid=9765}  := nil;
    _14:bat[:oid,:int]{rows=0:lng,bid=9766}  := sql.bind("sys","orders","o_orderkey",2);
    constraints.emptySet(_14);
    _14:bat[:oid,:int]{rows=0:lng,bid=9766}  := nil;
    _15:bat[:oid,:date]{notnil=true,rows=1500000:lng,bid=5371}  := sql.bind("sys","orders","o_orderdate",0);
    _25:bat[:oid,:oid]{rows=1500000:lng,bid=5485}  := sql.bind_idxbat("sys","orders","orders_customer_fk",0);
    _38:bat[:oid,:int]{notnil=true,rows=150000:lng,bid=5205}  := sql.bind("sys","customer","c_nationkey",0);
    _41:bat[:oid,:int]{notnil=true,rows=0:lng,bid=9748}  := sql.bind("sys","customer","c_nationkey",1);
    constraints.emptySet(_41);
    _41:bat[:oid,:int]{notnil=true,rows=0:lng,bid=9748}  := nil;
    _42:bat[:oid,:int]{notnil=true,rows=0:lng,bid=9749}  := sql.bind("sys","customer","c_nationkey",2);
    constraints.emptySet(_42);
    _42:bat[:oid,:int]{notnil=true,rows=0:lng,bid=9749}  := nil;
    _43:bat[:oid,:int]{notnil=true,rows=10000:lng,bid=5572}  := sql.bind("sys","supplier","s_nationkey",0);
    _46:bat[:oid,:int]{notnil=true,rows=0:lng,bid=9729}  := sql.bind("sys","supplier","s_nationkey",1);
    constraints.emptySet(_46);
    _46:bat[:oid,:int]{notnil=true,rows=0:lng,bid=9729}  := nil;
    _47:bat[:oid,:int]{notnil=true,rows=0:lng,bid=9730}  := sql.bind("sys","supplier","s_nationkey",2);
    constraints.emptySet(_47);
    _47:bat[:oid,:int]{notnil=true,rows=0:lng,bid=9730}  := nil;
    _54:bat[:oid,:oid]{rows=10000:lng,bid=5022}  := sql.bind_idxbat("sys","supplier","supplier_nation_fk",0);
    _64:bat[:oid,:oid]{rows=6001215:lng,bid=5566}  := sql.bind_idxbat("sys","lineitem","lineitem_supplier_fk",0);
    _87:bat[:oid,:oid]{rows=25:lng,bid=5150}  := sql.bind_idxbat("sys","nation","nation_region_fk",0);
    _92:bat[:oid,:str]{notnil=true,rows=5:lng,bid=5334}  := sql.bind("sys","region","r_name",0);
    _97:bat[:oid,:str]{notnil=true,rows=0:lng,bid=9678}  := sql.bind("sys","region","r_name",1);
    constraints.emptySet(_97);
    _97:bat[:oid,:str]{notnil=true,rows=0:lng,bid=9678}  := nil;
    _98:bat[:oid,:str]{notnil=true,rows=0:lng,bid=9679}  := sql.bind("sys","region","r_name",2);
    constraints.emptySet(_98);
    _98:bat[:oid,:str]{notnil=true,rows=0:lng,bid=9679}  := nil;
    _104:bat[:oid,:lng]{notnil=true,rows=6001215:lng,bid=5609}  := sql.bind("sys","lineitem","l_extendedprice",0);
    _108:bat[:oid,:lng]{notnil=true,rows=6001215:lng,bid=5461}  := sql.bind("sys","lineitem","l_discount",0);
    _113:bat[:oid,:str]{notnil=true,rows=25:lng,bid=5332}  := sql.bind("sys","nation","n_name",0);
    _115:bat[:oid,:str]{notnil=true,rows=0:lng,bid=9665}  := sql.bind("sys","nation","n_name",1);
    constraints.emptySet(_115);
    _115:bat[:oid,:str]{notnil=true,rows=0:lng,bid=9665}  := nil;
    _116:bat[:oid,:str]{notnil=true,rows=0:lng,bid=9666}  := sql.bind("sys","nation","n_name",2);
    constraints.emptySet(_116);
    _116:bat[:oid,:str]{notnil=true,rows=0:lng,bid=9666}  := nil;
    _19 := mtime.addmonths(1994-01-01,12);
    _20{rows=750001:lng} := algebra.uselect(_15,1994-01-01,_19,true,false);
    _15:bat[:oid,:date]{notnil=true,rows=1500000:lng,bid=5371}  := nil;
    _23{rows=750001:lng} := algebra.semijoin(_10,_20);
    _10:bat[:oid,:int]{rows=1500000:lng,bid=5374}  := nil;
    _24{rows=750001:lng} := bat.reverse(_23);
    _23{rows=750001:lng} := nil;
    _27{rows=750001:lng} := algebra.semijoin(_25,_20);
    _25:bat[:oid,:oid]{rows=1500000:lng,bid=5485}  := nil;
    _20{rows=750001:lng} := nil;
    _29{rows=750001:lng} := algebra.markT(_27,0@0);
    _30{rows=750001:lng} := algebra.joinPath(_1,_24,_29);
    _1:bat[:oid,:int]{rows=6001215:lng,bid=5468}  := nil;
    _24{rows=750001:lng} := nil;
    _29{rows=750001:lng} := nil;
    _31{rows=750001:lng} := bat.reverse(_30);
    _33{rows=750001:lng} := algebra.markT(_31,1@0);
    _31{rows=750001:lng} := nil;
    _34{rows=750001:lng} := bat.reverse(_33);
    _33{rows=750001:lng} := nil;
    _35{rows=750001:lng} := bat.reverse(_27);
    _27{rows=750001:lng} := nil;
    _36{rows=750001:lng} := algebra.markT(_35,0@0);
    _35{rows=750001:lng} := nil;
    _37{rows=750001:lng} := bat.reverse(_36);
    _36{rows=750001:lng} := nil;
    _48{rows=10000:lng} := bat.reverse(_43);
    _43:bat[:oid,:int]{notnil=true,rows=10000:lng,bid=5572}  := nil;
    _49{rows=10000:lng} := algebra.joinPath(_34,_37,_38,_48);
    _34{rows=750001:lng} := nil;
    _37{rows=750001:lng} := nil;
    _38:bat[:oid,:int]{notnil=true,rows=150000:lng,bid=5205}  := nil;
    _48{rows=10000:lng} := nil;
    _50{rows=10000:lng} := bat.reverse(_49);
    _52{rows=10000:lng} := algebra.markT(_50,2@0);
    _50{rows=10000:lng} := nil;
    _53{rows=10000:lng} := bat.reverse(_52);
    _52{rows=10000:lng} := nil;
    _56{rows=10000:lng} := algebra.join(_53,_54);
    _54:bat[:oid,:oid]{rows=10000:lng,bid=5022}  := nil;
    _58{rows=10000:lng} := algebra.markT(_56,3@0);
    _59{rows=10000:lng} := bat.reverse(_58);
    _58{rows=10000:lng} := nil;
    _60{rows=10000:lng} := algebra.markT(_49,2@0);
    _49{rows=10000:lng} := nil;
    _61{rows=10000:lng} := bat.reverse(_60);
    _60{rows=10000:lng} := nil;
    _62{rows=750001:lng} := algebra.markT(_30,1@0);
    _30{rows=750001:lng} := nil;
    _63{rows=750001:lng} := bat.reverse(_62);
    _62{rows=750001:lng} := nil;
    _66{rows=10000:lng} := algebra.joinPath(_59,_61,_63,_64);
    _67{rows=10000:lng} := algebra.join(_59,_53);
    _68{rows=10000:lng} := bat.mirror(_66);
    _66{rows=10000:lng} := nil;
    _69{rows=10000:lng} := bat.mirror(_67);
    _67{rows=10000:lng} := nil;
    _70{rows=10000:lng} := algebra.join(_68,_69);
    _68{rows=10000:lng} := nil;
    _69{rows=10000:lng} := nil;
    _71{rows=10000:lng} := algebra.markH(_70,0@0);
    _72{rows=10000:lng} := algebra.markT(_70,0@0);
    _70{rows=10000:lng} := nil;
    _73{rows=10000:lng} := bat.reverse(_72);
    _72{rows=10000:lng} := nil;
    _74{rows=10000:lng} := algebra.joinPath(_73,_59,_61,_63,_64);
    _73{rows=10000:lng} := nil;
    _64:bat[:oid,:oid]{rows=6001215:lng,bid=5566}  := nil;
    _75{rows=10000:lng} := algebra.joinPath(_71,_59,_53);
    _53{rows=10000:lng} := nil;
    _76:bat[:oid,:bit]{rows=10000:lng}  := batcalc.==(_74,_75);
    _74{rows=10000:lng} := nil;
    _75{rows=10000:lng} := nil;
    _77{rows=5001:lng} := algebra.uselect(_76,true);
    _76:bat[:oid,:bit]{rows=10000:lng}  := nil;
    _78{rows=5001:lng} := bat.reverse(_77);
    _77{rows=5001:lng} := nil;
    _79{rows=5001:lng} := algebra.join(_78,_71);
    _78{rows=5001:lng} := nil;
    _71{rows=10000:lng} := nil;
    _80{rows=5001:lng} := bat.reverse(_79);
    _79{rows=5001:lng} := nil;
    _82{rows=5001:lng} := algebra.markT(_80,4@0);
    _80{rows=5001:lng} := nil;
    _83{rows=5001:lng} := bat.reverse(_82);
    _82{rows=5001:lng} := nil;
    _84{rows=10000:lng} := bat.reverse(_56);
    _56{rows=10000:lng} := nil;
    _85{rows=10000:lng} := algebra.markT(_84,3@0);
    _84{rows=10000:lng} := nil;
    _86{rows=10000:lng} := bat.reverse(_85);
    _85{rows=10000:lng} := nil;
    _90{rows=25:lng} := algebra.joinPath(_83,_86,_87);
    _87:bat[:oid,:oid]{rows=25:lng,bid=5150}  := nil;
    _91{rows=25:lng} := bat.reverse(_90);
    _90{rows=25:lng} := nil;
    _96{rows=5:lng} := algebra.uselect(_92,"ASIA");
    _92:bat[:oid,:str]{notnil=true,rows=5:lng,bid=5334}  := nil;
    _99{rows=5:lng} := algebra.semijoin(_91,_96);
    _91{rows=25:lng} := nil;
    _96{rows=5:lng} := nil;
    _100{rows=5:lng} := bat.reverse(_99);
    _99{rows=5:lng} := nil;
    _102{rows=5:lng} := algebra.markT(_100,5@0);
    _100{rows=5:lng} := nil;
    _103{rows=5:lng} := bat.reverse(_102);
    _102{rows=5:lng} := nil;
    _106{rows=5:lng} := algebra.joinPath(_103,_83,_59,_61,_63,_104);
    _104:bat[:oid,:lng]{notnil=true,rows=6001215:lng,bid=5609}  := nil;
    _110{rows=5:lng} := algebra.joinPath(_103,_83,_59,_61,_63,_108);
    _59{rows=10000:lng} := nil;
    _61{rows=10000:lng} := nil;
    _63{rows=750001:lng} := nil;
    _108:bat[:oid,:lng]{notnil=true,rows=6001215:lng,bid=5461}  := nil;
    _152 := algebra.reuse(_110);
    _111:bat[:oid,:lng]{rows=5:lng}  := batcalc.-(_152,100,_110);
    _152 := nil;
    _110{rows=5:lng} := nil;
    _111:bat[:oid,:lng]{rows=5:lng}  := batcalc.*(_111,_106,_111);
    _106{rows=5:lng} := nil;
    _112 := algebra.selectNotNil(_111);
    _111:bat[:oid,:lng]{rows=5:lng}  := nil;
    _117{rows=5:lng} := algebra.joinPath(_103,_83,_86,_113);
    (ext274,grp272):= group.new(_117);
    _117{rows=5:lng} := nil;
    _120 := bat.mirror(ext274);
    ext274 := nil;
    _121:bat[:oid,:lng]  := aggr.sum(_112,grp272,_120);
    _112 := nil;
    grp272 := nil;
    _122 := algebra.sortTail(_121);
    _123 := algebra.sortReverseTail(_122);
    _122 := nil;
    _124 := algebra.joinPath(_120,_103,_83,_86,_113);
    _120 := nil;
    _103{rows=5:lng} := nil;
    _83{rows=5001:lng} := nil;
    _86{rows=10000:lng} := nil;
    _113:bat[:oid,:str]{notnil=true,rows=25:lng,bid=5332}  := nil;
    _125 := sql.resultSet(2,1,_123);
    _123 := nil;
    sql.rsColumn(_125,"sys.nation","n_name","char",25,0,_124);
    _124 := nil;
    sql.rsColumn(_125,"sys.","revenue","decimal",19,4,_121);
    _121:bat[:oid,:lng]  := nil;
    sql.exportResult(_125,"");
end s0_1;
...

SF.Net email is sponsored by:
Check out the new SourceForge.net Marketplace.
It's the best place to buy or sell services
for just about anything Open Source.
http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace
_______________________________________________
MonetDB-users mailing list
MonetDB-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- 

Niels Nes, Centre for Mathematics and Computer Science (CWI)
Kruislaan 413, 1098 SJ Amsterdam, The Netherlands
room C0.02,  phone ++31 20 592-4098, fax ++31 20 592-4312
url: http://www.cwi.nl/~niels   e-mail: Niels.Nes@cwi.nl