Giving monetdb a fair chance

Franck Routier franck.routier at axege.com
Thu Jul 11 19:06:03 CEST 2013


Here is the result of explain : http://pastebin.com/MKt4XxdF

Here is the result for plan :

sql>plan select temps_mois.rfoperyea as c0, temps_mois.rfoperdmo as c1, 
rfovsn.rfovsnide as c2, rfoadv_1.rfoadvsup as c3, rsaaev_2.rsaaevsup as 
c4, sum((case when dwhinv.dwhinv___rfodomide = 'CLINIQUE'  and 
dwhinv.dwhinv___rfoindide = 'cli_nb_entrees_mco' then dwhinv.dwhinvqte 
else 0 end)) as m0, sum((case when dwhinv.dwhinv___rfodomide = 
'CLINIQUE'  and dwhinv.dwhinv___rfoindide = '16' then dwhinv.dwhinvqte 
else 0 end)) as m1 from rfoper_temps_mois as temps_mois, dwhinv as 
dwhinv, rfovsn as rfovsn, rfoadv as rfoadv_1, rsaaev as rsaaev_2 where 
(temps_mois.rfoper___rforefide = 'CHUL') and (dwhinv.dwhinv___rforefide 
= 'CHUL' and (
more>more>(dwhinv.dwhinv___rfodomide = 'CLINIQUE' and 
dwhinv.dwhinv___rfoindide = 'cli_nb_entrees_mco') or
more>more>(dwhinv.dwhinv___rfodomide = 'CLINIQUE' and 
dwhinv.dwhinv___rfoindide = '16'))) and dwhinv.dwhinvdtd = 
temps_mois.rfoperdtd and temps_mois.rfoperyea in ('2011', '2012') and 
temps_mois.rfoperdmo in ('01', '02') and (rfovsn___rforefide = 'CHUL') 
and dwhinv.dwhinv___rfovsnide = rfovsn.rfovsnide and rfovsn.rfovsnide = 
'201204_reel' and (rfoadv_1.rfoadv___rfovdeide='MCO' AND 
rfoadv_1.rfoadvrvs=1 AND rfoadv_1.rfoadv___rforefide= 'CHUL') and 
dwhinv.dwhinv_d2rfodstide = rfoadv_1.rfoadvinf and rfoadv_1.rfoadvsup in 
('CHIR', 'MED', 'OBS') and 
(rsaaev_2.rsaaev___rsavedide='PRISE_EN_CHARGE' AND rsaaev_2.rsaaevrvs=1 
AND rsaaev_2.rsaaev___rforefide= 'CHUL') and dwhinv.dwhinv___rsaedtide = 
rsaaev_2.rsaaevinf and rsaaev_2.rsaaevsup = 'REG_HOSPI_SEANCE' group by 
temps_mois.rfoperyea, temps_mois.rfoperdmo, rfovsn.rfovsnide, 
rfoadv_1.rfoadvsup, rsaaev_2.rsaaevsup;
SELECT: identifier 'more' unknown
sql>plan select temps_mois.rfoperyea as c0, temps_mois.rfoperdmo as c1, 
rfovsn.rfovsnide as c2, rfoadv_1.rfoadvsup as c3, rsaaev_2.rsaaevsup as 
c4, sum((case when dwhinv.dwhinv___rfodomide = 'CLINIQUE'  and 
dwhinv.dwhinv___rfoindide = 'cli_nb_entrees_mco' then dwhinv.dwhinvqte 
else 0 end)) as m0, sum((case when dwhinv.dwhinv___rfodomide = 
'CLINIQUE'  and dwhinv.dwhinv___rfoindide = '16' then dwhinv.dwhinvqte 
else 0 end)) as m1 from rfoper_temps_mois as temps_mois, dwhinv as 
dwhinv, rfovsn as rfovsn, rfoadv as rfoadv_1, rsaaev as rsaaev_2 where 
(temps_mois.rfoper___rforefide = 'CHUL') and (dwhinv.dwhinv___rforefide 
= 'CHUL' and (
more>(dwhinv.dwhinv___rfodomide = 'CLINIQUE' and 
dwhinv.dwhinv___rfoindide = 'cli_nb_entrees_mco') or
more>(dwhinv.dwhinv___rfodomide = 'CLINIQUE' and 
dwhinv.dwhinv___rfoindide = '16'))) and dwhinv.dwhinvdtd = 
temps_mois.rfoperdtd and temps_mois.rfoperyea in ('2011', '2012') and 
temps_mois.rfoperdmo in ('01', '02') and (rfovsn___rforefide = 'CHUL') 
and dwhinv.dwhinv___rfovsnide = rfovsn.rfovsnide and rfovsn.rfovsnide = 
'201204_reel' and (rfoadv_1.rfoadv___rfovdeide='MCO' AND 
rfoadv_1.rfoadvrvs=1 AND rfoadv_1.rfoadv___rforefide= 'CHUL') and 
dwhinv.dwhinv_d2rfodstide = rfoadv_1.rfoadvinf and rfoadv_1.rfoadvsup in 
('CHIR', 'MED', 'OBS') and 
(rsaaev_2.rsaaev___rsavedide='PRISE_EN_CHARGE' AND rsaaev_2.rsaaevrvs=1 
AND rsaaev_2.rsaaev___rforefide= 'CHUL') and dwhinv.dwhinv___rsaedtide = 
rsaaev_2.rsaaevinf and rsaaev_2.rsaaevsup = 'REG_HOSPI_SEANCE' group by 
temps_mois.rfoperyea, temps_mois.rfoperdmo, rfovsn.rfovsnide, 
rfoadv_1.rfoadvsup, rsaaev_2.rsaaevsup;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| rel |
+===============================================================================================================================================================================+
| project ( |
| | group by ( |
| | | project ( |
| | | | join ( |
| | | | | join ( |
| | | | | | join ( |
| | | | | | | join ( |
| | | | | | | | join ( |
| | | | | | | | | join ( |
| | | | | | | | | | select ( |
| | | | | | | | | | | table(axabas.dwhinv) [ dwhinv.dwhinv___rforefide 
NOT NULL, dwhinv.dwhinv___rfodomide NOT NULL, dwhinv.dwhinv___rfovsnide 
NOT NULL, dwhinv.dwhinv___rfoind |
: ide NOT NULL, dwhinv.dwhinvqte, dwhinv.dwhinvdtd NOT NULL, 
dwhinv.dwhinv_d2rfodstide, dwhinv.dwhinv___rsaedtide, dwhinv.%TID% NOT 
NULL ] COUNT                                :
| | | | | | | | | | ) [ dwhinv.dwhinv___rforefide NOT NULL = 
varchar(32)[char(4) "CHUL"], dwhinv.dwhinv___rfodomide NOT NULL = 
varchar(32)[char(8) "CLINIQUE"], (dwhinv.dwhinv_ |
: __rfoindide NOT NULL = varchar(32)[char(18) "cli_nb_entrees_mco"]) or 
(dwhinv.dwhinv___rfoindide NOT NULL = varchar(32)[char(2) "16"]), 
dwhinv.dwhinv___rfoindide NOT NULL in :
:  (varchar(32)[char(18) "cli_nb_entrees_mco"], varchar(32)[char(2) 
"16"]) ], :
| | | | | | | | | | select ( |
| | | | | | | | | | | table(axabas.rfoper_temps_mois) [ 
rfoper_temps_mois.rfoper___rforefide as temps_mois.rfoper___rforefide, 
rfoper_temps_mois.rfoperyea as temps_mois.rfoper |
: yea, rfoper_temps_mois.rfoperdmo as temps_mois.rfoperdmo, 
rfoper_temps_mois.rfoperdtd as temps_mois.rfoperdtd, 
rfoper_temps_mois.%TID% NOT NULL as temps_mois.%TID% ] COUNT   :
| | | | | | | | | | ) [ temps_mois.rfoper___rforefide = 
varchar(32)[char(4) "CHUL"], temps_mois.rfoperyea in 
(varchar(32)[char(4) "2011"], varchar(32)[char(4) "2012"]), temps_ |
: mois.rfoperdmo in (varchar(32)[char(2) "01"], varchar(32)[char(2) 
"02"]) ] :
| | | | | | | | | ) [ dwhinv.dwhinvdtd NOT NULL = temps_mois.rfoperdtd ], |
| | | | | | | | | select ( |
| | | | | | | | | | table(axabas.rfovsn) [ rfovsn.rfovsn___rforefide NOT 
NULL, rfovsn.rfovsnide NOT NULL, rfovsn.%TID% NOT NULL ] 
COUNT                                         |
| | | | | | | | | ) [ rfovsn.rfovsn___rforefide NOT NULL = 
varchar(32)[char(4) "CHUL"], rfovsn.rfovsnide NOT NULL = 
varchar(32)[char(11) "201204_reel"] ]                       |
| | | | | | | | ) [ dwhinv.dwhinv___rfovsnide NOT NULL = 
rfovsn.rfovsnide NOT NULL ], |
| | | | | | | | table(axabas.rfoade) [ rfoade.rfoade___rforefide NOT 
NULL as ade2.rfoade___rforefide, rfoade.rfoade___rfovdeide NOT NULL as 
ade2.rfoade___rfovdeide, rfoade.rfo |
: ade_i_rfodstide NOT NULL as ade2.rfoade_i_rfodstide, rfoade.rfoadervs 
NOT NULL as ade2.rfoadervs, rfoade.rfoadegch NOT NULL as ade2.rfoadegch, 
rfoade.rfoadedrt NOT NULL as a :
: de2.rfoadedrt, rfoade.%TID% NOT NULL as ade2.%TID% ] COUNT :
| | | | | | | ) [ dwhinv.dwhinv_d2rfodstide = ade2.rfoade_i_rfodstide 
NOT NULL ], |
| | | | | | | select ( |
| | | | | | | | table(axabas.rfoade) [ rfoade.rfoade___rforefide NOT 
NULL as ade1.rfoade___rforefide, rfoade.rfoade___rfovdeide NOT NULL as 
ade1.rfoade___rfovdeide, rfoade.rfo |
: ade_i_rfodstide NOT NULL as ade1.rfoade_i_rfodstide, rfoade.rfoadervs 
NOT NULL as ade1.rfoadervs, rfoade.rfoadegch NOT NULL as ade1.rfoadegch, 
rfoade.rfoadedrt NOT NULL as a :
: de1.rfoadedrt, rfoade.%TID% NOT NULL as ade1.%TID% ] COUNT :
| | | | | | | ) [ ade1.rfoadervs NOT NULL = int[tinyint "1"], 
ade1.rfoade___rfovdeide NOT NULL = varchar(32)[char(3) "MCO"], 
ade1.rfoade___rforefide NOT NULL = varchar(32)[cha |
: r(4) "CHUL"], ade1.rfoade_i_rfodstide NOT NULL in (varchar(32)[char(4) 
"CHIR"], varchar(32)[char(3) "MED"], varchar(32)[char(3) "OBS"]) ] :
| | | | | | ) [ ade2.rfoade___rforefide NOT NULL = 
ade1.rfoade___rforefide NOT NULL, ade2.rfoade___rfovdeide NOT NULL = 
ade1.rfoade___rfovdeide NOT NULL, ade2.rfoadervs NOT NU |
: LL = ade1.rfoadervs NOT NULL, ade2.rfoadegch NOT NULL >= 
ade1.rfoadegch NOT NULL, ade2.rfoadedrt NOT NULL <= ade1.rfoadedrt NOT 
NULL ],                                       :
| | | | | | table(axabas.rsaaed) [ rsaaed.rsaaed___rforefide NOT NULL as 
aed2.rsaaed___rforefide, rsaaed.rsaaed___rsavedide NOT NULL as 
aed2.rsaaed___rsavedide, rsaaed.rsaaedr |
: vs NOT NULL as aed2.rsaaedrvs, rsaaed.rsaaed_i_rsaedtide NOT NULL as 
aed2.rsaaed_i_rsaedtide, rsaaed.rsaaedgch NOT NULL as aed2.rsaaedgch, 
rsaaed.rsaaeddrt NOT NULL as aed2. :
: rsaaeddrt, rsaaed.%TID% NOT NULL as aed2.%TID% ] COUNT :
| | | | | ) [ dwhinv.dwhinv___rsaedtide = aed2.rsaaed_i_rsaedtide NOT 
NULL ], |
| | | | | select ( |
| | | | | | table(axabas.rsaaed) [ rsaaed.rsaaed___rforefide NOT NULL as 
aed1.rsaaed___rforefide, rsaaed.rsaaed___rsavedide NOT NULL as 
aed1.rsaaed___rsavedide, rsaaed.rsaaedr |
: vs NOT NULL as aed1.rsaaedrvs, rsaaed.rsaaed_i_rsaedtide NOT NULL as 
aed1.rsaaed_i_rsaedtide, rsaaed.rsaaedgch NOT NULL as aed1.rsaaedgch, 
rsaaed.rsaaeddrt NOT NULL as aed1. :
: rsaaeddrt, rsaaed.%TID% NOT NULL as aed1.%TID% ] COUNT :
| | | | | ) [ aed1.rsaaedrvs NOT NULL = int[tinyint "1"], 
aed1.rsaaed___rforefide NOT NULL = varchar(32)[char(4) "CHUL"], 
aed1.rsaaed___rsavedide NOT NULL = varchar(32)[char(1 |
: 5) "PRISE_EN_CHARGE"], aed1.rsaaed_i_rsaedtide NOT NULL = 
varchar(32)[char(16) "REG_HOSPI_SEANCE"] ] :
| | | | ) [ aed2.rsaaed___rforefide NOT NULL = aed1.rsaaed___rforefide 
NOT NULL, aed2.rsaaed___rsavedide NOT NULL = aed1.rsaaed___rsavedide NOT 
NULL, aed2.rsaaedrvs NOT NULL = |
:  aed1.rsaaedrvs NOT NULL, aed2.rsaaedgch NOT NULL >= aed1.rsaaedgch 
NOT NULL, aed2.rsaaeddrt NOT NULL <= aed1.rsaaeddrt NOT NULL 
]                                            :
| | | ) [ temps_mois.rfoperyea, temps_mois.rfoperdmo, temps_mois.%TID% 
NOT NULL, dwhinv.dwhinv___rfodomide NOT NULL, dwhinv.dwhinv___rfoindide 
NOT NULL, dwhinv.dwhinvqte, dwhi |
: nv.%TID% NOT NULL, rfovsn.rfovsnide NOT NULL, rfovsn.%TID% NOT NULL, 
ade1.rfoade___rforefide NOT NULL as rfoadv_1.rfoadv___rforefide, 
ade1.rfoade___rfovdeide NOT NULL as rfo :
: adv_1.rfoadv___rfovdeide, ade1.rfoadervs NOT NULL as 
rfoadv_1.rfoadvrvs, ade1.rfoade_i_rfodstide NOT NULL as 
rfoadv_1.rfoadvsup, ade2.rfoade_i_rfodstide NOT NULL as rfoadv_1 :
: .rfoadvinf, ade1.%TID% NOT NULL, ade2.%TID% NOT NULL, 
aed1.rsaaed___rforefide NOT NULL as rsaaev_2.rsaaev___rforefide, 
aed1.rsaaed___rsavedide NOT NULL as rsaaev_2.rsaaev___ :
: rsavedide, aed1.rsaaedrvs NOT NULL as rsaaev_2.rsaaevrvs, 
aed1.rsaaed_i_rsaedtide NOT NULL as rsaaev_2.rsaaevsup, 
aed2.rsaaed_i_rsaedtide NOT NULL as rsaaev_2.rsaaevinf, aed :
: 1.%TID% NOT NULL, aed2.%TID% NOT NULL ] :
| | ) [ temps_mois.rfoperyea, temps_mois.rfoperdmo, rfovsn.rfovsnide NOT 
NULL, rfoadv_1.rfoadvsup NOT NULL, rsaaev_2.rsaaevsup NOT NULL ] [ 
temps_mois.rfoperyea, temps_mois.rf |
: operdmo, rfovsn.rfovsnide NOT NULL, rfoadv_1.rfoadvsup NOT NULL, 
rsaaev_2.rsaaevsup NOT NULL, sys.sum no nil 
(sys.ifthenelse(sys.ifthenelse(sys.isnull(sys.and(sys.=(dwhinv.d :
: whinv___rfodomide NOT NULL, varchar(32)[char(8) "CLINIQUE"]), 
sys.=(dwhinv.dwhinv___rfoindide NOT NULL, varchar(32)[char(18) 
"cli_nb_entrees_mco"]))), boolean "false", sys.a :
: nd(sys.=(dwhinv.dwhinv___rfodomide NOT NULL, varchar(32)[char(8) 
"CLINIQUE"]), sys.=(dwhinv.dwhinv___rfoindide NOT NULL, 
varchar(32)[char(18) "cli_nb_entrees_mco"]))), dwhin :
: v.dwhinvqte, decimal(18,6)[tinyint "0"])) as L1, sys.sum no nil 
(sys.ifthenelse(sys.ifthenelse(sys.isnull(sys.and(sys.=(dwhinv.dwhinv___rfodomide 
NOT NULL, varchar(32)[char( :
: 8) "CLINIQUE"]), sys.=(dwhinv.dwhinv___rfoindide NOT NULL, 
varchar(32)[char(2) "16"]))), boolean "false", 
sys.and(sys.=(dwhinv.dwhinv___rfodomide NOT NULL, varchar(32)[char( :
: 8) "CLINIQUE"]), sys.=(dwhinv.dwhinv___rfoindide NOT NULL, 
varchar(32)[char(2) "16"]))), dwhinv.dwhinvqte, decimal(18,6)[tinyint 
"0"])) as L2 ] :
| ) [ temps_mois.rfoperyea as L.c0, temps_mois.rfoperdmo as L.c1, 
rfovsn.rfovsnide NOT NULL as L.c2, rfoadv_1.rfoadvsup NOT NULL as L.c3, 
rsaaev_2.rsaaevsup NOT NULL as L.c4,  |
: L1 as L1.m0, L2 as L2.m1 ] :
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
35 tuples (3.899ms)
sql>


-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 4477 bytes
Desc: Signature cryptographique S/MIME
URL: <http://www.monetdb.org/pipermail/users-list/attachments/20130711/1c67678e/attachment.p7s>


More information about the users-list mailing list