Date: 2013-10-25 23:18:24 +0200
From: Klaudiusz <>
To: SQL devs <>
Version: 11.15.17 (Feb2013-SP5)
CC: @njnes

Last updated: 2014-02-20 15:03:11 +0100

Comment 19307

Date: 2013-10-25 23:18:24 +0200
From: Klaudiusz <>

User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/30.0.1599.101 Safari/537.36
Build Identifier:

if there is foreign key

SELECT dim_perf.name from "sys"."fact_perf" join dim_perf using (dim_key) group by dim_perf.name ;

is more than 10x slower than

SELECT dim_perf.name,count(id) from "sys"."fact_perf" join dim_perf using (dim_key) group by dim_perf.name ;

In the second query MonetDB uses all CPU cores in first only one.

Reproducible: Always

Steps to Reproduce:

-- 100 000 000 rows
CREATE TABLE "sys"."fact_perf" (
"id" BIGINT,
"value" DOUBLE,
"dim_key" BIGINT
);

-- 10 000 rows
CREATE TABLE "sys"."dim_perf" (
"dim_key" BIGINT,
"name" VARCHAR(15)
);

ALTER TABLE dim_perf ADD PRIMARY KEY (dim_key) ;
ALTER TABLE fact_perf ADD CONSTRAINT dim_key_fk FOREIGN KEY (dim_key) REFERENCES dim_perf (dim_key) MATCH FULL ;

-- This query is problematic with FK
-- During this query MonetDB uses only one CPU core
-- (1) ( Duration - 15.1s)
SELECT dim_perf.name from "sys"."fact_perf" join dim_perf using (dim_key) group by dim_perf.name ;

-- If I add count(id) to this query then performance is OK. Monet uses all CPU cores
-- (2) ( Duration 1.3s)
SELECT dim_perf.name,count(id) from "sys"."fact_perf" join dim_perf using (dim_key) group by dim_perf.name ;

-- If I drop FK
ALTER TABLE fact_perf DROP CONSTRAINT dim_key_fk ;

-- performance is much better than in (1) but not as good as in (2)
-- (3) ( Duration 2.6s)
SELECT dim_perf.name from "sys"."fact_perf" join dim_perf using (dim_key) group by dim_perf.name ;

MonetDB 5 server v11.15.18 (64-bit, 64-bit oids)
This is an unreleased version
Copyright (c) 1993-July 2008 CWI
Copyright (c) August 2008-2013 MonetDB B.V., all rights reserved
Visit http://www.monetdb.org/ for further information
Found 252.2GiB available memory, 32 available cpu cores
Libraries:
libpcre: 7.8 2008-09-05 (compiled with 7.8)
openssl: OpenSSL 1.0.0 29 Mar 2010 (compiled with OpenSSL 1.0.0-fips 29 Mar 2010)
libxml2: 2.7.6 (compiled with 2.7.6)
Compiled by: (x86_64-unknown-linux-gnu)
Compilation: gcc -O3 -fomit-frame-pointer -pipe -Wp,-D_FORTIFY_SOURCE=2
Linking : /usr/bin/ld -m elf_x86_64

Comment 19308

Date: 2013-10-25 23:19:33 +0200
From: Klaudiusz <>

-- (1) with FK

EXPLAIN SELECT dim_perf.name from "sys"."fact_perf" join dim_perf using (dim_key) group by dim_perf.name ;
+--------------------------------------------------------------------------+
| mal |
+==========================================================================+
| function user.s28_3{autoCommit=true}():void; |
| X_30 := nil:bat[:oid,:str]; |
| barrier X_52 := language.dataflow(); |
| X_2 := sql.mvc(); |
| X_3:bat[:oid,:oid] := sql.tid(X_2,"sys","fact_perf"); |
| X_6 := sql.bind_idxbat(X_2,"sys","fact_perf","dim_key_fk",0); |
| (X_9,r1_9) := sql.bind_idxbat(X_2,"sys","fact_perf","dim_key_fk",2); |
| X_12 := sql.bind_idxbat(X_2,"sys","fact_perf","dim_key_fk",1); |
| X_14 := sql.delta(X_6,X_9,r1_9,X_12); |
| X_15 := algebra.leftfetchjoin(X_3,X_14); |
| X_16:bat[:oid,:oid] := sql.tid(X_2,"sys","dim_perf"); |
| (X_18,r1_18) := algebra.join(X_15,X_16); |
| X_20 := sql.bind(X_2,"sys","dim_perf","name",0); |
| (X_22,r1_22) := sql.bind(X_2,"sys","dim_perf","name",2); |
| X_24 := sql.bind(X_2,"sys","dim_perf","name",1); |
| X_25 := sql.delta(X_20,X_22,r1_22,X_24); |
| X_26:bat[:oid,:str] := algebra.leftfetchjoinPath(r1_18,X_16,X_25); |
| (X_27,r1_28,r2_28) := group.subgroupdone(X_26); |
| X_30 := algebra.leftfetchjoin(r1_28,X_26); |
| language.pass(X_16); |
| language.pass(X_26); |
| exit X_52; |
| X_31 := sql.resultSet(1,1,X_30); |
| sql.rsColumn(X_31,"sys.dim_perf","name","varchar",15,0,X_30); |
| X_36 := io.stdout(); |
| sql.exportResult(X_36,X_31); |
| end s28_3; |
+--------------------------------------------------------------------------+

Comment 19309

Date: 2013-10-25 23:24:48 +0200
From: Klaudiusz <>

Created attachment 246
EXPLAIN (2) WITH FK

Attached file: explain_2_with_fk.txt (text/plain, 76401 bytes)
Description: EXPLAIN (2) WITH FK

Comment 19310

Date: 2013-10-25 23:34:40 +0200
From: Klaudiusz <>

EXPLAIN SELECT dim_perf.name from "sys"."fact_perf" join dim_perf using (dim_key) group by dim_perf.name ;

+---------------------------------------------------------------------------------------------------------+
| mal |
+=========================================================================================================+
| function user.s8_1{autoCommit=true}():void; |
| X_28 := nil:bat[:oid,:str]; |
| barrier X_765 := language.dataflow(); |
| X_2 := sql.mvc(); |
| X_54:bat[:oid,:oid] := sql.tid(X_2,"sys","fact_perf",0,32); |
| X_120:bat[:oid,:lng] := sql.bind(X_2,"sys","fact_perf","dim_key",0,0,32); |
| (X_186:bat[:oid,:oid] ,X_187:bat[:oid,:lng] ) := sql.bind(X_2,"sys","fact_perf","dim_key",2,0,32); |
| X_315 := sql.delta(X_120,X_186,X_187); |
| X_379 := algebra.leftfetchjoin(X_54,X_315); |
| X_10:bat[:oid,:oid] := sql.tid(X_2,"sys","dim_perf"); |
| X_12 := sql.bind(X_2,"sys","dim_perf","dim_key",0); |
| (X_13,r1_19) := sql.bind(X_2,"sys","dim_perf","dim_key",2); |
| X_15 := sql.bind(X_2,"sys","dim_perf","dim_key",1); |
| X_16 := sql.delta(X_12,X_13,r1_19,X_15); |
| X_17 := algebra.leftfetchjoin(X_10,X_16); |
| (X_443,X_444) := algebra.join(X_379,X_17); |
| X_18 := sql.bind(X_2,"sys","dim_perf","name",0); |
| (X_20,r1_28) := sql.bind(X_2,"sys","dim_perf","name",2); |
| X_22 := sql.bind(X_2,"sys","dim_perf","name",1); |
| X_23 := sql.delta(X_18,X_20,r1_28,X_22); |
| X_24 := algebra.leftfetchjoin(X_10,X_23); |
| X_539 := algebra.leftfetchjoin(X_444,X_24); |
| (X_603,X_604,X_605) := group.subgroupdone(X_539); |
| X_606 := algebra.leftfetchjoin(X_604,X_539); |
| X_56:bat[:oid,:oid] := sql.tid(X_2,"sys","fact_perf",1,32); |
| X_122:bat[:oid,:lng] := sql.bind(X_2,"sys","fact_perf","dim_key",0,1,32); |
| (X_189:bat[:oid,:oid] ,X_190:bat[:oid,:lng] ) := sql.bind(X_2,"sys","fact_perf","dim_key",2,1,32); |
| X_317 := sql.delta(X_122,X_189,X_190); |
| X_381 := algebra.leftfetchjoin(X_56,X_317); |
| (X_446,X_447) := algebra.join(X_381,X_17); |
| X_541 := algebra.leftfetchjoin(X_447,X_24); |
| (X_607,X_608,X_609) := group.subgroupdone(X_541); |
| X_610 := algebra.leftfetchjoin(X_608,X_541); |
| X_58:bat[:oid,:oid] := sql.tid(X_2,"sys","fact_perf",2,32); |
| X_124:bat[:oid,:lng] := sql.bind(X_2,"sys","fact_perf","dim_key",0,2,32); |
| (X_192:bat[:oid,:oid] ,X_193:bat[:oid,:lng] ) := sql.bind(X_2,"sys","fact_perf","dim_key",2,2,32); |
| X_319 := sql.delta(X_124,X_192,X_193); |
| X_383 := algebra.leftfetchjoin(X_58,X_319); |
| (X_449,X_450) := algebra.join(X_383,X_17); |
| X_543 := algebra.leftfetchjoin(X_450,X_24); |
| (X_611,X_612,X_613) := group.subgroupdone(X_543); |
| X_614 := algebra.leftfetchjoin(X_612,X_543); |
| X_60:bat[:oid,:oid] := sql.tid(X_2,"sys","fact_perf",3,32); |
| X_126:bat[:oid,:lng] := sql.bind(X_2,"sys","fact_perf","dim_key",0,3,32); |
| (X_195:bat[:oid,:oid] ,X_196:bat[:oid,:lng] ) := sql.bind(X_2,"sys","fact_perf","dim_key",2,3,32); |
| X_321 := sql.delta(X_126,X_195,X_196); |
| X_385 := algebra.leftfetchjoin(X_60,X_321); |
| (X_452,X_453) := algebra.join(X_385,X_17); |
| X_545 := algebra.leftfetchjoin(X_453,X_24); |
| (X_615,X_616,X_617) := group.subgroupdone(X_545); |
| X_618 := algebra.leftfetchjoin(X_616,X_545); |
| X_62:bat[:oid,:oid] := sql.tid(X_2,"sys","fact_perf",4,32); |
| X_128:bat[:oid,:lng] := sql.bind(X_2,"sys","fact_perf","dim_key",0,4,32); |
| (X_198:bat[:oid,:oid] ,X_199:bat[:oid,:lng] ) := sql.bind(X_2,"sys","fact_perf","dim_key",2,4,32); |
| X_323 := sql.delta(X_128,X_198,X_199); |
| X_387 := algebra.leftfetchjoin(X_62,X_323); |
| (X_455,X_456) := algebra.join(X_387,X_17); |
| X_547 := algebra.leftfetchjoin(X_456,X_24); |
| (X_619,X_620,X_621) := group.subgroupdone(X_547); |
| X_622 := algebra.leftfetchjoin(X_620,X_547); |
| X_64:bat[:oid,:oid] := sql.tid(X_2,"sys","fact_perf",5,32); |
| X_130:bat[:oid,:lng] := sql.bind(X_2,"sys","fact_perf","dim_key",0,5,32); |
| (X_201:bat[:oid,:oid] ,X_202:bat[:oid,:lng] ) := sql.bind(X_2,"sys","fact_perf","dim_key",2,5,32); |
| X_325 := sql.delta(X_130,X_201,X_202); |
| X_389 := algebra.leftfetchjoin(X_64,X_325); |
| (X_458,X_459) := algebra.join(X_389,X_17); |
| X_549 := algebra.leftfetchjoin(X_459,X_24); |
| (X_623,X_624,X_625) := group.subgroupdone(X_549); |
| X_626 := algebra.leftfetchjoin(X_624,X_549); |
| X_66:bat[:oid,:oid] := sql.tid(X_2,"sys","fact_perf",6,32); |
| X_132:bat[:oid,:lng] := sql.bind(X_2,"sys","fact_perf","dim_key",0,6,32); |
| (X_204:bat[:oid,:oid] ,X_205:bat[:oid,:lng] ) := sql.bind(X_2,"sys","fact_perf","dim_key",2,6,32); |
| X_327 := sql.delta(X_132,X_204,X_205); |
| X_391 := algebra.leftfetchjoin(X_66,X_327); |
| (X_461,X_462) := algebra.join(X_391,X_17); |
| X_551 := algebra.leftfetchjoin(X_462,X_24); |
| (X_627,X_628,X_629) := group.subgroupdone(X_551); |
| X_630 := algebra.leftfetchjoin(X_628,X_551); |
| X_68:bat[:oid,:oid] := sql.tid(X_2,"sys","fact_perf",7,32); |
| X_134:bat[:oid,:lng] := sql.bind(X_2,"sys","fact_perf","dim_key",0,7,32); |
| (X_207:bat[:oid,:oid] ,X_208:bat[:oid,:lng] ) := sql.bind(X_2,"sys","fact_perf","dim_key",2,7,32); |
| X_329 := sql.delta(X_134,X_207,X_208); |
| X_393 := algebra.leftfetchjoin(X_68,X_329); |
| (X_464,X_465) := algebra.join(X_393,X_17); |
| X_553 := algebra.leftfetchjoin(X_465,X_24); |
| (X_631,X_632,X_633) := group.subgroupdone(X_553); |
| X_634 := algebra.leftfetchjoin(X_632,X_553); |
| X_70:bat[:oid,:oid] := sql.tid(X_2,"sys","fact_perf",8,32); |
| X_136:bat[:oid,:lng] := sql.bind(X_2,"sys","fact_perf","dim_key",0,8,32); |
| (X_210:bat[:oid,:oid] ,X_211:bat[:oid,:lng] ) := sql.bind(X_2,"sys","fact_perf","dim_key",2,8,32); |
| X_331 := sql.delta(X_136,X_210,X_211); |
| X_395 := algebra.leftfetchjoin(X_70,X_331); |
| (X_467,X_468) := algebra.join(X_395,X_17); |
| X_555 := algebra.leftfetchjoin(X_468,X_24); |
| (X_635,X_636,X_637) := group.subgroupdone(X_555); |
| X_638 := algebra.leftfetchjoin(X_636,X_555); |
| X_72:bat[:oid,:oid] := sql.tid(X_2,"sys","fact_perf",9,32); |
| X_138:bat[:oid,:lng] := sql.bind(X_2,"sys","fact_perf","dim_key",0,9,32); |
| (X_213:bat[:oid,:oid] ,X_214:bat[:oid,:lng] ) := sql.bind(X_2,"sys","fact_perf","dim_key",2,9,32); |
| X_333 := sql.delta(X_138,X_213,X_214); |
| X_397 := algebra.leftfetchjoin(X_72,X_333); |
| (X_470,X_471) := algebra.join(X_397,X_17); |
| X_557 := algebra.leftfetchjoin(X_471,X_24); |
| (X_639,X_640,X_641) := group.subgroupdone(X_557); |
| X_642 := algebra.leftfetchjoin(X_640,X_557); |
| X_74:bat[:oid,:oid] := sql.tid(X_2,"sys","fact_perf",10,32); |
| X_140:bat[:oid,:lng] := sql.bind(X_2,"sys","fact_perf","dim_key",0,10,32); |
| (X_216:bat[:oid,:oid] ,X_217:bat[:oid,:lng] ) := sql.bind(X_2,"sys","fact_perf","dim_key",2,10,32); |
| X_335 := sql.delta(X_140,X_216,X_217); |
| X_399 := algebra.leftfetchjoin(X_74,X_335); |
| (X_473,X_474) := algebra.join(X_399,X_17); |
| X_559 := algebra.leftfetchjoin(X_474,X_24); |
| (X_643,X_644,X_645) := group.subgroupdone(X_559); |
| X_646 := algebra.leftfetchjoin(X_644,X_559); |
| X_76:bat[:oid,:oid] := sql.tid(X_2,"sys","fact_perf",11,32); |
| X_142:bat[:oid,:lng] := sql.bind(X_2,"sys","fact_perf","dim_key",0,11,32); |
| (X_220:bat[:oid,:oid] ,X_221:bat[:oid,:lng] ) := sql.bind(X_2,"sys","fact_perf","dim_key",2,11,32); |
| X_337 := sql.delta(X_142,X_220,X_221); |
| X_401 := algebra.leftfetchjoin(X_76,X_337); |
| (X_476,X_477) := algebra.join(X_401,X_17); |
| X_561 := algebra.leftfetchjoin(X_477,X_24); |
| (X_647,X_648,X_649) := group.subgroupdone(X_561); |
| X_650 := algebra.leftfetchjoin(X_648,X_561); |
| X_78:bat[:oid,:oid] := sql.tid(X_2,"sys","fact_perf",12,32); |
| X_144:bat[:oid,:lng] := sql.bind(X_2,"sys","fact_perf","dim_key",0,12,32); |
| (X_223:bat[:oid,:oid] ,X_224:bat[:oid,:lng] ) := sql.bind(X_2,"sys","fact_perf","dim_key",2,12,32); |
| X_339 := sql.delta(X_144,X_223,X_224); |
| X_403 := algebra.leftfetchjoin(X_78,X_339); |
| (X_479,X_480) := algebra.join(X_403,X_17); |
| X_563 := algebra.leftfetchjoin(X_480,X_24); |
| (X_651,X_652,X_653) := group.subgroupdone(X_563); |
| X_654 := algebra.leftfetchjoin(X_652,X_563); |
| X_80:bat[:oid,:oid] := sql.tid(X_2,"sys","fact_perf",13,32); |
| X_146:bat[:oid,:lng] := sql.bind(X_2,"sys","fact_perf","dim_key",0,13,32); |
| (X_226:bat[:oid,:oid] ,X_227:bat[:oid,:lng] ) := sql.bind(X_2,"sys","fact_perf","dim_key",2,13,32); |
| X_341 := sql.delta(X_146,X_226,X_227); |
| X_405 := algebra.leftfetchjoin(X_80,X_341); |
| (X_482,X_483) := algebra.join(X_405,X_17); |
| X_565 := algebra.leftfetchjoin(X_483,X_24); |
| (X_655,X_656,X_657) := group.subgroupdone(X_565); |
| X_658 := algebra.leftfetchjoin(X_656,X_565); |
| X_82:bat[:oid,:oid] := sql.tid(X_2,"sys","fact_perf",14,32); |
| X_148:bat[:oid,:lng] := sql.bind(X_2,"sys","fact_perf","dim_key",0,14,32); |
| (X_229:bat[:oid,:oid] ,X_230:bat[:oid,:lng] ) := sql.bind(X_2,"sys","fact_perf","dim_key",2,14,32); |
| X_343 := sql.delta(X_148,X_229,X_230); |
| X_407 := algebra.leftfetchjoin(X_82,X_343); |
| (X_485,X_486) := algebra.join(X_407,X_17); |
| X_567 := algebra.leftfetchjoin(X_486,X_24); |
| (X_659,X_660,X_661) := group.subgroupdone(X_567); |
| X_662 := algebra.leftfetchjoin(X_660,X_567); |
| X_84:bat[:oid,:oid] := sql.tid(X_2,"sys","fact_perf",15,32); |
| X_150:bat[:oid,:lng] := sql.bind(X_2,"sys","fact_perf","dim_key",0,15,32); |
| (X_232:bat[:oid,:oid] ,X_233:bat[:oid,:lng] ) := sql.bind(X_2,"sys","fact_perf","dim_key",2,15,32); |
| X_345 := sql.delta(X_150,X_232,X_233); |
| X_409 := algebra.leftfetchjoin(X_84,X_345); |
| (X_488,X_489) := algebra.join(X_409,X_17); |
| X_569 := algebra.leftfetchjoin(X_489,X_24); |
| (X_663,X_664,X_665) := group.subgroupdone(X_569); |
| X_666 := algebra.leftfetchjoin(X_664,X_569); |
| X_87:bat[:oid,:oid] := sql.tid(X_2,"sys","fact_perf",16,32); |
| X_153:bat[:oid,:lng] := sql.bind(X_2,"sys","fact_perf","dim_key",0,16,32); |
| (X_235:bat[:oid,:oid] ,X_236:bat[:oid,:lng] ) := sql.bind(X_2,"sys","fact_perf","dim_key",2,16,32); |
| X_347 := sql.delta(X_153,X_235,X_236); |
| X_411 := algebra.leftfetchjoin(X_87,X_347); |
| (X_491,X_492) := algebra.join(X_411,X_17); |
| X_571 := algebra.leftfetchjoin(X_492,X_24); |
| (X_667,X_668,X_669) := group.subgroupdone(X_571); |
| X_670 := algebra.leftfetchjoin(X_668,X_571); |
| X_89:bat[:oid,:oid] := sql.tid(X_2,"sys","fact_perf",17,32); |
| X_155:bat[:oid,:lng] := sql.bind(X_2,"sys","fact_perf","dim_key",0,17,32); |
| (X_238:bat[:oid,:oid] ,X_239:bat[:oid,:lng] ) := sql.bind(X_2,"sys","fact_perf","dim_key",2,17,32); |
| X_349 := sql.delta(X_155,X_238,X_239); |
| X_413 := algebra.leftfetchjoin(X_89,X_349); |
| (X_494,X_495) := algebra.join(X_413,X_17); |
| X_573 := algebra.leftfetchjoin(X_495,X_24); |
| (X_671,X_672,X_673) := group.subgroupdone(X_573); |
| X_674 := algebra.leftfetchjoin(X_672,X_573); |
| X_91:bat[:oid,:oid] := sql.tid(X_2,"sys","fact_perf",18,32); |
| X_157:bat[:oid,:lng] := sql.bind(X_2,"sys","fact_perf","dim_key",0,18,32); |
| (X_241:bat[:oid,:oid] ,X_242:bat[:oid,:lng] ) := sql.bind(X_2,"sys","fact_perf","dim_key",2,18,32); |
| X_351 := sql.delta(X_157,X_241,X_242); |
| X_415 := algebra.leftfetchjoin(X_91,X_351); |
| (X_497,X_498) := algebra.join(X_415,X_17); |
| X_575 := algebra.leftfetchjoin(X_498,X_24); |
| (X_675,X_676,X_677) := group.subgroupdone(X_575); |
| X_678 := algebra.leftfetchjoin(X_676,X_575); |
| X_93:bat[:oid,:oid] := sql.tid(X_2,"sys","fact_perf",19,32); |
| X_159:bat[:oid,:lng] := sql.bind(X_2,"sys","fact_perf","dim_key",0,19,32); |
| (X_244:bat[:oid,:oid] ,X_245:bat[:oid,:lng] ) := sql.bind(X_2,"sys","fact_perf","dim_key",2,19,32); |
| X_353 := sql.delta(X_159,X_244,X_245); |
| X_417 := algebra.leftfetchjoin(X_93,X_353); |
| (X_500,X_501) := algebra.join(X_417,X_17); |
| X_577 := algebra.leftfetchjoin(X_501,X_24); |
| (X_679,X_680,X_681) := group.subgroupdone(X_577); |
| X_682 := algebra.leftfetchjoin(X_680,X_577); |
| X_95:bat[:oid,:oid] := sql.tid(X_2,"sys","fact_perf",20,32); |
| X_161:bat[:oid,:lng] := sql.bind(X_2,"sys","fact_perf","dim_key",0,20,32); |
| (X_247:bat[:oid,:oid] ,X_248:bat[:oid,:lng] ) := sql.bind(X_2,"sys","fact_perf","dim_key",2,20,32); |
| X_355 := sql.delta(X_161,X_247,X_248); |
| X_419 := algebra.leftfetchjoin(X_95,X_355); |
| (X_503,X_504) := algebra.join(X_419,X_17); |
| X_579 := algebra.leftfetchjoin(X_504,X_24); |
| (X_683,X_684,X_685) := group.subgroupdone(X_579); |
| X_686 := algebra.leftfetchjoin(X_684,X_579); |
| X_97:bat[:oid,:oid] := sql.tid(X_2,"sys","fact_perf",21,32); |
| X_163:bat[:oid,:lng] := sql.bind(X_2,"sys","fact_perf","dim_key",0,21,32); |
| (X_250:bat[:oid,:oid] ,X_251:bat[:oid,:lng] ) := sql.bind(X_2,"sys","fact_perf","dim_key",2,21,32); |
| X_357 := sql.delta(X_163,X_250,X_251); |
| X_421 := algebra.leftfetchjoin(X_97,X_357); |
| (X_506,X_507) := algebra.join(X_421,X_17); |
| X_581 := algebra.leftfetchjoin(X_507,X_24); |
| (X_687,X_688,X_689) := group.subgroupdone(X_581); |
| X_690 := algebra.leftfetchjoin(X_688,X_581); |
| X_99:bat[:oid,:oid] := sql.tid(X_2,"sys","fact_perf",22,32); |
| X_165:bat[:oid,:lng] := sql.bind(X_2,"sys","fact_perf","dim_key",0,22,32); |
| (X_254:bat[:oid,:oid] ,X_255:bat[:oid,:lng] ) := sql.bind(X_2,"sys","fact_perf","dim_key",2,22,32); |
| X_359 := sql.delta(X_165,X_254,X_255); |
| X_423 := algebra.leftfetchjoin(X_99,X_359); |
| (X_509,X_510) := algebra.join(X_423,X_17); |
| X_583 := algebra.leftfetchjoin(X_510,X_24); |
| (X_691,X_692,X_693) := group.subgroupdone(X_583); |
| X_694 := algebra.leftfetchjoin(X_692,X_583); |
| X_101:bat[:oid,:oid] := sql.tid(X_2,"sys","fact_perf",23,32); |
| X_167:bat[:oid,:lng] := sql.bind(X_2,"sys","fact_perf","dim_key",0,23,32); |
| (X_257:bat[:oid,:oid] ,X_258:bat[:oid,:lng] ) := sql.bind(X_2,"sys","fact_perf","dim_key",2,23,32); |
| X_361 := sql.delta(X_167,X_257,X_258); |
| X_425 := algebra.leftfetchjoin(X_101,X_361); |
| (X_512,X_513) := algebra.join(X_425,X_17); |
| X_585 := algebra.leftfetchjoin(X_513,X_24); |
| (X_695,X_696,X_697) := group.subgroupdone(X_585); |
| X_698 := algebra.leftfetchjoin(X_696,X_585); |
| X_103:bat[:oid,:oid] := sql.tid(X_2,"sys","fact_perf",24,32); |
| X_169:bat[:oid,:lng] := sql.bind(X_2,"sys","fact_perf","dim_key",0,24,32); |
| (X_260:bat[:oid,:oid] ,X_261:bat[:oid,:lng] ) := sql.bind(X_2,"sys","fact_perf","dim_key",2,24,32); |
| X_363 := sql.delta(X_169,X_260,X_261); |
| X_427 := algebra.leftfetchjoin(X_103,X_363); |
| (X_515,X_516) := algebra.join(X_427,X_17); |
| X_587 := algebra.leftfetchjoin(X_516,X_24); |
| (X_699,X_700,X_701) := group.subgroupdone(X_587); |
| X_702 := algebra.leftfetchjoin(X_700,X_587); |
| X_105:bat[:oid,:oid] := sql.tid(X_2,"sys","fact_perf",25,32); |
| X_171:bat[:oid,:lng] := sql.bind(X_2,"sys","fact_perf","dim_key",0,25,32); |
| (X_263:bat[:oid,:oid] ,X_264:bat[:oid,:lng] ) := sql.bind(X_2,"sys","fact_perf","dim_key",2,25,32); |
| X_365 := sql.delta(X_171,X_263,X_264); |
| X_429 := algebra.leftfetchjoin(X_105,X_365); |
| (X_518,X_519) := algebra.join(X_429,X_17); |
| X_589 := algebra.leftfetchjoin(X_519,X_24); |
| (X_703,X_704,X_705) := group.subgroupdone(X_589); |
| X_706 := algebra.leftfetchjoin(X_704,X_589); |
| X_107:bat[:oid,:oid] := sql.tid(X_2,"sys","fact_perf",26,32); |
| X_173:bat[:oid,:lng] := sql.bind(X_2,"sys","fact_perf","dim_key",0,26,32); |
| (X_266:bat[:oid,:oid] ,X_267:bat[:oid,:lng] ) := sql.bind(X_2,"sys","fact_perf","dim_key",2,26,32); |
| X_367 := sql.delta(X_173,X_266,X_267); |
| X_431 := algebra.leftfetchjoin(X_107,X_367); |
| (X_521,X_522) := algebra.join(X_431,X_17); |
| X_591 := algebra.leftfetchjoin(X_522,X_24); |
| (X_707,X_708,X_709) := group.subgroupdone(X_591); |
| X_710 := algebra.leftfetchjoin(X_708,X_591); |
| X_109:bat[:oid,:oid] := sql.tid(X_2,"sys","fact_perf",27,32); |
| X_175:bat[:oid,:lng] := sql.bind(X_2,"sys","fact_perf","dim_key",0,27,32); |
| (X_269:bat[:oid,:oid] ,X_270:bat[:oid,:lng] ) := sql.bind(X_2,"sys","fact_perf","dim_key",2,27,32); |
| X_369 := sql.delta(X_175,X_269,X_270); |
| X_433 := algebra.leftfetchjoin(X_109,X_369); |
| (X_524,X_525) := algebra.join(X_433,X_17); |
| X_593 := algebra.leftfetchjoin(X_525,X_24); |
| (X_711,X_712,X_713) := group.subgroupdone(X_593); |
| X_714 := algebra.leftfetchjoin(X_712,X_593); |
| X_111:bat[:oid,:oid] := sql.tid(X_2,"sys","fact_perf",28,32); |
| X_177:bat[:oid,:lng] := sql.bind(X_2,"sys","fact_perf","dim_key",0,28,32); |
| (X_272:bat[:oid,:oid] ,X_273:bat[:oid,:lng] ) := sql.bind(X_2,"sys","fact_perf","dim_key",2,28,32); |
| X_371 := sql.delta(X_177,X_272,X_273); |
| X_435 := algebra.leftfetchjoin(X_111,X_371); |
| (X_527,X_528) := algebra.join(X_435,X_17); |
| X_595 := algebra.leftfetchjoin(X_528,X_24); |
| (X_715,X_716,X_717) := group.subgroupdone(X_595); |
| X_718 := algebra.leftfetchjoin(X_716,X_595); |
| X_113:bat[:oid,:oid] := sql.tid(X_2,"sys","fact_perf",29,32); |
| X_179:bat[:oid,:lng] := sql.bind(X_2,"sys","fact_perf","dim_key",0,29,32); |
| (X_275:bat[:oid,:oid] ,X_276:bat[:oid,:lng] ) := sql.bind(X_2,"sys","fact_perf","dim_key",2,29,32); |
| X_373 := sql.delta(X_179,X_275,X_276); |
| X_437 := algebra.leftfetchjoin(X_113,X_373); |
| (X_530,X_531) := algebra.join(X_437,X_17); |
| X_597 := algebra.leftfetchjoin(X_531,X_24); |
| (X_719,X_720,X_721) := group.subgroupdone(X_597); |
| X_722 := algebra.leftfetchjoin(X_720,X_597); |
| X_115:bat[:oid,:oid] := sql.tid(X_2,"sys","fact_perf",30,32); |
| X_181:bat[:oid,:lng] := sql.bind(X_2,"sys","fact_perf","dim_key",0,30,32); |
| (X_278:bat[:oid,:oid] ,X_279:bat[:oid,:lng] ) := sql.bind(X_2,"sys","fact_perf","dim_key",2,30,32); |
| X_375 := sql.delta(X_181,X_278,X_279); |
| X_439 := algebra.leftfetchjoin(X_115,X_375); |
| (X_533,X_534) := algebra.join(X_439,X_17); |
| X_599 := algebra.leftfetchjoin(X_534,X_24); |
| (X_723,X_724,X_725) := group.subgroupdone(X_599); |
| X_726 := algebra.leftfetchjoin(X_724,X_599); |
| X_117:bat[:oid,:oid] := sql.tid(X_2,"sys","fact_perf",31,32); |
| X_183:bat[:oid,:lng] := sql.bind(X_2,"sys","fact_perf","dim_key",0,31,32); |
| (X_281:bat[:oid,:oid] ,X_282:bat[:oid,:lng] ) := sql.bind(X_2,"sys","fact_perf","dim_key",2,31,32); |
| X_8 := sql.bind(X_2,"sys","fact_perf","dim_key",1); |
| X_377 := sql.delta(X_183,X_281,X_282,X_8); |
| X_441 := algebra.leftfetchjoin(X_117,X_377); |
| (X_536,X_537) := algebra.join(X_441,X_17); |
| X_601 := algebra.leftfetchjoin(X_537,X_24); |
| (X_727,X_728,X_729) := group.subgroupdone(X_601); |
| X_730 := algebra.leftfetchjoin(X_728,X_601); |
| X_732 := mat.packIncrement(X_606,32); |
| X_734 := mat.packIncrement(X_732,X_610); |
| X_735 := mat.packIncrement(X_734,X_614); |
| X_736 := mat.packIncrement(X_735,X_618); |
| X_737 := mat.packIncrement(X_736,X_622); |
| X_738 := mat.packIncrement(X_737,X_626); |
| X_739 := mat.packIncrement(X_738,X_630); |
| X_740 := mat.packIncrement(X_739,X_634); |
| X_741 := mat.packIncrement(X_740,X_638); |
| X_742 := mat.packIncrement(X_741,X_642); |
| X_743 := mat.packIncrement(X_742,X_646); |
| X_744 := mat.packIncrement(X_743,X_650); |
| X_745 := mat.packIncrement(X_744,X_654); |
| X_746 := mat.packIncrement(X_745,X_658); |
| X_747 := mat.packIncrement(X_746,X_662); |
| X_748 := mat.packIncrement(X_747,X_666); |
| X_749 := mat.packIncrement(X_748,X_670); |
| X_750 := mat.packIncrement(X_749,X_674); |
| X_751 := mat.packIncrement(X_750,X_678); |
| X_752 := mat.packIncrement(X_751,X_682); |
| X_753 := mat.packIncrement(X_752,X_686); |
| X_754 := mat.packIncrement(X_753,X_690); |
| X_755 := mat.packIncrement(X_754,X_694); |
| X_756 := mat.packIncrement(X_755,X_698); |
| X_757 := mat.packIncrement(X_756,X_702); |
| X_758 := mat.packIncrement(X_757,X_706); |
| X_759 := mat.packIncrement(X_758,X_710); |
| X_760 := mat.packIncrement(X_759,X_714); |
| X_761 := mat.packIncrement(X_760,X_718); |
| X_762 := mat.packIncrement(X_761,X_722); |
| X_763 := mat.packIncrement(X_762,X_726); |
| X_25 := mat.packIncrement(X_763,X_730); |
| (X_26,r1_34,X_731) := group.subgroupdone(X_25); |
| X_28 := algebra.leftfetchjoin(r1_34,X_25); |
| language.pass(X_10); |
| language.pass(X_539); |
| language.pass(X_541); |
| language.pass(X_543); |
| language.pass(X_545); |
| language.pass(X_547); |
| language.pass(X_549); |
| language.pass(X_551); |
| language.pass(X_553); |
| language.pass(X_555); |
| language.pass(X_557); |
| language.pass(X_559); |
| language.pass(X_561); |
| language.pass(X_563); |
| language.pass(X_565); |
| language.pass(X_567); |
| language.pass(X_569); |
| language.pass(X_571); |
| language.pass(X_573); |
| language.pass(X_575); |
| language.pass(X_577); |
| language.pass(X_579); |
| language.pass(X_581); |
| language.pass(X_583); |
| language.pass(X_585); |
| language.pass(X_587); |
| language.pass(X_589); |
| language.pass(X_591); |
| language.pass(X_593); |
| language.pass(X_595); |
| language.pass(X_597); |
| language.pass(X_599); |
| language.pass(X_17); |
| language.pass(X_24); |
| language.pass(X_601); |
| language.pass(X_25); |
| exit X_765; |
| X_764:bat[:oid,:str] := nil:bat[:oid,:str]; |
| X_29 := sql.resultSet(1,1,X_28); |
| sql.rsColumn(X_29,"sys.dim_perf","name","varchar",15,0,X_28); |
| X_35 := io.stdout(); |
| sql.exportResult(X_35,X_29); |
| end s8_1; |
+---------------------------------------------------------------------------------------------------------+

Comment 19336

Date: 2013-11-15 12:35:31 +0100
From: @njnes

I tested with a small test set and --forcemito (ie even on small sizes the mitosis optimizer kicks in). And then I see that both queries get horizontally partitioned.

Comment 19353

Date: 2013-11-22 00:34:42 +0100
From: Klaudiusz <>

(In reply to comment 4)

I tested with a small test set and --forcemito (ie even on small sizes the
mitosis optimizer kicks in). And then I see that both queries get
horizontally partitioned.

I've tested --forcemito option and I can confirm that with --forcemito everything works OK, but unfortunately without --forcemito problem still exist (at least by me)

-- (4) --forcemito Duration - 2 s slightly worse than (2)
SELECT dim_perf.name from "sys"."fact_perf" join dim_perf using (dim_key) group by dim_perf.name ;

Please let me know if there is anything else what can I do to investigate this problem.

BTW is it safe to use --forcemito option as workaround in a production environment ?
How can I pass this option from monetdbd to mserver5 ? I had to use some ugly hack for testing.

Comment 19354

Date: 2013-11-22 07:46:34 +0100
From: @njnes

If forcemito helps then your database isn't large enough to trigger mitosis. The largest table should be atleast MINPARTCNT, ie larger then 100000 rows.

Comment 19355

Date: 2013-11-22 12:26:08 +0100
From: Klaudiusz <>

(In reply to comment 6)

If forcemito helps then your database isn't large enough to trigger mitosis.
The largest table should be atleast MINPARTCNT, ie larger then 100000 rows.

The largest table has 100000000 rows, the smaller one 10000.
Even if I change MINPARTCNT to 9000 problem still exist.

Let me summarize.
If I drop FK then mitosis is triggered.
If I add agregator (for example count(id)) mitosis is also triggered.

for example this two queries are equal:

SELECT dim_perf.name from "sys"."fact_perf" join dim_perf using (dim_key) group by dim_perf.name order by rand(count(id));

SELECT dim_perf.name from "sys"."fact_perf" join dim_perf using (dim_key) group by dim_perf.name;

If there is FK then only the first query triggers mitosis.
If there is no FK then both query trigger mitosis.

Comment 19356

Date: 2013-11-23 15:05:26 +0100
From: Klaudiusz <>

It looks like mitosis optimizer doesn't recognize that the larger table is involved in query.

SELECT dim_perf.name from "sys"."fact_perf" join dim_perf using (dim_key) group by dim_perf.name;

in log:
opt_mitosis: target is sys.dim_perf with 4 rows of size 8 into 5642340522 rows/piece 32 threads 1 pieces fixed parts 0 fixed size 0

SELECT dim_perf.name from "sys"."fact_perf" join dim_perf using (dim_key) group by dim_perf.name order by count(id);

in log:

opt_mitosis: target is sys.fact_perf with 111000000 rows of size 8 into 5642340522 rows/piece 32 threads 32 pieces fixed parts 0 fixed size 0

forcemito helps only partially due to this part in code

pieces = (int) MIN((wrd) threads, rowcnt);

If the smaller table isn't large enough query will be not spread across all cpu cores.

Comment 19366

Date: 2013-12-01 13:34:19 +0100
From: @njnes

okay this seems to indicate that the rowcnt of a foreignkey isn't properly used/set. So the code to pass the rowcnt count be wrong or the code to use a foreignkey in mitosis.

Comment 19367

Date: 2013-12-01 13:41:57 +0100
From: @njnes

Could you test the following patch?

diff --git a/monetdb5/optimizer/opt_mitosis.c b/monetdb5/optimizer/opt_mitosis.c
--- a/monetdb5/optimizer/opt_mitosis.c
+++ b/monetdb5/optimizer/opt_mitosis.c
@@ -79,7 +79,7 @@ OPTmitosisImplementation(Client cntxt, M
return 0;

	/* locate the largest non-partitioned table */

•  if (getModuleId(p) != sqlRef || getFunctionId(p) != bindRef)
  

•  if (getModuleId(p) != sqlRef || (getFunctionId(p) != bindRef && getFunctionId(p) != bindidxRef))
   	continue;
   /* don't split insert BATs */
   if (getVarConstant(mb, getArg(p, 5)).val.ival == 1)
  

Comment 19368

Date: 2013-12-01 18:29:20 +0100
From: Klaudiusz <>

(In reply to comment 10)

Could you test the following patch?

It works! Query is spread across all cpu cores. Thank you!

Comment 19369

Date: 2013-12-01 20:43:58 +0100
From: @njnes

Fix checked into the Feb branch

Comment 19370

Date: 2013-12-01 20:45:12 +0100
From: MonetDB Mercurial Repository <>

Changeset 9be54f5eba3e made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=9be54f5eba3e

Changeset description:

fix for (performance) bug #3391

Comment 19371

Date: 2013-12-01 21:51:32 +0100
From: MonetDB Mercurial Repository <>

Changeset 914695159abb made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=914695159abb

Changeset description:

fixed performance bug #3391

Comment 19625

Date: 2014-02-20 15:03:11 +0100
From: @sjoerdmullender

Jan2014 has been released.