Hi Franck These numbers add not up to 450 seconds. I have the suspicion that you have encountered the Linux problem with HugePageMap management. This can lead to unexpected stalls. Sjoerd/Niels can give you the details to avoid it. regards, Martin On 7/11/13 6:40 PM, Stefan Manegold wrote:
Hi Franck,
thanks for the info!
Given the complexity of your query, it would be very helpful for us if you could also send us the results of running your query prefixed with PLAN and EXPLAIN. Then we can check which kind of plan we generate from your query.
Looking only at the most expensive operations in your trace (see below) does not instantly ring a bell with me ...
Thanks, Stefan
| 1986607 | X_757=<tmp_14022>[36915397] := algebra.subselect(X_550=<tmp_12726>:bat[:oid,:str][36915434],X_476=<tmp_14513>:bat[:oid,:oid][36915434],A7="CHUL",A7="CHUL",true,tru | | 1950533 | X_756=<tmp_13614>[36915434] := algebra.subselect(X_548=<tmp_14646>:bat[:oid,:str][36915434],X_474=<tmp_13534>:bat[:oid,:oid][36915434],A7="CHUL",A7="CHUL",true,tru | | 1702679 | X_758=<tmp_13741>[36915434] := algebra.subselect(X_552=<tmp_14477>:bat[:oid,:str][36915434],X_478=<tmp_12452>:bat[:oid,:oid][36915434],A7="CHUL",A7="CHUL",true,tru | | 1612725 | X_755=<tmp_13666>[36904002] := algebra.subselect(X_546=<tmp_14165>:bat[:oid,:str][36915434],X_472=<tmp_12430>:bat[:oid,:oid][36915434],A7="CHUL",A7="CHUL",true,tru | | 1286316 | X_787=<tmp_14647>[18846579] := algebra.subselect(X_517=<tmp_14115>:bat[:oid,:str][36915434],X_774=<tmp_14022>[36915397],A8="CLINIQUE",A8="CLINIQUE",true,true,false | | 1243002 | X_754=<tmp_13114>[36915434] := algebra.subselect(X_544=<tmp_14143>:bat[:oid,:str][36915434],X_470=<tmp_13102>:bat[:oid,:oid][36915434],A7="CHUL",A7="CHUL",true,tru | | 1134769 | X_846=<tmp_14513>[18846579] := algebra.subselect(X_517=<tmp_14115>:bat[:oid,:str][36915434],X_774=<tmp_14022>[36915397],A10="CLINIQUE",A10="CLINIQUE",true,true,fal | | 1132200 | X_759=<tmp_13162>[36915437] := algebra.subselect(X_554=<tmp_12437>:bat[:oid,:str][36915437],X_480=<tmp_14550>:bat[:oid,:oid][36915437],A7="CHUL",A7="CHUL",true,tru | | 1105372 | X_841=<tmp_13635>[4137747] := algebra.subselect(X_507=<tmp_13627>:bat[:oid,:str][36915434],X_769=<tmp_13312>[36915434],A10="CLINIQUE",A10="CLINIQUE",true,true,fals | | 914882 | X_752=<tmp_13312>[36915434] := algebra.subselect(X_540=<tmp_12426>:bat[:oid,:str][36915434],X_466=<tmp_13635>:bat[:oid,:oid][36915434],A7="CHUL",A7="CHUL",true,tru | | 890052 | X_753=<tmp_14415>[36915434] := algebra.subselect(X_542=<tmp_14002>:bat[:oid,:str][36915434],X_468=<tmp_13577>:bat[:oid,:oid][36915434],A7="CHUL",A7="CHUL",true,tru | | 864087 | X_843=<tmp_13102>[5897070] := algebra.subselect(X_511=<tmp_13561>:bat[:oid,:str][36915434],X_771=<tmp_13114>[36915434],A10="CLINIQUE",A10="CLINIQUE",true,true,fals | | 764170 | X_844=<tmp_12430>[5690737] := algebra.subselect(X_513=<tmp_14027>:bat[:oid,:str][36915434],X_772=<tmp_13666>[36904002],A10="CLINIQUE",A10="CLINIQUE",true,true,fals | | 719337 | X_842=<tmp_13577>[6526518] := algebra.subselect(X_509=<tmp_12625>:bat[:oid,:str][36915434],X_770=<tmp_14415>[36915434],A10="CLINIQUE",A10="CLINIQUE",true,true,fals | | 718974 | X_789=<tmp_13577>[3696387] := algebra.subselect(X_519=<tmp_14557>:bat[:oid,:str][36915434],X_775=<tmp_13741>[36915434],A8="CLINIQUE",A8="CLINIQUE",true,true,false) | | 685787 | X_783=<tmp_13617>[5690737] := algebra.subselect(X_513=<tmp_14027>:bat[:oid,:str][36915434],X_772=<tmp_13666>[36904002],A8="CLINIQUE",A8="CLINIQUE",true,true,false) | | 648988 | X_847=<tmp_12452>[3696387] := algebra.subselect(X_519=<tmp_14557>:bat[:oid,:str][36915434],X_775=<tmp_13741>[36915434],A10="CLINIQUE",A10="CLINIQUE",true,true,fals | | 612241 | X_777=<tmp_12737>[4137747] := algebra.subselect(X_507=<tmp_13627>:bat[:oid,:str][36915434],X_769=<tmp_13312>[36915434],A8="CLINIQUE",A8="CLINIQUE",true,true,false) | | 541833 | X_781=<tmp_13617>[5897070] := algebra.subselect(X_511=<tmp_13561>:bat[:oid,:str][36915434],X_771=<tmp_13114>[36915434],A8="CLINIQUE",A8="CLINIQUE",true,true,false) | | 508678 | X_779=<tmp_13741>[6526518] := algebra.subselect(X_509=<tmp_12625>:bat[:oid,:str][36915434],X_770=<tmp_14415>[36915434],A8="CLINIQUE",A8="CLINIQUE",true,true,false) | | 464423 | X_785=<tmp_13102>[4803050] := algebra.subselect(X_515=<tmp_14743>:bat[:oid,:str][36915434],X_773=<tmp_13614>[36915434],A8="CLINIQUE",A8="CLINIQUE",true,true,false) | | 432781 | X_845=<tmp_13534>[4803050] := algebra.subselect(X_515=<tmp_14743>:bat[:oid,:str][36915434],X_773=<tmp_13614>[36915434],A10="CLINIQUE",A10="CLINIQUE",true,true,fals | | 401519 | X_791=<tmp_12775>[1531879] := algebra.subselect(X_521=<tmp_13002>:bat[:oid,:str][36915437],X_776=<tmp_13162>[36915437],A8="CLINIQUE",A8="CLINIQUE",true,true,false) | | 373662 | X_848=<tmp_13617>[1531879] := algebra.subselect(X_521=<tmp_13002>:bat[:oid,:str][36915437],X_776=<tmp_13162>[36915437],A10="CLINIQUE",A10="CLINIQUE",true,true,fals | | 237526 | X_877=<tmp_12441>[1057] := algebra.subselect(X_486=<tmp_14536>:bat[:oid,:str][36915434],X_867=<tmp_14513>[18846579],A11="16",A11="16",true,true,false); | | 220168 | X_814=<tmp_13710>[12845] := algebra.subselect(X_486=<tmp_14536>:bat[:oid,:str][36915434],X_806=<tmp_14647>[18846579],A9="cli_nb_entrees_mco",A9="cli_nb_entrees_mco | | 140860 | X_1539=<tmp_14027>[17073] := sql.projectdelta(X_1044=<tmp_13661>[17073],X_678=<tmp_12742>:bat[:oid,:str][36915434],X_693=<tmp_4501>:bat[:oid,:oid][0],X_694=<tmp_75 | | 121108 | X_1542=<tmp_14557>[7336] := sql.projectdelta(X_1047=<tmp_13741>[7336],X_684=<tmp_14357>:bat[:oid,:str][36915434],X_700=<tmp_4501>:bat[:oid,:oid][0],X_701=<tmp_7501 | | 101599 | X_809=<tmp_14371>[7412] := algebra.subselect(X_481=<tmp_14362>:bat[:oid,:str][36915434],X_801=<tmp_12737>[4137747],A9="cli_nb_entrees_mco",A9="cli_nb_entrees_mco", | | 98855 | X_875=<tmp_12452>[386] := algebra.subselect(X_484=<tmp_13122>:bat[:oid,:str][36915434],X_863=<tmp_12430>[5690737],A11="16",A11="16",true,true,false); | | 95194 | X_810=<tmp_14017>[10596] := algebra.subselect(X_482=<tmp_13672>:bat[:oid,:str][36915434],X_802=<tmp_13741>[6526518],A9="cli_nb_entrees_mco",A9="cli_nb_entrees_mco" | | 93175 | X_812=<tmp_14320>[16687] := algebra.subselect(X_484=<tmp_13122>:bat[:oid,:str][36915434],X_804=<tmp_13617>[5690737],A9="cli_nb_entrees_mco",A9="cli_nb_entrees_mco" | | 91491 | X_813=<tmp_13661>[2797] := algebra.subselect(X_485=<tmp_13061>:bat[:oid,:str][36915434],X_805=<tmp_13102>[4803050],A9="cli_nb_entrees_mco",A9="cli_nb_entrees_mco", | | 78025 | X_811=<tmp_14007>[12501] := algebra.subselect(X_483=<tmp_13103>:bat[:oid,:str][36915434],X_803=<tmp_13617>[5897070],A9="cli_nb_entrees_mco",A9="cli_nb_entrees_mco" | | 77577 | X_1094=<tmp_12441>[13902] := sql.projectdelta(X_1046=<tmp_13724>[13902],X_616=<tmp_13700>:bat[:oid,:str][36915434],X_632=<tmp_4501>:bat[:oid,:oid][0],X_633=<tmp_75 | | 76876 | X_1541=<tmp_13725>[13902] := sql.projectdelta(X_1046=<tmp_13724>[13902],X_682=<tmp_12722>:bat[:oid,:str][36915434],X_698=<tmp_4501>:bat[:oid,:oid][0],X_699=<tmp_75 | | 73573 | X_815=<tmp_13534>[7086] := algebra.subselect(X_487=<tmp_13151>:bat[:oid,:str][36915434],X_807=<tmp_13577>[3696387],A9="cli_nb_entrees_mco",A9="cli_nb_entrees_mco", | | 59667 | X_873=<tmp_12642>[483] := algebra.subselect(X_482=<tmp_13672>:bat[:oid,:str][36915434],X_859=<tmp_13577>[6526518],A11="16",A11="16",true,true,false); | | 55559 | X_874=<tmp_14125>[420] := algebra.subselect(X_483=<tmp_13103>:bat[:oid,:str][36915434],X_861=<tmp_13102>[5897070],A11="16",A11="16",true,true,false); | | 45781 | X_872=<tmp_14120>[276] := algebra.subselect(X_481=<tmp_14362>:bat[:oid,:str][36915434],X_857=<tmp_13635>[4137747],A11="16",A11="16",true,true,false); | | 37478 | X_878=<tmp_13472>[250] := algebra.subselect(X_487=<tmp_13151>:bat[:oid,:str][36915434],X_869=<tmp_12452>[3696387],A11="16",A11="16",true,true,false); | | 37083 | X_876=<tmp_13530>[170] := algebra.subselect(X_485=<tmp_13061>:bat[:oid,:str][36915434],X_865=<tmp_13534>[4803050],A11="16",A11="16",true,true,false); | | 27566 | X_816=<tmp_13054>[5148] := algebra.subselect(X_488=<tmp_13604>:bat[:oid,:str][36915437],X_808=<tmp_12775>[1531879],A9="cli_nb_entrees_mco",A9="cli_nb_entrees_mco", | | 12331 | X_1139=<tmp_12430>[17073] := sql.projectdelta(X_1044=<tmp_13661>[17073],X_645=<tmp_13004>:bat[:oid,:str][36915434],X_660=<tmp_4501>:bat[:oid,:oid][0],X_661=<tmp_75 | | 11638 | X_879=<tmp_14550>[97] := algebra.subselect(X_488=<tmp_13604>:bat[:oid,:str][36915437],X_871=<tmp_13617>[1531879],A11="16",A11="16",true,true,false); | | 4839 | X_884=<tmp_14217>[0] := algebra.subselect(X_496=<tmp_7501>:bat[:oid,:str][0],A11="16",A11="16",true,true,false); | | 3144 | X_1141=<tmp_14000>[13902] := sql.projectdelta(X_1046=<tmp_13724>[13902],X_649=<tmp_14023>:bat[:oid,:str][36915434],X_665=<tmp_4501>:bat[:oid,:oid][0],X_666=<tmp_75 | | 2756 | X_1095=<tmp_12632>[7336] := sql.projectdelta(X_1047=<tmp_13741>[7336],X_618=<tmp_13661>:bat[:oid,:str][36915434],X_634=<tmp_4501>:bat[:oid,:oid][0],X_635=<tmp_7501 | | 2552 | X_2252=<tmp_14360>[17073] := sql.projectdelta(X_1044=<tmp_13661>[17073],X_484=<tmp_13122>:bat[:oid,:str][36915434],X_495=<tmp_4501>:bat[:oid,:oid][0],X_496=<tmp_75 | | 2403 | X_1089=<tmp_13627>[7688] := sql.projectdelta(X_1041=<tmp_14211>[7688],X_606=<tmp_12760>:bat[:oid,:str][36915434],X_621=<tmp_4501>:bat[:oid,:oid][0],X_622=<tmp_7501 | | 2272 | X_2328=<tmp_14017>[11079] := sql.projectdelta(X_1042=<tmp_14335>[11079],X_707=<tmp_13204>:bat[:oid,:lng][36915434],X_722=<tmp_4501>:bat[:oid,:oid][0],X_723=<tmp_73 | | 1784 | (X_1153=<tmpr_12760>[542],X_1154=<tmpr_14211>[542]) := algebra.join(X_1145=<tmp_13627>:bat[:oid,:str][542],X_127=<tmp_13033>[126847]); | | 1754 | X_2212=<tmp_13122>[17073] := sql.projectdelta(X_1044=<tmp_13661>[17073],X_513=<tmp_14027>:bat[:oid,:str][36915434],X_528=<tmp_4501>:bat[:oid,:oid][0],X_529=<tmp_75 | | 1699 | X_1092=<tmp_13004>[17073] := sql.projectdelta(X_1044=<tmp_13661>[17073],X_612=<tmp_12737>:bat[:oid,:str][36915434],X_627=<tmp_4501>:bat[:oid,:oid][0],X_628=<tmp_75 | | 1621 | X_1140=<tmp_12675>[2967] := sql.projectdelta(X_1045=<tmp_13614>[2967],X_647=<tmp_12444>:bat[:oid,:str][36915434],X_662=<tmp_4501>:bat[:oid,:oid][0],X_663=<tmp_7501 | | 1571 | X_1090=<tmp_12625>[11079] := sql.projectdelta(X_1042=<tmp_14335>[11079],X_608=<tmp_14061>:bat[:oid,:str][36915434],X_623=<tmp_4501>:bat[:oid,:oid][0],X_624=<tmp_75 | | 1493 | X_1056=<tmp_14120>[7688] := sql.projectdelta(X_1041=<tmp_14211>[7688],X_573=="<tmp_13534>":bat[:oid,:timestamp][36915434],X_588=<tmp_4501>:bat[:oid,:oid][0],X_589= | | 1379 | X_2256=<tmp_14102>[13902] := sql.projectdelta(X_1046=<tmp_13724>[13902],X_486=<tmp_14536>:bat[:oid,:str][36915434],X_500=<tmp_4501>:bat[:oid,:oid][0],X_501=<tmp_75 | | 1346 | X_2246=<tmp_14371>[7688] := sql.projectdelta(X_1041=<tmp_14211>[7688],X_481=<tmp_14362>:bat[:oid,:str][36915434],X_489=<tmp_4501>:bat[:oid,:oid][0],X_490=<tmp_7501 | | 1297 | X_2216=<tmp_14063>[13902] := sql.projectdelta(X_1046=<tmp_13724>[13902],X_517=<tmp_14115>:bat[:oid,:str][36915434],X_533=<tmp_4501>:bat[:oid,:oid][0],X_534=<tmp_75 | | 1203 | X_2250=<tmp_14007>[12921] := sql.projectdelta(X_1043=<tmp_12445>[12921],X_483=<tmp_13103>:bat[:oid,:str][36915434],X_493=<tmp_4501>:bat[:oid,:oid][0],X_494=<tmp_75 | | 1112 | X_1091=<tmp_13561>[12921] := sql.projectdelta(X_1043=<tmp_12445>[12921],X_610=<tmp_13062>:bat[:oid,:str][36915434],X_625=<tmp_4501>:bat[:oid,:oid][0],X_626=<tmp_75 | | 1064 | X_2248=<tmp_13737>[11079] := sql.projectdelta(X_1042=<tmp_14335>[11079],X_482=<tmp_13672>:bat[:oid,:str][36915434],X_491=<tmp_4501>:bat[:oid,:oid][0],X_492=<tmp_75 | | 1001 | X_2210=<tmp_13770>[12921] := sql.projectdelta(X_1043=<tmp_12445>[12921],X_511=<tmp_13561>:bat[:oid,:str][36915434],X_526=<tmp_4501>:bat[:oid,:oid][0],X_527=<tmp_75 |
----- Original Message -----
Ok, I have an example query, that takes 4 sec in MonetDB. This is considered long, as Postgresql performs the same task in some 200 ms...
I have used trace to get detailed information on what happens. But this output is really detailed : 2500 lines ! Here it is : http://pastebin.com/EmhERAeF For information, here the Pg explain plan, if that can be of any help http://explain.depesz.com/s/StM
The point is that I don't know what to look for, so visualy parsing this output is quite hard :-) Any hint ?
Thanks a lot, Franck
_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list