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=
[36915397] := algebra.subselect(X_550= :bat[:oid,:str][36915434],X_476= :bat[:oid,:oid][36915434],A7="CHUL",A7="CHUL",true,tru | | 1950533 | X_756= [36915434] := algebra.subselect(X_548= :bat[:oid,:str][36915434],X_474= :bat[:oid,:oid][36915434],A7="CHUL",A7="CHUL",true,tru | | 1702679 | X_758= [36915434] := algebra.subselect(X_552= :bat[:oid,:str][36915434],X_478= :bat[:oid,:oid][36915434],A7="CHUL",A7="CHUL",true,tru | | 1612725 | X_755= [36904002] := algebra.subselect(X_546= :bat[:oid,:str][36915434],X_472= :bat[:oid,:oid][36915434],A7="CHUL",A7="CHUL",true,tru | | 1286316 | X_787= [18846579] := algebra.subselect(X_517= :bat[:oid,:str][36915434],X_774= [36915397],A8="CLINIQUE",A8="CLINIQUE",true,true,false | | 1243002 | X_754= [36915434] := algebra.subselect(X_544= :bat[:oid,:str][36915434],X_470= :bat[:oid,:oid][36915434],A7="CHUL",A7="CHUL",true,tru | | 1134769 | X_846= [18846579] := algebra.subselect(X_517= :bat[:oid,:str][36915434],X_774= [36915397],A10="CLINIQUE",A10="CLINIQUE",true,true,fal | | 1132200 | X_759= [36915437] := algebra.subselect(X_554= :bat[:oid,:str][36915437],X_480= :bat[:oid,:oid][36915437],A7="CHUL",A7="CHUL",true,tru | | 1105372 | X_841= [4137747] := algebra.subselect(X_507= :bat[:oid,:str][36915434],X_769= [36915434],A10="CLINIQUE",A10="CLINIQUE",true,true,fals | | 914882 | X_752= [36915434] := algebra.subselect(X_540= :bat[:oid,:str][36915434],X_466= :bat[:oid,:oid][36915434],A7="CHUL",A7="CHUL",true,tru | | 890052 | X_753= [36915434] := algebra.subselect(X_542= :bat[:oid,:str][36915434],X_468= :bat[:oid,:oid][36915434],A7="CHUL",A7="CHUL",true,tru | | 864087 | X_843= [5897070] := algebra.subselect(X_511= :bat[:oid,:str][36915434],X_771= [36915434],A10="CLINIQUE",A10="CLINIQUE",true,true,fals | | 764170 | X_844= [5690737] := algebra.subselect(X_513= :bat[:oid,:str][36915434],X_772= [36904002],A10="CLINIQUE",A10="CLINIQUE",true,true,fals | | 719337 | X_842= [6526518] := algebra.subselect(X_509= :bat[:oid,:str][36915434],X_770= [36915434],A10="CLINIQUE",A10="CLINIQUE",true,true,fals | | 718974 | X_789= [3696387] := algebra.subselect(X_519= :bat[:oid,:str][36915434],X_775= [36915434],A8="CLINIQUE",A8="CLINIQUE",true,true,false) | | 685787 | X_783= [5690737] := algebra.subselect(X_513= :bat[:oid,:str][36915434],X_772= [36904002],A8="CLINIQUE",A8="CLINIQUE",true,true,false) | | 648988 | X_847= [3696387] := algebra.subselect(X_519= :bat[:oid,:str][36915434],X_775= [36915434],A10="CLINIQUE",A10="CLINIQUE",true,true,fals | | 612241 | X_777= [4137747] := algebra.subselect(X_507= :bat[:oid,:str][36915434],X_769= [36915434],A8="CLINIQUE",A8="CLINIQUE",true,true,false) | | 541833 | X_781= [5897070] := algebra.subselect(X_511= :bat[:oid,:str][36915434],X_771= [36915434],A8="CLINIQUE",A8="CLINIQUE",true,true,false) | | 508678 | X_779= [6526518] := algebra.subselect(X_509= :bat[:oid,:str][36915434],X_770= [36915434],A8="CLINIQUE",A8="CLINIQUE",true,true,false) | | 464423 | X_785= [4803050] := algebra.subselect(X_515= :bat[:oid,:str][36915434],X_773= [36915434],A8="CLINIQUE",A8="CLINIQUE",true,true,false) | | 432781 | X_845= [4803050] := algebra.subselect(X_515= :bat[:oid,:str][36915434],X_773= [36915434],A10="CLINIQUE",A10="CLINIQUE",true,true,fals | | 401519 | X_791= [1531879] := algebra.subselect(X_521= :bat[:oid,:str][36915437],X_776= [36915437],A8="CLINIQUE",A8="CLINIQUE",true,true,false) | | 373662 | X_848= [1531879] := algebra.subselect(X_521= :bat[:oid,:str][36915437],X_776= [36915437],A10="CLINIQUE",A10="CLINIQUE",true,true,fals | | 237526 | X_877= [1057] := algebra.subselect(X_486= :bat[:oid,:str][36915434],X_867= [18846579],A11="16",A11="16",true,true,false); | | 220168 | X_814= [12845] := algebra.subselect(X_486= :bat[:oid,:str][36915434],X_806= [18846579],A9="cli_nb_entrees_mco",A9="cli_nb_entrees_mco | | 140860 | X_1539= [17073] := sql.projectdelta(X_1044= [17073],X_678= :bat[:oid,:str][36915434],X_693= :bat[:oid,:oid][0],X_694= [7336] := sql.projectdelta(X_1047= [7336],X_684= :bat[:oid,:str][36915434],X_700= :bat[:oid,:oid][0],X_701= [7412] := algebra.subselect(X_481= :bat[:oid,:str][36915434],X_801= [4137747],A9="cli_nb_entrees_mco",A9="cli_nb_entrees_mco", | | 98855 | X_875= [386] := algebra.subselect(X_484= :bat[:oid,:str][36915434],X_863= [5690737],A11="16",A11="16",true,true,false); | | 95194 | X_810= [10596] := algebra.subselect(X_482= :bat[:oid,:str][36915434],X_802= [6526518],A9="cli_nb_entrees_mco",A9="cli_nb_entrees_mco" | | 93175 | X_812= [16687] := algebra.subselect(X_484= :bat[:oid,:str][36915434],X_804= [5690737],A9="cli_nb_entrees_mco",A9="cli_nb_entrees_mco" | | 91491 | X_813= [2797] := algebra.subselect(X_485= :bat[:oid,:str][36915434],X_805= [4803050],A9="cli_nb_entrees_mco",A9="cli_nb_entrees_mco", | | 78025 | X_811= [12501] := algebra.subselect(X_483= :bat[:oid,:str][36915434],X_803= [5897070],A9="cli_nb_entrees_mco",A9="cli_nb_entrees_mco" | | 77577 | X_1094= [13902] := sql.projectdelta(X_1046= [13902],X_616= :bat[:oid,:str][36915434],X_632= :bat[:oid,:oid][0],X_633= [13902] := sql.projectdelta(X_1046= [13902],X_682= :bat[:oid,:str][36915434],X_698= :bat[:oid,:oid][0],X_699= [7086] := algebra.subselect(X_487= :bat[:oid,:str][36915434],X_807= [3696387],A9="cli_nb_entrees_mco",A9="cli_nb_entrees_mco", | | 59667 | X_873= [483] := algebra.subselect(X_482= :bat[:oid,:str][36915434],X_859= [6526518],A11="16",A11="16",true,true,false); | | 55559 | X_874= [420] := algebra.subselect(X_483= :bat[:oid,:str][36915434],X_861= [5897070],A11="16",A11="16",true,true,false); | | 45781 | X_872= [276] := algebra.subselect(X_481= :bat[:oid,:str][36915434],X_857= [4137747],A11="16",A11="16",true,true,false); | | 37478 | X_878= [250] := algebra.subselect(X_487= :bat[:oid,:str][36915434],X_869= [3696387],A11="16",A11="16",true,true,false); | | 37083 | X_876= [170] := algebra.subselect(X_485= :bat[:oid,:str][36915434],X_865= [4803050],A11="16",A11="16",true,true,false); | | 27566 | X_816= [5148] := algebra.subselect(X_488= :bat[:oid,:str][36915437],X_808= [1531879],A9="cli_nb_entrees_mco",A9="cli_nb_entrees_mco", | | 12331 | X_1139= [17073] := sql.projectdelta(X_1044= [17073],X_645= :bat[:oid,:str][36915434],X_660= :bat[:oid,:oid][0],X_661= [97] := algebra.subselect(X_488= :bat[:oid,:str][36915437],X_871= [1531879],A11="16",A11="16",true,true,false); | | 4839 | X_884= [0] := algebra.subselect(X_496= :bat[:oid,:str][0],A11="16",A11="16",true,true,false); | | 3144 | X_1141= [13902] := sql.projectdelta(X_1046= [13902],X_649= :bat[:oid,:str][36915434],X_665= :bat[:oid,:oid][0],X_666= [7336] := sql.projectdelta(X_1047= [7336],X_618= :bat[:oid,:str][36915434],X_634= :bat[:oid,:oid][0],X_635= [17073] := sql.projectdelta(X_1044= [17073],X_484= :bat[:oid,:str][36915434],X_495= :bat[:oid,:oid][0],X_496= [7688] := sql.projectdelta(X_1041= [7688],X_606= :bat[:oid,:str][36915434],X_621= :bat[:oid,:oid][0],X_622= [11079] := sql.projectdelta(X_1042= [11079],X_707= :bat[:oid,:lng][36915434],X_722= :bat[:oid,:oid][0],X_723= [542],X_1154= [542]) := algebra.join(X_1145= :bat[:oid,:str][542],X_127= [126847]); | | 1754 | X_2212= [17073] := sql.projectdelta(X_1044= [17073],X_513= :bat[:oid,:str][36915434],X_528= :bat[:oid,:oid][0],X_529= [17073] := sql.projectdelta(X_1044= [17073],X_612= :bat[:oid,:str][36915434],X_627= :bat[:oid,:oid][0],X_628= [2967] := sql.projectdelta(X_1045= [2967],X_647= :bat[:oid,:str][36915434],X_662= :bat[:oid,:oid][0],X_663= [11079] := sql.projectdelta(X_1042= [11079],X_608= :bat[:oid,:str][36915434],X_623= :bat[:oid,:oid][0],X_624= [7688] := sql.projectdelta(X_1041= [7688],X_573==" ":bat[:oid,:timestamp][36915434],X_588= :bat[:oid,:oid][0],X_589= | | 1379 | X_2256= [13902] := sql.projectdelta(X_1046= [13902],X_486= :bat[:oid,:str][36915434],X_500= :bat[:oid,:oid][0],X_501= [7688] := sql.projectdelta(X_1041= [7688],X_481= :bat[:oid,:str][36915434],X_489= :bat[:oid,:oid][0],X_490= [13902] := sql.projectdelta(X_1046= [13902],X_517= :bat[:oid,:str][36915434],X_533= :bat[:oid,:oid][0],X_534= [12921] := sql.projectdelta(X_1043= [12921],X_483= :bat[:oid,:str][36915434],X_493= :bat[:oid,:oid][0],X_494= [12921] := sql.projectdelta(X_1043= [12921],X_610= :bat[:oid,:str][36915434],X_625= :bat[:oid,:oid][0],X_626= [11079] := sql.projectdelta(X_1042= [11079],X_482= :bat[:oid,:str][36915434],X_491= :bat[:oid,:oid][0],X_492= [12921] := sql.projectdelta(X_1043= [12921],X_511= :bat[:oid,:str][36915434],X_526= :bat[:oid,:oid][0],X_527= ----- 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