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=
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
--
| Stefan.Manegold@CWI.nl | DB Architectures (DA) |
| www.CWI.nl/~manegold/ | Science Park 123 (L321) |
| +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
