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

Niels Nes Niels.Nes at cwi.nl
Tue Dec 18 13:23:37 CET 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 at 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 at 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 at 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 at 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 at 0);
>     _59{rows=10000:lng} := bat.reverse(_58);
>     _58{rows=10000:lng} := nil;
>     _60{rows=10000:lng} := algebra.markT(_49,2 at 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 at 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 at 0);
>     _72{rows=10000:lng} := algebra.markT(_70,0 at 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 at 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 at 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 at 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 at 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 at cwi.nl




More information about the users-list mailing list