Thread: Slow query when the select list is big

Yes but it is very big.

I don't understand why the select list is influencing the CPU usage.

I was expecting that only the join and where clauses would influence CPU.

-- 
john r pierce, recycling bits in santa cruz

Yes but it is very big.

I don't understand why the select list is influencing the CPU usage.

I was expecting that only the join and where clauses would influence CPU.

On 9 May 2016 at 18:46, David G. Johnston <david.g.johnston@gmail.com> wrote:
> On Sunday, May 8, 2016, Sterpu Victor <victor@caido.ro> wrote:
>>
>> Yes but it is very big.
>> I don't understand why the select list is influencing the CPU usage.
>> I was expecting that only the join and where clauses would influence CPU.
>>
>
> PostgreSQL is smart enough to optimize away stuff that it knows doesn't
> impact the final query result.

To be more accurate with what David is saying, PostgreSQL will remove
unused LEFT JOINed relations where the left joined relation can be
proved to not duplicate rows from the right hand side. It would just
be a matter of comparing the EXPLAINs from the query with all the
SELECT items to the one with the single SELECT item to prove that this
is what's happening.

Please also note that this only occurs with LEFT JOINs

It would also be quite helpful for people if you were to include a
copy of the query. It's impossible to reverse engineer what that is
from this EXPLAIN output. I see that your using a windowing function
and performing a LIMIT 1, there may be ways to improve that just by
selecting the single highest j1031101.validfrom row and performing the
joins to the other table on that single row, but that will depend on
which windowing function you're using as the function may require the
other rows in the window frame to calculate the correct result.

--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

I solved the problem patialy by swithing the order of a join.
I tested on a slower server to see better the difference.
After moving a single join the query runs in 4.1 seconds insted 6.4
seconds.
I pasted the optimized query down. When I move the join J1033704 at the
end(last join) the time is increased to 6.4 seconds.

SELECT
row_number() OVER (ORDER BY J1031101.validfrom DESC)  AS  nrcrt ,
CASE WHEN(CHAR_LENGTH(J1031074.signature) > 10)  THEN(1)  ELSE(0) END
AS  semnat_internare ,
CASE WHEN(CHAR_LENGTH(J1031074.signature_externare) > 10)  THEN(1)
ELSE(0) END  AS  semnat_externare ,
J1031076.name  AS  sex ,
J1031074.id  AS  id ,
J1031074.siui_appid  AS  siui_appid ,
J1031074.data_adeverinta  AS  data_adeverinta ,
J1031074.is_paliativ  AS  text_paliativ ,
J1031074.cardno  AS  cardno ,
J1031074.cardno_externare  AS  cardno_externare ,
J1031074.sign_date  AS  sign_date ,
J1031074.sign_date_externare  AS  sign_date_externare ,
J1031074.unsigned_string  AS  unsigned_string ,
J1031074.unsigned_string_externare  AS  unsigned_string_externare ,
J1031074.signhash  AS  signhash ,
J1031074.signhash_externare  AS  signhash_externare ,
J1031074.signature  AS  signature ,
J1031074.signature_externare  AS  signature_externare ,
J1031074.send_xml  AS  send_xml ,
J1031074.send_xml_externare  AS  send_xml_externare ,
J1031074.received_xml  AS  received_xml ,
J1031074.received_xml_externare  AS  received_xml_externare ,
J1031074.error  AS  error ,
J1031074.error_externare  AS  error_externare ,
J1031074.validat  AS  validat ,
J1031074.validat_externare  AS  validat_externare ,
J1031074.online  AS  online ,
J1031074.online_externare  AS  online_externare ,
J1031074.serie_bilet_internare  AS  text_serie_bilet_internare ,
J1031074.nr_bilet_internare  AS  text_numar_bilet_internare ,
J1031074.idpatient  AS  _popup_cnp_pacient ,
J1031075.cnp  AS  popup_cnp_pacient ,
J1031075.name  AS  text_nume_pacient ,
J1031075.surname  AS  text_prenume_pacient ,
J1031074.nrfo  AS  text_numar_fosz ,
J1031074.greutate_nastere  AS  text_greutate_nastere ,
J1031078.value  AS  popup_tip_asigurare ,
J1031074.idensuredstatustype  AS  _popup_tip_asigurare ,
J1031079.value  AS  popup_statut_asigurat ,
J1031074.idensuredstatus  AS  _popup_statut_asigurat ,
J1031080.code  AS  popup_cas_asigurat ,
J1031074.id_org_unit  AS  _popup_cas_asigurat ,
J1031081.code  AS  popup_categorie_asigurare ,
J1031074.id_categorie_asigurat  AS  _popup_categorie_asigurare ,
J1031082.name  AS  popup_tip_internare ,
J1031074.id_focg_tip_internare  AS  _popup_tip_internare ,
J1031083.name  AS  popup_criteriu_internare ,
J1031074.id_focg_criteriu_internare  AS  _popup_criteriu_internare ,
J1031084.stencil_no  AS  popup_medic_curant ,
J1031084.id  AS  _popup_medic_curant ,
J1031089.value  AS  popup_nivel_instruire ,
J1031074.id_education_level  AS  _popup_nivel_instruire ,
J1031074.greutate  AS  text_greutate_internare ,
J1031090.nume  AS  popup_situatii_speciale ,
J1031074.id_focg_situatii_speciale  AS  _popup_situatii_speciale ,
J1031091.nume  AS  popup_internat_prin ,
J1031091.id  AS  _popup_internat_prin ,
J1031092.nume  AS  popup_formulare_europene ,
J1031074.id_formulare_europene  AS  _popup_formulare_europene ,
J1031074.id_cnp_mama  AS  _popup_cnp_mama ,
J1031094.cnp  AS  popup_cnp_mama ,
J1031093.nrfo  AS  popup_fo_mama ,
J1031074.id_focg  AS  _popup_fo_mama ,
J1031074.nr_card_euro  AS  text_nr_card_european ,
J1031074.nr_pasaport  AS  text_nr_pasaport ,
J1031074.nr_card_national  AS  text_nr_card_national ,
J1031088.id  AS  _popup_ocupatia ,
J1031088.name  AS  popup_ocupatia ,
J1031074.export_drg  AS  export_drg ,
J1031074.drgcaseid  AS  drgcaseid ,
J1031074.export_ecosoft  AS  export_ecosoft ,
J1031074.mesaj_drg  AS  mesaj_drg ,
J1031074.uid  AS  uid ,
J1031074.mesaj_ecosoft  AS  mesaj_ecosoft ,
J1031074.id_address_domiciliu  AS  text_id_address_domiciliu ,
J1031074.id_address_domiciliu  AS  _text_id_address_domiciliu ,
J1031074.id_address_resedinta  AS  _text_id_address_resedinta ,
J1031074.id_address_resedinta  AS  text_id_address_resedinta ,
'0'  AS  marcator ,
J1031095.id  AS  _popup_sursa_internare ,
J1031095.denumire  AS  popup_sursa_internare ,
J1031096.id  AS  _popup_diseasecategory ,
J1031096.code  AS  popup_diseasecategory ,
J1031097.id  AS  _popup_diagnostic_internare_icd10 ,
J1031097.name  AS  popup_diagnostic_internare_icd10 ,
J1031098.id  AS  _popup_mod_contract ,
J1031098.description  AS  popup_mod_contract ,
J1031099.id  AS  _popup_criteriu_urgenta ,
J1031099.name  AS  popup_criteriu_urgenta ,
J1031100.id  AS  _popup_exceptie_bi ,
J1031100.code  AS  popup_exceptie_bi ,
J1031074.scrisoare_medicala_parafa  AS  text_parafa_scrisoare_medicala ,
J1031074.scrisoare_medicala_contract  AS
text_contract_scrisoare_medicala ,
J1031074.scrisoare_medicala_tip_contract  AS
text_tip_contract_scrisoare_medicala ,
J1031074.export_siui  AS  export_siui ,
J1031074.mesaj_siui  AS  mesaj_siui ,
J1031087.id  AS  _popup_intocmit ,
J1031087.stencil_no  AS  popup_intocmit ,
J1031074.diagnostic_trimitere_text  AS  text_diagnostic_trimitere ,
J1031074.greutate_externare  AS  text_greutate_externare ,
J1031074.data_decesului  AS  text_data_deces ,
J1031736.id  AS  _popup_tip_externare ,
J1031736.descriere  AS  popup_tip_externare ,
J1031737.id  AS  _popup_stare_externare ,
J1031737.descriere  AS  popup_stare_externare ,
J1033295.id  AS  _popup_chirurg ,
J1033295.stencil_no  AS  popup_chirurg ,
J1033299.id  AS  _text_caz_externare ,
J1033299.description  AS  text_caz_externare ,
J1031074.text_ore_ventilatie  AS  text_ore_ventilatie ,
J1031074.drg_cod_grupa  AS  text_drg_cod_grupa ,
J1031074.drg_relative_value  AS  text_drg_relative_value ,
J1031074.data_2500g  AS  text_data_2500g ,
J1031074.prematur_gr_i  AS  text_prematur_gr_i ,
J1033304.id  AS  _popup_cetatenie ,
J1033304.description  AS  popup_cetatenie ,
J1033358.id  AS  _popup_tip_cetatenie ,
J1033358.name  AS  popup_tip_cetatenie ,
J1031074.reinternat_transfer  AS  text_reinternat_transfer ,
J1031074.aviz_comisie  AS  text_aviz_comisie_reinternare ,
J1031074.criteriu_urgenta1  AS  criteriu_urgenta1 ,
J1031074.criteriu_urgenta2  AS  criteriu_urgenta2 ,
J1031074.criteriu_urgenta3  AS  criteriu_urgenta3 ,
J1031074.criteriu_urgenta4  AS  criteriu_urgenta4 ,
J1031074.criteriu_urgenta5  AS  criteriu_urgenta5 ,
J1031074.criteriu_urgenta6  AS  criteriu_urgenta6 ,
J1031074.criteriu_urgenta7  AS  criteriu_urgenta7 ,
J1031074.criteriu_urgenta8  AS  criteriu_urgenta8 ,
J1031074.criteriu_urgenta9a  AS  criteriu_urgenta9a ,
J1031074.criteriu_urgenta9b  AS  criteriu_urgenta9b ,
J1031074.criteriu_urgenta10  AS  criteriu_urgenta10 ,
J1031074.criteriu_urgenta11  AS  criteriu_urgenta11 ,
J1031074.criteriu_urgenta12  AS  criteriu_urgenta12 ,
J1031074.criteriu_urgenta13  AS  criteriu_urgenta13 ,
J1031074.infectie_nosocomiala  AS  infectie_nosocomiala ,
J1031074.cu_programare  AS  cu_programare ,
J1031074.stare_critica_internare  AS  stare_critica_internare ,
J1033704.id  AS  _id_stari_spitalizari ,
J1033704.nume  AS  id_stari_spitalizari
    FROM focg AS J1031074
   LEFT JOIN person AS J1031075 ON ( J1031075.id = J1031074.idpatient)
    LEFT JOIN sex AS J1031076 ON ( J1031076.id = J1031075.idsex)
    LEFT JOIN focg_serials AS J1031077 ON ( J1031077.id =
J1031074.id_focg_serials)
    LEFT JOIN ensuredstatustype AS J1031078 ON ( J1031078.id =
J1031074.idensuredstatustype)
    LEFT JOIN ensuredstatus AS J1031079 ON ( J1031079.id =
J1031074.idensuredstatus)
    LEFT JOIN org_units AS J1031080 ON ( J1031080.id =
J1031074.id_org_unit)
    LEFT JOIN personstate AS J1031081 ON ( J1031081.id =
J1031074.id_categorie_asigurat)
    LEFT JOIN focginterntype AS J1031082 ON ( J1031082.id =
J1031074.id_focg_tip_internare)
    LEFT JOIN focginterncrit AS J1031083 ON ( J1031083.id =
J1031074.id_focg_criteriu_internare)
    LEFT JOIN physicians AS J1031084 ON ( J1031084.id =
J1031074.idphysiciancurrent)
    LEFT JOIN personnel AS J1031085 ON ( J1031085.id =
J1031084.id_personnel)
    LEFT JOIN person AS J1031086 ON ( J1031086.id = J1031085.idperson)
    LEFT JOIN physicians AS J1031087 ON ( J1031087.id =
J1031074.idphysician_madeby)
    LEFT JOIN proffession AS J1031088 ON ( J1031088.id =
J1031074.idproffesion)
    LEFT JOIN educationlevel AS J1031089 ON ( J1031089.id =
J1031074.id_education_level)
    LEFT JOIN focg_situatii_speciale AS J1031090 ON ( J1031090.id =
J1031074.id_focg_situatii_speciale)
    LEFT JOIN focg_internat_prin AS J1031091 ON ( J1031091.id =
J1031074.id_focg_internat_prin)
    LEFT JOIN formulare_europene AS J1031092 ON ( J1031092.id =
J1031074.id_formulare_europene)
    LEFT JOIN focg AS J1031093 ON ( J1031093.id = J1031074.id_focg)
    LEFT JOIN person AS J1031094 ON ( J1031094.id = J1031074.id_cnp_mama)
    LEFT JOIN focg_sursa_internare AS J1031095 ON ( J1031095.id =
J1031074.id_focg_sursa_internare)
    LEFT JOIN diseasecategory AS J1031096 ON ( J1031096.id =
J1031074.id_diseasecategory)
    LEFT JOIN icd10 AS J1031097 ON ( J1031097.id = J1031074.id_icd10)
    LEFT JOIN focg_contract_modes AS J1031098 ON ( J1031098.id =
J1031074.id_focg_contract_modes)
    LEFT JOIN focg_criterii_urgenta AS J1031099 ON ( J1031099.id =
J1031074.id_focg_criterii_urgenta)
    LEFT JOIN exceptie_bilet_internare AS J1031100 ON ( J1031100.id =
J1031074.id_exceptie_bilet_internare)
    LEFT JOIN focg_tip_externare AS J1031736 ON ( J1031736.id =
J1031074.id_focg_tip_externare)
    LEFT JOIN focg_stare_externare AS J1031737 ON ( J1031737.id =
J1031074.id_focg_stare_externare)
    LEFT JOIN physicians AS J1033295 ON ( J1033295.id =
J1031074.idphysician_surgeon)
    LEFT JOIN hospital_release_statuses AS J1033299 ON ( J1033299.id =
J1031074.id_hospital_release_statuses)
    LEFT JOIN citizenship AS J1033304 ON ( J1033304.id =
J1031074.id_citizenship)
    LEFT JOIN drg_tip_cetatenie AS J1033358 ON ( J1033358.id =
J1031074.id_drg_tip_cetatenie)
    LEFT JOIN stari_spitalizari AS J1033704 ON ( J1033704.id =
J1031074.id_stari_spitalizari)
    LEFT JOIN focgdepartment AS J1031101 ON ( J1031101.idfocg =
J1031074.id)
    LEFT JOIN department AS J1033386 ON ( J1033386.id =
J1031101.iddepartment)
    LEFT JOIN focgdepartment AS J1031102 ON ( J1031102.idfocg =
J1031074.id AND  J1031102.validfrom < J1031101.validfrom)
    LEFT JOIN focgdepartment AS J1033359 ON ( J1033359.idfocg =
J1031074.id)
    LEFT JOIN department AS J1033387 ON ( J1033387.id =
J1033359.iddepartment)
    LEFT JOIN focgdepartment AS J1033360 ON ( J1033360.idfocg =
J1031074.id AND  J1033360.validfrom > J1033359.validfrom)
     JOIN focgdepartment AS J1033407 ON ( J1033407.idfocg = J1031074.id
AND  J1033407.iddepartment IN (/*Executing QueryID 476590*/SELECT
DISTINCT(J1034477.id)  AS  ret FROM department AS J1034477  JOIN
personnel_department AS J1034478 ON ( J1034478.id_department =
J1034477.id)
   JOIN personnel AS J1034479 ON ( J1034479.id = J1034478.id_personnel
AND  J1034479.id_connected_user = '1')))

  WHERE   J1031102.id IS NULL   AND    J1033360.id IS NULL   AND    (
(   DATE(J1031101.validfrom)>= DATE('2016-05-01')   AND
DATE(J1031101.validfrom)<= DATE('2016-05-31 00:00:00.0')   )    OR    (
   J1033359.validto IS NOT NULL   AND   DATE(J1033359.validto)>=
DATE('2016-05-01')   AND   DATE(J1033359.validto)<= DATE('2016-05-31
00:00:00.0')   )    OR    (    J1033359.validto IS NULL   AND
DATE(J1031101.validfrom)<= DATE('2016-05-01')   )    )

    ORDER BY J1031101.validfrom DESC
  LIMIT  20 OFFSET 0

------ Original Message ------
From: "David Rowley" <david.rowley@2ndquadrant.com>
To: "Sterpu Victor" <victor@caido.ro>
Cc: "Rob Imig" <rimig88@gmail.com>; "PostgreSQL General"
<pgsql-general@postgresql.org>; "David G. Johnston"
<david.g.johnston@gmail.com>
Sent: 9/5/2016 10:04:54 AM
Subject: Re: [GENERAL] Slow query when the select list is big

>On 9 May 2016 at 18:46, David G. Johnston <david.g.johnston@gmail.com>
>wrote:
>>  On Sunday, May 8, 2016, Sterpu Victor <victor@caido.ro> wrote:
>>>
>>>  Yes but it is very big.
>>>  I don't understand why the select list is influencing the CPU usage.
>>>  I was expecting that only the join and where clauses would influence
>>>CPU.
>>>
>>
>>  PostgreSQL is smart enough to optimize away stuff that it knows
>>doesn't
>>  impact the final query result.
>
>To be more accurate with what David is saying, PostgreSQL will remove
>unused LEFT JOINed relations where the left joined relation can be
>proved to not duplicate rows from the right hand side. It would just
>be a matter of comparing the EXPLAINs from the query with all the
>SELECT items to the one with the single SELECT item to prove that this
>is what's happening.
>
>Please also note that this only occurs with LEFT JOINs
>
>It would also be quite helpful for people if you were to include a
>copy of the query. It's impossible to reverse engineer what that is
>from this EXPLAIN output. I see that your using a windowing function
>and performing a LIMIT 1, there may be ways to improve that just by
>selecting the single highest j1031101.validfrom row and performing the
>joins to the other table on that single row, but that will depend on
>which windowing function you're using as the function may require the
>other rows in the window frame to calculate the correct result.
>
>--
>  David Rowley http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services

I went to 2.4 seconds by joining first the tables that produce many
rows.

SELECT
row_number() OVER (ORDER BY J1031101.validfrom DESC)  AS  nrcrt ,
J1033386.name  AS  sectie_internare ,
J1033387.name  AS  sectie_externare ,
TO_CHAR(J1031101.validfrom , 'YYYY-MM-DD HH24:MI')  AS  validfrom ,
TO_CHAR(J1033359.validto , 'YYYY-MM-DD HH24:MI')  AS  validto ,
CASE WHEN(CHAR_LENGTH(J1031074.signature) > 10)  THEN(1)  ELSE(0) END
AS  semnat_internare ,
CASE WHEN(CHAR_LENGTH(J1031074.signature_externare) > 10)  THEN(1)
ELSE(0) END  AS  semnat_externare ,
J1031076.name  AS  sex ,
J1031074.id  AS  id ,
J1031074.siui_appid  AS  siui_appid ,
J1031074.data_adeverinta  AS  data_adeverinta ,
J1031074.is_paliativ  AS  text_paliativ ,
J1031074.cardno  AS  cardno ,
J1031074.cardno_externare  AS  cardno_externare ,
J1031074.sign_date  AS  sign_date ,
J1031074.sign_date_externare  AS  sign_date_externare ,
J1031074.unsigned_string  AS  unsigned_string ,
J1031074.unsigned_string_externare  AS  unsigned_string_externare ,
J1031074.signhash  AS  signhash ,
J1031074.signhash_externare  AS  signhash_externare ,
J1031074.signature  AS  signature ,
J1031074.signature_externare  AS  signature_externare ,
J1031074.send_xml  AS  send_xml ,
J1031074.send_xml_externare  AS  send_xml_externare ,
J1031074.received_xml  AS  received_xml ,
J1031074.received_xml_externare  AS  received_xml_externare ,
J1031074.error  AS  error ,
J1031074.error_externare  AS  error_externare ,
J1031074.validat  AS  validat ,
J1031074.validat_externare  AS  validat_externare ,
J1031074.online  AS  online ,
J1031074.online_externare  AS  online_externare ,
J1031074.serie_bilet_internare  AS  text_serie_bilet_internare ,
J1031074.nr_bilet_internare  AS  text_numar_bilet_internare ,
J1031074.idpatient  AS  _popup_cnp_pacient ,
J1031075.cnp  AS  popup_cnp_pacient ,
J1031075.name  AS  text_nume_pacient ,
J1031075.surname  AS  text_prenume_pacient ,
J1031074.nrfo  AS  text_numar_fosz ,
J1031074.greutate_nastere  AS  text_greutate_nastere ,
J1031078.value  AS  popup_tip_asigurare ,
J1031074.idensuredstatustype  AS  _popup_tip_asigurare ,
J1031079.value  AS  popup_statut_asigurat ,
J1031074.idensuredstatus  AS  _popup_statut_asigurat ,
J1031080.code  AS  popup_cas_asigurat ,
J1031074.id_org_unit  AS  _popup_cas_asigurat ,
J1031081.code  AS  popup_categorie_asigurare ,
J1031074.id_categorie_asigurat  AS  _popup_categorie_asigurare ,
J1031082.name  AS  popup_tip_internare ,
J1031074.id_focg_tip_internare  AS  _popup_tip_internare ,
J1031083.name  AS  popup_criteriu_internare ,
J1031074.id_focg_criteriu_internare  AS  _popup_criteriu_internare ,
J1031084.stencil_no  AS  popup_medic_curant ,
J1031084.id  AS  _popup_medic_curant ,
J1031089.value  AS  popup_nivel_instruire ,
J1031074.id_education_level  AS  _popup_nivel_instruire ,
J1031074.greutate  AS  text_greutate_internare ,
J1031090.nume  AS  popup_situatii_speciale ,
J1031074.id_focg_situatii_speciale  AS  _popup_situatii_speciale ,
J1031091.nume  AS  popup_internat_prin ,
J1031091.id  AS  _popup_internat_prin ,
J1031092.nume  AS  popup_formulare_europene ,
J1031074.id_formulare_europene  AS  _popup_formulare_europene ,
J1031074.id_cnp_mama  AS  _popup_cnp_mama ,
J1031094.cnp  AS  popup_cnp_mama ,
J1031093.nrfo  AS  popup_fo_mama ,
J1031074.id_focg  AS  _popup_fo_mama ,
J1031074.nr_card_euro  AS  text_nr_card_european ,
J1031074.nr_pasaport  AS  text_nr_pasaport ,
J1031074.nr_card_national  AS  text_nr_card_national ,
J1031088.id  AS  _popup_ocupatia ,
J1031088.name  AS  popup_ocupatia ,
J1031074.export_drg  AS  export_drg ,
J1031074.drgcaseid  AS  drgcaseid ,
J1031074.export_ecosoft  AS  export_ecosoft ,
J1031074.mesaj_drg  AS  mesaj_drg ,
J1031074.uid  AS  uid ,
J1031074.mesaj_ecosoft  AS  mesaj_ecosoft ,
J1031074.id_address_domiciliu  AS  text_id_address_domiciliu ,
J1031074.id_address_domiciliu  AS  _text_id_address_domiciliu ,
J1031074.id_address_resedinta  AS  _text_id_address_resedinta ,
J1031074.id_address_resedinta  AS  text_id_address_resedinta ,
'0'  AS  marcator ,
J1031095.id  AS  _popup_sursa_internare ,
J1031095.denumire  AS  popup_sursa_internare ,
J1031096.id  AS  _popup_diseasecategory ,
J1031096.code  AS  popup_diseasecategory ,
J1031097.id  AS  _popup_diagnostic_internare_icd10 ,
J1031097.name  AS  popup_diagnostic_internare_icd10 ,
J1031098.id  AS  _popup_mod_contract ,
J1031098.description  AS  popup_mod_contract ,
J1031099.id  AS  _popup_criteriu_urgenta ,
J1031099.name  AS  popup_criteriu_urgenta ,
J1031100.id  AS  _popup_exceptie_bi ,
J1031100.code  AS  popup_exceptie_bi ,
J1031074.scrisoare_medicala_parafa  AS  text_parafa_scrisoare_medicala ,
J1031074.scrisoare_medicala_contract  AS
text_contract_scrisoare_medicala ,
J1031074.scrisoare_medicala_tip_contract  AS
text_tip_contract_scrisoare_medicala ,
J1031074.export_siui  AS  export_siui ,
J1031074.mesaj_siui  AS  mesaj_siui ,
J1031087.id  AS  _popup_intocmit ,
J1031087.stencil_no  AS  popup_intocmit ,
J1031074.diagnostic_trimitere_text  AS  text_diagnostic_trimitere ,
J1031074.greutate_externare  AS  text_greutate_externare ,
J1031074.data_decesului  AS  text_data_deces ,
J1031736.id  AS  _popup_tip_externare ,
J1031736.descriere  AS  popup_tip_externare ,
J1031737.id  AS  _popup_stare_externare ,
J1031737.descriere  AS  popup_stare_externare ,
J1033295.id  AS  _popup_chirurg ,
J1033295.stencil_no  AS  popup_chirurg ,
J1033299.id  AS  _text_caz_externare ,
J1033299.description  AS  text_caz_externare ,
J1031074.text_ore_ventilatie  AS  text_ore_ventilatie ,
J1031074.drg_cod_grupa  AS  text_drg_cod_grupa ,
J1031074.drg_relative_value  AS  text_drg_relative_value ,
J1031074.data_2500g  AS  text_data_2500g ,
J1031074.prematur_gr_i  AS  text_prematur_gr_i ,
J1033304.id  AS  _popup_cetatenie ,
J1033304.description  AS  popup_cetatenie ,
J1033358.id  AS  _popup_tip_cetatenie ,
J1033358.name  AS  popup_tip_cetatenie ,
J1031074.reinternat_transfer  AS  text_reinternat_transfer ,
J1031074.aviz_comisie  AS  text_aviz_comisie_reinternare ,
J1031074.criteriu_urgenta1  AS  criteriu_urgenta1 ,
J1031074.criteriu_urgenta2  AS  criteriu_urgenta2 ,
J1031074.criteriu_urgenta3  AS  criteriu_urgenta3 ,
J1031074.criteriu_urgenta4  AS  criteriu_urgenta4 ,
J1031074.criteriu_urgenta5  AS  criteriu_urgenta5 ,
J1031074.criteriu_urgenta6  AS  criteriu_urgenta6 ,
J1031074.criteriu_urgenta7  AS  criteriu_urgenta7 ,
J1031074.criteriu_urgenta8  AS  criteriu_urgenta8 ,
J1031074.criteriu_urgenta9a  AS  criteriu_urgenta9a ,
J1031074.criteriu_urgenta9b  AS  criteriu_urgenta9b ,
J1031074.criteriu_urgenta10  AS  criteriu_urgenta10 ,
J1031074.criteriu_urgenta11  AS  criteriu_urgenta11 ,
J1031074.criteriu_urgenta12  AS  criteriu_urgenta12 ,
J1031074.criteriu_urgenta13  AS  criteriu_urgenta13 ,
J1031074.infectie_nosocomiala  AS  infectie_nosocomiala ,
J1031074.cu_programare  AS  cu_programare ,
J1031074.stare_critica_internare  AS  stare_critica_internare ,
J1033704.id  AS  _id_stari_spitalizari ,
J1033704.nume  AS  id_stari_spitalizari
    FROM focg AS J1031074
    LEFT JOIN focgdepartment AS J1031101 ON ( J1031101.idfocg =
J1031074.id)
    LEFT JOIN department AS J1033386 ON ( J1033386.id =
J1031101.iddepartment)
    LEFT JOIN focgdepartment AS J1031102 ON ( J1031102.idfocg =
J1031074.id AND  J1031102.validfrom < J1031101.validfrom)
    LEFT JOIN focgdepartment AS J1033359 ON ( J1033359.idfocg =
J1031074.id)
    LEFT JOIN department AS J1033387 ON ( J1033387.id =
J1033359.iddepartment)
    LEFT JOIN focgdepartment AS J1033360 ON ( J1033360.idfocg =
J1031074.id AND  J1033360.validfrom > J1033359.validfrom)
    JOIN focgdepartment AS J1033407 ON ( J1033407.idfocg = J1031074.id
AND  J1033407.iddepartment IN (/*Executing QueryID 476590*/SELECT
DISTINCT(J1034477.id)  AS  ret FROM department AS J1034477  JOIN
personnel_department AS J1034478 ON ( J1034478.id_department =
J1034477.id) JOIN personnel AS J1034479 ON ( J1034479.id =
J1034478.id_personnel AND  J1034479.id_connected_user = '1')))

    LEFT JOIN person AS J1031075 ON ( J1031075.id = J1031074.idpatient)
    LEFT JOIN sex AS J1031076 ON ( J1031076.id = J1031075.idsex)
    LEFT JOIN focg_serials AS J1031077 ON ( J1031077.id =
J1031074.id_focg_serials)
    LEFT JOIN ensuredstatustype AS J1031078 ON ( J1031078.id =
J1031074.idensuredstatustype)
    LEFT JOIN ensuredstatus AS J1031079 ON ( J1031079.id =
J1031074.idensuredstatus)
    LEFT JOIN org_units AS J1031080 ON ( J1031080.id =
J1031074.id_org_unit)
    LEFT JOIN personstate AS J1031081 ON ( J1031081.id =
J1031074.id_categorie_asigurat)
    LEFT JOIN focginterntype AS J1031082 ON ( J1031082.id =
J1031074.id_focg_tip_internare)
    LEFT JOIN focginterncrit AS J1031083 ON ( J1031083.id =
J1031074.id_focg_criteriu_internare)
    LEFT JOIN physicians AS J1031084 ON ( J1031084.id =
J1031074.idphysiciancurrent)
    LEFT JOIN personnel AS J1031085 ON ( J1031085.id =
J1031084.id_personnel)
    LEFT JOIN person AS J1031086 ON ( J1031086.id = J1031085.idperson)
    LEFT JOIN physicians AS J1031087 ON ( J1031087.id =
J1031074.idphysician_madeby)
    LEFT JOIN proffession AS J1031088 ON ( J1031088.id =
J1031074.idproffesion)
    LEFT JOIN educationlevel AS J1031089 ON ( J1031089.id =
J1031074.id_education_level)
    LEFT JOIN focg_situatii_speciale AS J1031090 ON ( J1031090.id =
J1031074.id_focg_situatii_speciale)
    LEFT JOIN focg_internat_prin AS J1031091 ON ( J1031091.id =
J1031074.id_focg_internat_prin)
    LEFT JOIN formulare_europene AS J1031092 ON ( J1031092.id =
J1031074.id_formulare_europene)
    LEFT JOIN focg AS J1031093 ON ( J1031093.id = J1031074.id_focg)
    LEFT JOIN person AS J1031094 ON ( J1031094.id = J1031074.id_cnp_mama)
    LEFT JOIN focg_sursa_internare AS J1031095 ON ( J1031095.id =
J1031074.id_focg_sursa_internare)
    LEFT JOIN diseasecategory AS J1031096 ON ( J1031096.id =
J1031074.id_diseasecategory)
    LEFT JOIN icd10 AS J1031097 ON ( J1031097.id = J1031074.id_icd10)
    LEFT JOIN focg_contract_modes AS J1031098 ON ( J1031098.id =
J1031074.id_focg_contract_modes)
    LEFT JOIN focg_criterii_urgenta AS J1031099 ON ( J1031099.id =
J1031074.id_focg_criterii_urgenta)
    LEFT JOIN exceptie_bilet_internare AS J1031100 ON ( J1031100.id =
J1031074.id_exceptie_bilet_internare)
    LEFT JOIN focg_tip_externare AS J1031736 ON ( J1031736.id =
J1031074.id_focg_tip_externare)
    LEFT JOIN focg_stare_externare AS J1031737 ON ( J1031737.id =
J1031074.id_focg_stare_externare)
    LEFT JOIN physicians AS J1033295 ON ( J1033295.id =
J1031074.idphysician_surgeon)
    LEFT JOIN hospital_release_statuses AS J1033299 ON ( J1033299.id =
J1031074.id_hospital_release_statuses)
    LEFT JOIN citizenship AS J1033304 ON ( J1033304.id =
J1031074.id_citizenship)
    LEFT JOIN drg_tip_cetatenie AS J1033358 ON ( J1033358.id =
J1031074.id_drg_tip_cetatenie)
    LEFT JOIN stari_spitalizari AS J1033704 ON ( J1033704.id =
J1031074.id_stari_spitalizari)

  WHERE   J1031102.id IS NULL   AND    J1033360.id IS NULL   AND    (
(   DATE(J1031101.validfrom)>= DATE('2016-05-01')   AND
DATE(J1031101.validfrom)<= DATE('2016-05-31 00:00:00.0')   )    OR    (
   J1033359.validto IS NOT NULL   AND   DATE(J1033359.validto)>=
DATE('2016-05-01')   AND   DATE(J1033359.validto)<= DATE('2016-05-31
00:00:00.0')   )    OR    (    J1033359.validto IS NULL   AND
DATE(J1031101.validfrom)<= DATE('2016-05-01')   )    )

    ORDER BY J1031101.validfrom DESC
  LIMIT  20 OFFSET 0


------ Original Message ------
From: "Sterpu Victor" <victor@caido.ro>
To: "David Rowley" <david.rowley@2ndquadrant.com>
Cc: "Rob Imig" <rimig88@gmail.com>; "PostgreSQL General"
<pgsql-general@postgresql.org>; "David G. Johnston"
<david.g.johnston@gmail.com>
Sent: 9/5/2016 10:44:45 AM
Subject: Re: [GENERAL] Slow query when the select list is big

>I solved the problem patialy by swithing the order of a join.
>I tested on a slower server to see better the difference.
>After moving a single join the query runs in 4.1 seconds insted 6.4
>seconds.
>I pasted the optimized query down. When I move the join J1033704 at the
>end(last join) the time is increased to 6.4 seconds.
>
>SELECT
>row_number() OVER (ORDER BY J1031101.validfrom DESC) AS nrcrt ,
>CASE WHEN(CHAR_LENGTH(J1031074.signature) > 10) THEN(1) ELSE(0) END AS
>semnat_internare ,
>CASE WHEN(CHAR_LENGTH(J1031074.signature_externare) > 10) THEN(1)
>ELSE(0) END AS semnat_externare ,
>J1031076.name AS sex ,
>J1031074.id AS id ,
>J1031074.siui_appid AS siui_appid ,
>J1031074.data_adeverinta AS data_adeverinta ,
>J1031074.is_paliativ AS text_paliativ ,
>J1031074.cardno AS cardno ,
>J1031074.cardno_externare AS cardno_externare ,
>J1031074.sign_date AS sign_date ,
>J1031074.sign_date_externare AS sign_date_externare ,
>J1031074.unsigned_string AS unsigned_string ,
>J1031074.unsigned_string_externare AS unsigned_string_externare ,
>J1031074.signhash AS signhash ,
>J1031074.signhash_externare AS signhash_externare ,
>J1031074.signature AS signature ,
>J1031074.signature_externare AS signature_externare ,
>J1031074.send_xml AS send_xml ,
>J1031074.send_xml_externare AS send_xml_externare ,
>J1031074.received_xml AS received_xml ,
>J1031074.received_xml_externare AS received_xml_externare ,
>J1031074.error AS error ,
>J1031074.error_externare AS error_externare ,
>J1031074.validat AS validat ,
>J1031074.validat_externare AS validat_externare ,
>J1031074.online AS online ,
>J1031074.online_externare AS online_externare ,
>J1031074.serie_bilet_internare AS text_serie_bilet_internare ,
>J1031074.nr_bilet_internare AS text_numar_bilet_internare ,
>J1031074.idpatient AS _popup_cnp_pacient ,
>J1031075.cnp AS popup_cnp_pacient ,
>J1031075.name AS text_nume_pacient ,
>J1031075.surname AS text_prenume_pacient ,
>J1031074.nrfo AS text_numar_fosz ,
>J1031074.greutate_nastere AS text_greutate_nastere ,
>J1031078.value AS popup_tip_asigurare ,
>J1031074.idensuredstatustype AS _popup_tip_asigurare ,
>J1031079.value AS popup_statut_asigurat ,
>J1031074.idensuredstatus AS _popup_statut_asigurat ,
>J1031080.code AS popup_cas_asigurat ,
>J1031074.id_org_unit AS _popup_cas_asigurat ,
>J1031081.code AS popup_categorie_asigurare ,
>J1031074.id_categorie_asigurat AS _popup_categorie_asigurare ,
>J1031082.name AS popup_tip_internare ,
>J1031074.id_focg_tip_internare AS _popup_tip_internare ,
>J1031083.name AS popup_criteriu_internare ,
>J1031074.id_focg_criteriu_internare AS _popup_criteriu_internare ,
>J1031084.stencil_no AS popup_medic_curant ,
>J1031084.id AS _popup_medic_curant ,
>J1031089.value AS popup_nivel_instruire ,
>J1031074.id_education_level AS _popup_nivel_instruire ,
>J1031074.greutate AS text_greutate_internare ,
>J1031090.nume AS popup_situatii_speciale ,
>J1031074.id_focg_situatii_speciale AS _popup_situatii_speciale ,
>J1031091.nume AS popup_internat_prin ,
>J1031091.id AS _popup_internat_prin ,
>J1031092.nume AS popup_formulare_europene ,
>J1031074.id_formulare_europene AS _popup_formulare_europene ,
>J1031074.id_cnp_mama AS _popup_cnp_mama ,
>J1031094.cnp AS popup_cnp_mama ,
>J1031093.nrfo AS popup_fo_mama ,
>J1031074.id_focg AS _popup_fo_mama ,
>J1031074.nr_card_euro AS text_nr_card_european ,
>J1031074.nr_pasaport AS text_nr_pasaport ,
>J1031074.nr_card_national AS text_nr_card_national ,
>J1031088.id AS _popup_ocupatia ,
>J1031088.name AS popup_ocupatia ,
>J1031074.export_drg AS export_drg ,
>J1031074.drgcaseid AS drgcaseid ,
>J1031074.export_ecosoft AS export_ecosoft ,
>J1031074.mesaj_drg AS mesaj_drg ,
>J1031074.uid AS uid ,
>J1031074.mesaj_ecosoft AS mesaj_ecosoft ,
>J1031074.id_address_domiciliu AS text_id_address_domiciliu ,
>J1031074.id_address_domiciliu AS _text_id_address_domiciliu ,
>J1031074.id_address_resedinta AS _text_id_address_resedinta ,
>J1031074.id_address_resedinta AS text_id_address_resedinta ,
>'0' AS marcator ,
>J1031095.id AS _popup_sursa_internare ,
>J1031095.denumire AS popup_sursa_internare ,
>J1031096.id AS _popup_diseasecategory ,
>J1031096.code AS popup_diseasecategory ,
>J1031097.id AS _popup_diagnostic_internare_icd10 ,
>J1031097.name AS popup_diagnostic_internare_icd10 ,
>J1031098.id AS _popup_mod_contract ,
>J1031098.description AS popup_mod_contract ,
>J1031099.id AS _popup_criteriu_urgenta ,
>J1031099.name AS popup_criteriu_urgenta ,
>J1031100.id AS _popup_exceptie_bi ,
>J1031100.code AS popup_exceptie_bi ,
>J1031074.scrisoare_medicala_parafa AS text_parafa_scrisoare_medicala ,
>J1031074.scrisoare_medicala_contract AS
>text_contract_scrisoare_medicala ,
>J1031074.scrisoare_medicala_tip_contract AS
>text_tip_contract_scrisoare_medicala ,
>J1031074.export_siui AS export_siui ,
>J1031074.mesaj_siui AS mesaj_siui ,
>J1031087.id AS _popup_intocmit ,
>J1031087.stencil_no AS popup_intocmit ,
>J1031074.diagnostic_trimitere_text AS text_diagnostic_trimitere ,
>J1031074.greutate_externare AS text_greutate_externare ,
>J1031074.data_decesului AS text_data_deces ,
>J1031736.id AS _popup_tip_externare ,
>J1031736.descriere AS popup_tip_externare ,
>J1031737.id AS _popup_stare_externare ,
>J1031737.descriere AS popup_stare_externare ,
>J1033295.id AS _popup_chirurg ,
>J1033295.stencil_no AS popup_chirurg ,
>J1033299.id AS _text_caz_externare ,
>J1033299.description AS text_caz_externare ,
>J1031074.text_ore_ventilatie AS text_ore_ventilatie ,
>J1031074.drg_cod_grupa AS text_drg_cod_grupa ,
>J1031074.drg_relative_value AS text_drg_relative_value ,
>J1031074.data_2500g AS text_data_2500g ,
>J1031074.prematur_gr_i AS text_prematur_gr_i ,
>J1033304.id AS _popup_cetatenie ,
>J1033304.description AS popup_cetatenie ,
>J1033358.id AS _popup_tip_cetatenie ,
>J1033358.name AS popup_tip_cetatenie ,
>J1031074.reinternat_transfer AS text_reinternat_transfer ,
>J1031074.aviz_comisie AS text_aviz_comisie_reinternare ,
>J1031074.criteriu_urgenta1 AS criteriu_urgenta1 ,
>J1031074.criteriu_urgenta2 AS criteriu_urgenta2 ,
>J1031074.criteriu_urgenta3 AS criteriu_urgenta3 ,
>J1031074.criteriu_urgenta4 AS criteriu_urgenta4 ,
>J1031074.criteriu_urgenta5 AS criteriu_urgenta5 ,
>J1031074.criteriu_urgenta6 AS criteriu_urgenta6 ,
>J1031074.criteriu_urgenta7 AS criteriu_urgenta7 ,
>J1031074.criteriu_urgenta8 AS criteriu_urgenta8 ,
>J1031074.criteriu_urgenta9a AS criteriu_urgenta9a ,
>J1031074.criteriu_urgenta9b AS criteriu_urgenta9b ,
>J1031074.criteriu_urgenta10 AS criteriu_urgenta10 ,
>J1031074.criteriu_urgenta11 AS criteriu_urgenta11 ,
>J1031074.criteriu_urgenta12 AS criteriu_urgenta12 ,
>J1031074.criteriu_urgenta13 AS criteriu_urgenta13 ,
>J1031074.infectie_nosocomiala AS infectie_nosocomiala ,
>J1031074.cu_programare AS cu_programare ,
>J1031074.stare_critica_internare AS stare_critica_internare ,
>J1033704.id AS _id_stari_spitalizari ,
>J1033704.nume AS id_stari_spitalizari
>    FROM focg AS J1031074
>   LEFT JOIN person AS J1031075 ON ( J1031075.id = J1031074.idpatient)
>    LEFT JOIN sex AS J1031076 ON ( J1031076.id = J1031075.idsex)
>    LEFT JOIN focg_serials AS J1031077 ON ( J1031077.id =
>J1031074.id_focg_serials)
>    LEFT JOIN ensuredstatustype AS J1031078 ON ( J1031078.id =
>J1031074.idensuredstatustype)
>    LEFT JOIN ensuredstatus AS J1031079 ON ( J1031079.id =
>J1031074.idensuredstatus)
>    LEFT JOIN org_units AS J1031080 ON ( J1031080.id =
>J1031074.id_org_unit)
>    LEFT JOIN personstate AS J1031081 ON ( J1031081.id =
>J1031074.id_categorie_asigurat)
>    LEFT JOIN focginterntype AS J1031082 ON ( J1031082.id =
>J1031074.id_focg_tip_internare)
>    LEFT JOIN focginterncrit AS J1031083 ON ( J1031083.id =
>J1031074.id_focg_criteriu_internare)
>    LEFT JOIN physicians AS J1031084 ON ( J1031084.id =
>J1031074.idphysiciancurrent)
>    LEFT JOIN personnel AS J1031085 ON ( J1031085.id =
>J1031084.id_personnel)
>    LEFT JOIN person AS J1031086 ON ( J1031086.id = J1031085.idperson)
>    LEFT JOIN physicians AS J1031087 ON ( J1031087.id =
>J1031074.idphysician_madeby)
>    LEFT JOIN proffession AS J1031088 ON ( J1031088.id =
>J1031074.idproffesion)
>    LEFT JOIN educationlevel AS J1031089 ON ( J1031089.id =
>J1031074.id_education_level)
>    LEFT JOIN focg_situatii_speciale AS J1031090 ON ( J1031090.id =
>J1031074.id_focg_situatii_speciale)
>    LEFT JOIN focg_internat_prin AS J1031091 ON ( J1031091.id =
>J1031074.id_focg_internat_prin)
>    LEFT JOIN formulare_europene AS J1031092 ON ( J1031092.id =
>J1031074.id_formulare_europene)
>    LEFT JOIN focg AS J1031093 ON ( J1031093.id = J1031074.id_focg)
>    LEFT JOIN person AS J1031094 ON ( J1031094.id =
>J1031074.id_cnp_mama)
>    LEFT JOIN focg_sursa_internare AS J1031095 ON ( J1031095.id =
>J1031074.id_focg_sursa_internare)
>    LEFT JOIN diseasecategory AS J1031096 ON ( J1031096.id =
>J1031074.id_diseasecategory)
>    LEFT JOIN icd10 AS J1031097 ON ( J1031097.id = J1031074.id_icd10)
>    LEFT JOIN focg_contract_modes AS J1031098 ON ( J1031098.id =
>J1031074.id_focg_contract_modes)
>    LEFT JOIN focg_criterii_urgenta AS J1031099 ON ( J1031099.id =
>J1031074.id_focg_criterii_urgenta)
>    LEFT JOIN exceptie_bilet_internare AS J1031100 ON ( J1031100.id =
>J1031074.id_exceptie_bilet_internare)
>    LEFT JOIN focg_tip_externare AS J1031736 ON ( J1031736.id =
>J1031074.id_focg_tip_externare)
>    LEFT JOIN focg_stare_externare AS J1031737 ON ( J1031737.id =
>J1031074.id_focg_stare_externare)
>    LEFT JOIN physicians AS J1033295 ON ( J1033295.id =
>J1031074.idphysician_surgeon)
>    LEFT JOIN hospital_release_statuses AS J1033299 ON ( J1033299.id =
>J1031074.id_hospital_release_statuses)
>    LEFT JOIN citizenship AS J1033304 ON ( J1033304.id =
>J1031074.id_citizenship)
>    LEFT JOIN drg_tip_cetatenie AS J1033358 ON ( J1033358.id =
>J1031074.id_drg_tip_cetatenie)
>    LEFT JOIN stari_spitalizari AS J1033704 ON ( J1033704.id =
>J1031074.id_stari_spitalizari)
>    LEFT JOIN focgdepartment AS J1031101 ON ( J1031101.idfocg =
>J1031074.id)
>    LEFT JOIN department AS J1033386 ON ( J1033386.id =
>J1031101.iddepartment)
>    LEFT JOIN focgdepartment AS J1031102 ON ( J1031102.idfocg =
>J1031074.id AND J1031102.validfrom < J1031101.validfrom)
>    LEFT JOIN focgdepartment AS J1033359 ON ( J1033359.idfocg =
>J1031074.id)
>    LEFT JOIN department AS J1033387 ON ( J1033387.id =
>J1033359.iddepartment)
>    LEFT JOIN focgdepartment AS J1033360 ON ( J1033360.idfocg =
>J1031074.id AND J1033360.validfrom > J1033359.validfrom)
>     JOIN focgdepartment AS J1033407 ON ( J1033407.idfocg = J1031074.id
>AND J1033407.iddepartment IN (/*Executing QueryID 476590*/SELECT
>DISTINCT(J1034477.id) AS ret FROM department AS J1034477 JOIN
>personnel_department AS J1034478 ON ( J1034478.id_department =
>J1034477.id)
>   JOIN personnel AS J1034479 ON ( J1034479.id = J1034478.id_personnel
>AND J1034479.id_connected_user = '1')))
>
>  WHERE J1031102.id IS NULL AND J1033360.id IS NULL AND ( (
>DATE(J1031101.validfrom)>= DATE('2016-05-01') AND
>DATE(J1031101.validfrom)<= DATE('2016-05-31 00:00:00.0') ) OR (
>J1033359.validto IS NOT NULL AND DATE(J1033359.validto)>=
>DATE('2016-05-01') AND DATE(J1033359.validto)<= DATE('2016-05-31
>00:00:00.0') ) OR ( J1033359.validto IS NULL AND
>DATE(J1031101.validfrom)<= DATE('2016-05-01') ) )
>
>    ORDER BY J1031101.validfrom DESC
>  LIMIT 20 OFFSET 0
>
>------ Original Message ------
>From: "David Rowley" <david.rowley@2ndquadrant.com>
>To: "Sterpu Victor" <victor@caido.ro>
>Cc: "Rob Imig" <rimig88@gmail.com>; "PostgreSQL General"
><pgsql-general@postgresql.org>; "David G. Johnston"
><david.g.johnston@gmail.com>
>Sent: 9/5/2016 10:04:54 AM
>Subject: Re: [GENERAL] Slow query when the select list is big
>
>>On 9 May 2016 at 18:46, David G. Johnston <david.g.johnston@gmail.com>
>>wrote:
>>>  On Sunday, May 8, 2016, Sterpu Victor <victor@caido.ro> wrote:
>>>>
>>>>  Yes but it is very big.
>>>>  I don't understand why the select list is influencing the CPU
>>>>usage.
>>>>  I was expecting that only the join and where clauses would
>>>>influence CPU.
>>>>
>>>
>>>  PostgreSQL is smart enough to optimize away stuff that it knows
>>>doesn't
>>>  impact the final query result.
>>
>>To be more accurate with what David is saying, PostgreSQL will remove
>>unused LEFT JOINed relations where the left joined relation can be
>>proved to not duplicate rows from the right hand side. It would just
>>be a matter of comparing the EXPLAINs from the query with all the
>>SELECT items to the one with the single SELECT item to prove that this
>>is what's happening.
>>
>>Please also note that this only occurs with LEFT JOINs
>>
>>It would also be quite helpful for people if you were to include a
>>copy of the query. It's impossible to reverse engineer what that is
>>from this EXPLAIN output. I see that your using a windowing function
>>and performing a LIMIT 1, there may be ways to improve that just by
>>selecting the single highest j1031101.validfrom row and performing the
>>joins to the other table on that single row, but that will depend on
>>which windowing function you're using as the function may require the
>>other rows in the window frame to calculate the correct result.
>>
>>--
>>  David Rowley http://www.2ndQuadrant.com/
>>  PostgreSQL Development, 24x7 Support, Training & Services
>
>
>
>-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general

Acctualy the optimization should be cauzed by the filters on the joins
that have been moved at the begining of the query.
So now postres is making a filter before joining a lot of data.
The fact that these join produce multiple rows is not relevant.
At least this is what I think is heapening.

------ Original Message ------
From: "Sterpu Victor" <victor@caido.ro>
To: "Sterpu Victor" <victor@caido.ro>; "David Rowley"
<david.rowley@2ndquadrant.com>
Cc: "Rob Imig" <rimig88@gmail.com>; "PostgreSQL General"
<pgsql-general@postgresql.org>; "David G. Johnston"
<david.g.johnston@gmail.com>
Sent: 9/5/2016 11:01:56 AM
Subject: Re[2]: [GENERAL] Slow query when the select list is big

>I went to 2.4 seconds by joining first the tables that produce many
>rows.
>
>SELECT
>row_number() OVER (ORDER BY J1031101.validfrom DESC) AS nrcrt ,
>J1033386.name AS sectie_internare ,
>J1033387.name AS sectie_externare ,
>TO_CHAR(J1031101.validfrom , 'YYYY-MM-DD HH24:MI') AS validfrom ,
>TO_CHAR(J1033359.validto , 'YYYY-MM-DD HH24:MI') AS validto ,
>CASE WHEN(CHAR_LENGTH(J1031074.signature) > 10) THEN(1) ELSE(0) END AS
>semnat_internare ,
>CASE WHEN(CHAR_LENGTH(J1031074.signature_externare) > 10) THEN(1)
>ELSE(0) END AS semnat_externare ,
>J1031076.name AS sex ,
>J1031074.id AS id ,
>J1031074.siui_appid AS siui_appid ,
>J1031074.data_adeverinta AS data_adeverinta ,
>J1031074.is_paliativ AS text_paliativ ,
>J1031074.cardno AS cardno ,
>J1031074.cardno_externare AS cardno_externare ,
>J1031074.sign_date AS sign_date ,
>J1031074.sign_date_externare AS sign_date_externare ,
>J1031074.unsigned_string AS unsigned_string ,
>J1031074.unsigned_string_externare AS unsigned_string_externare ,
>J1031074.signhash AS signhash ,
>J1031074.signhash_externare AS signhash_externare ,
>J1031074.signature AS signature ,
>J1031074.signature_externare AS signature_externare ,
>J1031074.send_xml AS send_xml ,
>J1031074.send_xml_externare AS send_xml_externare ,
>J1031074.received_xml AS received_xml ,
>J1031074.received_xml_externare AS received_xml_externare ,
>J1031074.error AS error ,
>J1031074.error_externare AS error_externare ,
>J1031074.validat AS validat ,
>J1031074.validat_externare AS validat_externare ,
>J1031074.online AS online ,
>J1031074.online_externare AS online_externare ,
>J1031074.serie_bilet_internare AS text_serie_bilet_internare ,
>J1031074.nr_bilet_internare AS text_numar_bilet_internare ,
>J1031074.idpatient AS _popup_cnp_pacient ,
>J1031075.cnp AS popup_cnp_pacient ,
>J1031075.name AS text_nume_pacient ,
>J1031075.surname AS text_prenume_pacient ,
>J1031074.nrfo AS text_numar_fosz ,
>J1031074.greutate_nastere AS text_greutate_nastere ,
>J1031078.value AS popup_tip_asigurare ,
>J1031074.idensuredstatustype AS _popup_tip_asigurare ,
>J1031079.value AS popup_statut_asigurat ,
>J1031074.idensuredstatus AS _popup_statut_asigurat ,
>J1031080.code AS popup_cas_asigurat ,
>J1031074.id_org_unit AS _popup_cas_asigurat ,
>J1031081.code AS popup_categorie_asigurare ,
>J1031074.id_categorie_asigurat AS _popup_categorie_asigurare ,
>J1031082.name AS popup_tip_internare ,
>J1031074.id_focg_tip_internare AS _popup_tip_internare ,
>J1031083.name AS popup_criteriu_internare ,
>J1031074.id_focg_criteriu_internare AS _popup_criteriu_internare ,
>J1031084.stencil_no AS popup_medic_curant ,
>J1031084.id AS _popup_medic_curant ,
>J1031089.value AS popup_nivel_instruire ,
>J1031074.id_education_level AS _popup_nivel_instruire ,
>J1031074.greutate AS text_greutate_internare ,
>J1031090.nume AS popup_situatii_speciale ,
>J1031074.id_focg_situatii_speciale AS _popup_situatii_speciale ,
>J1031091.nume AS popup_internat_prin ,
>J1031091.id AS _popup_internat_prin ,
>J1031092.nume AS popup_formulare_europene ,
>J1031074.id_formulare_europene AS _popup_formulare_europene ,
>J1031074.id_cnp_mama AS _popup_cnp_mama ,
>J1031094.cnp AS popup_cnp_mama ,
>J1031093.nrfo AS popup_fo_mama ,
>J1031074.id_focg AS _popup_fo_mama ,
>J1031074.nr_card_euro AS text_nr_card_european ,
>J1031074.nr_pasaport AS text_nr_pasaport ,
>J1031074.nr_card_national AS text_nr_card_national ,
>J1031088.id AS _popup_ocupatia ,
>J1031088.name AS popup_ocupatia ,
>J1031074.export_drg AS export_drg ,
>J1031074.drgcaseid AS drgcaseid ,
>J1031074.export_ecosoft AS export_ecosoft ,
>J1031074.mesaj_drg AS mesaj_drg ,
>J1031074.uid AS uid ,
>J1031074.mesaj_ecosoft AS mesaj_ecosoft ,
>J1031074.id_address_domiciliu AS text_id_address_domiciliu ,
>J1031074.id_address_domiciliu AS _text_id_address_domiciliu ,
>J1031074.id_address_resedinta AS _text_id_address_resedinta ,
>J1031074.id_address_resedinta AS text_id_address_resedinta ,
>'0' AS marcator ,
>J1031095.id AS _popup_sursa_internare ,
>J1031095.denumire AS popup_sursa_internare ,
>J1031096.id AS _popup_diseasecategory ,
>J1031096.code AS popup_diseasecategory ,
>J1031097.id AS _popup_diagnostic_internare_icd10 ,
>J1031097.name AS popup_diagnostic_internare_icd10 ,
>J1031098.id AS _popup_mod_contract ,
>J1031098.description AS popup_mod_contract ,
>J1031099.id AS _popup_criteriu_urgenta ,
>J1031099.name AS popup_criteriu_urgenta ,
>J1031100.id AS _popup_exceptie_bi ,
>J1031100.code AS popup_exceptie_bi ,
>J1031074.scrisoare_medicala_parafa AS text_parafa_scrisoare_medicala ,
>J1031074.scrisoare_medicala_contract AS
>text_contract_scrisoare_medicala ,
>J1031074.scrisoare_medicala_tip_contract AS
>text_tip_contract_scrisoare_medicala ,
>J1031074.export_siui AS export_siui ,
>J1031074.mesaj_siui AS mesaj_siui ,
>J1031087.id AS _popup_intocmit ,
>J1031087.stencil_no AS popup_intocmit ,
>J1031074.diagnostic_trimitere_text AS text_diagnostic_trimitere ,
>J1031074.greutate_externare AS text_greutate_externare ,
>J1031074.data_decesului AS text_data_deces ,
>J1031736.id AS _popup_tip_externare ,
>J1031736.descriere AS popup_tip_externare ,
>J1031737.id AS _popup_stare_externare ,
>J1031737.descriere AS popup_stare_externare ,
>J1033295.id AS _popup_chirurg ,
>J1033295.stencil_no AS popup_chirurg ,
>J1033299.id AS _text_caz_externare ,
>J1033299.description AS text_caz_externare ,
>J1031074.text_ore_ventilatie AS text_ore_ventilatie ,
>J1031074.drg_cod_grupa AS text_drg_cod_grupa ,
>J1031074.drg_relative_value AS text_drg_relative_value ,
>J1031074.data_2500g AS text_data_2500g ,
>J1031074.prematur_gr_i AS text_prematur_gr_i ,
>J1033304.id AS _popup_cetatenie ,
>J1033304.description AS popup_cetatenie ,
>J1033358.id AS _popup_tip_cetatenie ,
>J1033358.name AS popup_tip_cetatenie ,
>J1031074.reinternat_transfer AS text_reinternat_transfer ,
>J1031074.aviz_comisie AS text_aviz_comisie_reinternare ,
>J1031074.criteriu_urgenta1 AS criteriu_urgenta1 ,
>J1031074.criteriu_urgenta2 AS criteriu_urgenta2 ,
>J1031074.criteriu_urgenta3 AS criteriu_urgenta3 ,
>J1031074.criteriu_urgenta4 AS criteriu_urgenta4 ,
>J1031074.criteriu_urgenta5 AS criteriu_urgenta5 ,
>J1031074.criteriu_urgenta6 AS criteriu_urgenta6 ,
>J1031074.criteriu_urgenta7 AS criteriu_urgenta7 ,
>J1031074.criteriu_urgenta8 AS criteriu_urgenta8 ,
>J1031074.criteriu_urgenta9a AS criteriu_urgenta9a ,
>J1031074.criteriu_urgenta9b AS criteriu_urgenta9b ,
>J1031074.criteriu_urgenta10 AS criteriu_urgenta10 ,
>J1031074.criteriu_urgenta11 AS criteriu_urgenta11 ,
>J1031074.criteriu_urgenta12 AS criteriu_urgenta12 ,
>J1031074.criteriu_urgenta13 AS criteriu_urgenta13 ,
>J1031074.infectie_nosocomiala AS infectie_nosocomiala ,
>J1031074.cu_programare AS cu_programare ,
>J1031074.stare_critica_internare AS stare_critica_internare ,
>J1033704.id AS _id_stari_spitalizari ,
>J1033704.nume AS id_stari_spitalizari
>    FROM focg AS J1031074
>    LEFT JOIN focgdepartment AS J1031101 ON ( J1031101.idfocg =
>J1031074.id)
>    LEFT JOIN department AS J1033386 ON ( J1033386.id =
>J1031101.iddepartment)
>    LEFT JOIN focgdepartment AS J1031102 ON ( J1031102.idfocg =
>J1031074.id AND J1031102.validfrom < J1031101.validfrom)
>    LEFT JOIN focgdepartment AS J1033359 ON ( J1033359.idfocg =
>J1031074.id)
>    LEFT JOIN department AS J1033387 ON ( J1033387.id =
>J1033359.iddepartment)
>    LEFT JOIN focgdepartment AS J1033360 ON ( J1033360.idfocg =
>J1031074.id AND J1033360.validfrom > J1033359.validfrom)
>    JOIN focgdepartment AS J1033407 ON ( J1033407.idfocg = J1031074.id
>AND J1033407.iddepartment IN (/*Executing QueryID 476590*/SELECT
>DISTINCT(J1034477.id) AS ret FROM department AS J1034477 JOIN
>personnel_department AS J1034478 ON ( J1034478.id_department =
>J1034477.id) JOIN personnel AS J1034479 ON ( J1034479.id =
>J1034478.id_personnel AND J1034479.id_connected_user = '1')))
>
>    LEFT JOIN person AS J1031075 ON ( J1031075.id = J1031074.idpatient)
>    LEFT JOIN sex AS J1031076 ON ( J1031076.id = J1031075.idsex)
>    LEFT JOIN focg_serials AS J1031077 ON ( J1031077.id =
>J1031074.id_focg_serials)
>    LEFT JOIN ensuredstatustype AS J1031078 ON ( J1031078.id =
>J1031074.idensuredstatustype)
>    LEFT JOIN ensuredstatus AS J1031079 ON ( J1031079.id =
>J1031074.idensuredstatus)
>    LEFT JOIN org_units AS J1031080 ON ( J1031080.id =
>J1031074.id_org_unit)
>    LEFT JOIN personstate AS J1031081 ON ( J1031081.id =
>J1031074.id_categorie_asigurat)
>    LEFT JOIN focginterntype AS J1031082 ON ( J1031082.id =
>J1031074.id_focg_tip_internare)
>    LEFT JOIN focginterncrit AS J1031083 ON ( J1031083.id =
>J1031074.id_focg_criteriu_internare)
>    LEFT JOIN physicians AS J1031084 ON ( J1031084.id =
>J1031074.idphysiciancurrent)
>    LEFT JOIN personnel AS J1031085 ON ( J1031085.id =
>J1031084.id_personnel)
>    LEFT JOIN person AS J1031086 ON ( J1031086.id = J1031085.idperson)
>    LEFT JOIN physicians AS J1031087 ON ( J1031087.id =
>J1031074.idphysician_madeby)
>    LEFT JOIN proffession AS J1031088 ON ( J1031088.id =
>J1031074.idproffesion)
>    LEFT JOIN educationlevel AS J1031089 ON ( J1031089.id =
>J1031074.id_education_level)
>    LEFT JOIN focg_situatii_speciale AS J1031090 ON ( J1031090.id =
>J1031074.id_focg_situatii_speciale)
>    LEFT JOIN focg_internat_prin AS J1031091 ON ( J1031091.id =
>J1031074.id_focg_internat_prin)
>    LEFT JOIN formulare_europene AS J1031092 ON ( J1031092.id =
>J1031074.id_formulare_europene)
>    LEFT JOIN focg AS J1031093 ON ( J1031093.id = J1031074.id_focg)
>    LEFT JOIN person AS J1031094 ON ( J1031094.id =
>J1031074.id_cnp_mama)
>    LEFT JOIN focg_sursa_internare AS J1031095 ON ( J1031095.id =
>J1031074.id_focg_sursa_internare)
>    LEFT JOIN diseasecategory AS J1031096 ON ( J1031096.id =
>J1031074.id_diseasecategory)
>    LEFT JOIN icd10 AS J1031097 ON ( J1031097.id = J1031074.id_icd10)
>    LEFT JOIN focg_contract_modes AS J1031098 ON ( J1031098.id =
>J1031074.id_focg_contract_modes)
>    LEFT JOIN focg_criterii_urgenta AS J1031099 ON ( J1031099.id =
>J1031074.id_focg_criterii_urgenta)
>    LEFT JOIN exceptie_bilet_internare AS J1031100 ON ( J1031100.id =
>J1031074.id_exceptie_bilet_internare)
>    LEFT JOIN focg_tip_externare AS J1031736 ON ( J1031736.id =
>J1031074.id_focg_tip_externare)
>    LEFT JOIN focg_stare_externare AS J1031737 ON ( J1031737.id =
>J1031074.id_focg_stare_externare)
>    LEFT JOIN physicians AS J1033295 ON ( J1033295.id =
>J1031074.idphysician_surgeon)
>    LEFT JOIN hospital_release_statuses AS J1033299 ON ( J1033299.id =
>J1031074.id_hospital_release_statuses)
>    LEFT JOIN citizenship AS J1033304 ON ( J1033304.id =
>J1031074.id_citizenship)
>    LEFT JOIN drg_tip_cetatenie AS J1033358 ON ( J1033358.id =
>J1031074.id_drg_tip_cetatenie)
>    LEFT JOIN stari_spitalizari AS J1033704 ON ( J1033704.id =
>J1031074.id_stari_spitalizari)
>
>  WHERE J1031102.id IS NULL AND J1033360.id IS NULL AND ( (
>DATE(J1031101.validfrom)>= DATE('2016-05-01') AND
>DATE(J1031101.validfrom)<= DATE('2016-05-31 00:00:00.0') ) OR (
>J1033359.validto IS NOT NULL AND DATE(J1033359.validto)>=
>DATE('2016-05-01') AND DATE(J1033359.validto)<= DATE('2016-05-31
>00:00:00.0') ) OR ( J1033359.validto IS NULL AND
>DATE(J1031101.validfrom)<= DATE('2016-05-01') ) )
>
>    ORDER BY J1031101.validfrom DESC
>  LIMIT 20 OFFSET 0
>
>
>------ Original Message ------
>From: "Sterpu Victor" <victor@caido.ro>
>To: "David Rowley" <david.rowley@2ndquadrant.com>
>Cc: "Rob Imig" <rimig88@gmail.com>; "PostgreSQL General"
><pgsql-general@postgresql.org>; "David G. Johnston"
><david.g.johnston@gmail.com>
>Sent: 9/5/2016 10:44:45 AM
>Subject: Re: [GENERAL] Slow query when the select list is big
>
>>I solved the problem patialy by swithing the order of a join.
>>I tested on a slower server to see better the difference.
>>After moving a single join the query runs in 4.1 seconds insted 6.4
>>seconds.
>>I pasted the optimized query down. When I move the join J1033704 at
>>the end(last join) the time is increased to 6.4 seconds.
>>
>>SELECT
>>row_number() OVER (ORDER BY J1031101.validfrom DESC) AS nrcrt ,
>>CASE WHEN(CHAR_LENGTH(J1031074.signature) > 10) THEN(1) ELSE(0) END AS
>>semnat_internare ,
>>CASE WHEN(CHAR_LENGTH(J1031074.signature_externare) > 10) THEN(1)
>>ELSE(0) END AS semnat_externare ,
>>J1031076.name AS sex ,
>>J1031074.id AS id ,
>>J1031074.siui_appid AS siui_appid ,
>>J1031074.data_adeverinta AS data_adeverinta ,
>>J1031074.is_paliativ AS text_paliativ ,
>>J1031074.cardno AS cardno ,
>>J1031074.cardno_externare AS cardno_externare ,
>>J1031074.sign_date AS sign_date ,
>>J1031074.sign_date_externare AS sign_date_externare ,
>>J1031074.unsigned_string AS unsigned_string ,
>>J1031074.unsigned_string_externare AS unsigned_string_externare ,
>>J1031074.signhash AS signhash ,
>>J1031074.signhash_externare AS signhash_externare ,
>>J1031074.signature AS signature ,
>>J1031074.signature_externare AS signature_externare ,
>>J1031074.send_xml AS send_xml ,
>>J1031074.send_xml_externare AS send_xml_externare ,
>>J1031074.received_xml AS received_xml ,
>>J1031074.received_xml_externare AS received_xml_externare ,
>>J1031074.error AS error ,
>>J1031074.error_externare AS error_externare ,
>>J1031074.validat AS validat ,
>>J1031074.validat_externare AS validat_externare ,
>>J1031074.online AS online ,
>>J1031074.online_externare AS online_externare ,
>>J1031074.serie_bilet_internare AS text_serie_bilet_internare ,
>>J1031074.nr_bilet_internare AS text_numar_bilet_internare ,
>>J1031074.idpatient AS _popup_cnp_pacient ,
>>J1031075.cnp AS popup_cnp_pacient ,
>>J1031075.name AS text_nume_pacient ,
>>J1031075.surname AS text_prenume_pacient ,
>>J1031074.nrfo AS text_numar_fosz ,
>>J1031074.greutate_nastere AS text_greutate_nastere ,
>>J1031078.value AS popup_tip_asigurare ,
>>J1031074.idensuredstatustype AS _popup_tip_asigurare ,
>>J1031079.value AS popup_statut_asigurat ,
>>J1031074.idensuredstatus AS _popup_statut_asigurat ,
>>J1031080.code AS popup_cas_asigurat ,
>>J1031074.id_org_unit AS _popup_cas_asigurat ,
>>J1031081.code AS popup_categorie_asigurare ,
>>J1031074.id_categorie_asigurat AS _popup_categorie_asigurare ,
>>J1031082.name AS popup_tip_internare ,
>>J1031074.id_focg_tip_internare AS _popup_tip_internare ,
>>J1031083.name AS popup_criteriu_internare ,
>>J1031074.id_focg_criteriu_internare AS _popup_criteriu_internare ,
>>J1031084.stencil_no AS popup_medic_curant ,
>>J1031084.id AS _popup_medic_curant ,
>>J1031089.value AS popup_nivel_instruire ,
>>J1031074.id_education_level AS _popup_nivel_instruire ,
>>J1031074.greutate AS text_greutate_internare ,
>>J1031090.nume AS popup_situatii_speciale ,
>>J1031074.id_focg_situatii_speciale AS _popup_situatii_speciale ,
>>J1031091.nume AS popup_internat_prin ,
>>J1031091.id AS _popup_internat_prin ,
>>J1031092.nume AS popup_formulare_europene ,
>>J1031074.id_formulare_europene AS _popup_formulare_europene ,
>>J1031074.id_cnp_mama AS _popup_cnp_mama ,
>>J1031094.cnp AS popup_cnp_mama ,
>>J1031093.nrfo AS popup_fo_mama ,
>>J1031074.id_focg AS _popup_fo_mama ,
>>J1031074.nr_card_euro AS text_nr_card_european ,
>>J1031074.nr_pasaport AS text_nr_pasaport ,
>>J1031074.nr_card_national AS text_nr_card_national ,
>>J1031088.id AS _popup_ocupatia ,
>>J1031088.name AS popup_ocupatia ,
>>J1031074.export_drg AS export_drg ,
>>J1031074.drgcaseid AS drgcaseid ,
>>J1031074.export_ecosoft AS export_ecosoft ,
>>J1031074.mesaj_drg AS mesaj_drg ,
>>J1031074.uid AS uid ,
>>J1031074.mesaj_ecosoft AS mesaj_ecosoft ,
>>J1031074.id_address_domiciliu AS text_id_address_domiciliu ,
>>J1031074.id_address_domiciliu AS _text_id_address_domiciliu ,
>>J1031074.id_address_resedinta AS _text_id_address_resedinta ,
>>J1031074.id_address_resedinta AS text_id_address_resedinta ,
>>'0' AS marcator ,
>>J1031095.id AS _popup_sursa_internare ,
>>J1031095.denumire AS popup_sursa_internare ,
>>J1031096.id AS _popup_diseasecategory ,
>>J1031096.code AS popup_diseasecategory ,
>>J1031097.id AS _popup_diagnostic_internare_icd10 ,
>>J1031097.name AS popup_diagnostic_internare_icd10 ,
>>J1031098.id AS _popup_mod_contract ,
>>J1031098.description AS popup_mod_contract ,
>>J1031099.id AS _popup_criteriu_urgenta ,
>>J1031099.name AS popup_criteriu_urgenta ,
>>J1031100.id AS _popup_exceptie_bi ,
>>J1031100.code AS popup_exceptie_bi ,
>>J1031074.scrisoare_medicala_parafa AS text_parafa_scrisoare_medicala ,
>>J1031074.scrisoare_medicala_contract AS
>>text_contract_scrisoare_medicala ,
>>J1031074.scrisoare_medicala_tip_contract AS
>>text_tip_contract_scrisoare_medicala ,
>>J1031074.export_siui AS export_siui ,
>>J1031074.mesaj_siui AS mesaj_siui ,
>>J1031087.id AS _popup_intocmit ,
>>J1031087.stencil_no AS popup_intocmit ,
>>J1031074.diagnostic_trimitere_text AS text_diagnostic_trimitere ,
>>J1031074.greutate_externare AS text_greutate_externare ,
>>J1031074.data_decesului AS text_data_deces ,
>>J1031736.id AS _popup_tip_externare ,
>>J1031736.descriere AS popup_tip_externare ,
>>J1031737.id AS _popup_stare_externare ,
>>J1031737.descriere AS popup_stare_externare ,
>>J1033295.id AS _popup_chirurg ,
>>J1033295.stencil_no AS popup_chirurg ,
>>J1033299.id AS _text_caz_externare ,
>>J1033299.description AS text_caz_externare ,
>>J1031074.text_ore_ventilatie AS text_ore_ventilatie ,
>>J1031074.drg_cod_grupa AS text_drg_cod_grupa ,
>>J1031074.drg_relative_value AS text_drg_relative_value ,
>>J1031074.data_2500g AS text_data_2500g ,
>>J1031074.prematur_gr_i AS text_prematur_gr_i ,
>>J1033304.id AS _popup_cetatenie ,
>>J1033304.description AS popup_cetatenie ,
>>J1033358.id AS _popup_tip_cetatenie ,
>>J1033358.name AS popup_tip_cetatenie ,
>>J1031074.reinternat_transfer AS text_reinternat_transfer ,
>>J1031074.aviz_comisie AS text_aviz_comisie_reinternare ,
>>J1031074.criteriu_urgenta1 AS criteriu_urgenta1 ,
>>J1031074.criteriu_urgenta2 AS criteriu_urgenta2 ,
>>J1031074.criteriu_urgenta3 AS criteriu_urgenta3 ,
>>J1031074.criteriu_urgenta4 AS criteriu_urgenta4 ,
>>J1031074.criteriu_urgenta5 AS criteriu_urgenta5 ,
>>J1031074.criteriu_urgenta6 AS criteriu_urgenta6 ,
>>J1031074.criteriu_urgenta7 AS criteriu_urgenta7 ,
>>J1031074.criteriu_urgenta8 AS criteriu_urgenta8 ,
>>J1031074.criteriu_urgenta9a AS criteriu_urgenta9a ,
>>J1031074.criteriu_urgenta9b AS criteriu_urgenta9b ,
>>J1031074.criteriu_urgenta10 AS criteriu_urgenta10 ,
>>J1031074.criteriu_urgenta11 AS criteriu_urgenta11 ,
>>J1031074.criteriu_urgenta12 AS criteriu_urgenta12 ,
>>J1031074.criteriu_urgenta13 AS criteriu_urgenta13 ,
>>J1031074.infectie_nosocomiala AS infectie_nosocomiala ,
>>J1031074.cu_programare AS cu_programare ,
>>J1031074.stare_critica_internare AS stare_critica_internare ,
>>J1033704.id AS _id_stari_spitalizari ,
>>J1033704.nume AS id_stari_spitalizari
>>    FROM focg AS J1031074
>>   LEFT JOIN person AS J1031075 ON ( J1031075.id = J1031074.idpatient)
>>    LEFT JOIN sex AS J1031076 ON ( J1031076.id = J1031075.idsex)
>>    LEFT JOIN focg_serials AS J1031077 ON ( J1031077.id =
>>J1031074.id_focg_serials)
>>    LEFT JOIN ensuredstatustype AS J1031078 ON ( J1031078.id =
>>J1031074.idensuredstatustype)
>>    LEFT JOIN ensuredstatus AS J1031079 ON ( J1031079.id =
>>J1031074.idensuredstatus)
>>    LEFT JOIN org_units AS J1031080 ON ( J1031080.id =
>>J1031074.id_org_unit)
>>    LEFT JOIN personstate AS J1031081 ON ( J1031081.id =
>>J1031074.id_categorie_asigurat)
>>    LEFT JOIN focginterntype AS J1031082 ON ( J1031082.id =
>>J1031074.id_focg_tip_internare)
>>    LEFT JOIN focginterncrit AS J1031083 ON ( J1031083.id =
>>J1031074.id_focg_criteriu_internare)
>>    LEFT JOIN physicians AS J1031084 ON ( J1031084.id =
>>J1031074.idphysiciancurrent)
>>    LEFT JOIN personnel AS J1031085 ON ( J1031085.id =
>>J1031084.id_personnel)
>>    LEFT JOIN person AS J1031086 ON ( J1031086.id = J1031085.idperson)
>>    LEFT JOIN physicians AS J1031087 ON ( J1031087.id =
>>J1031074.idphysician_madeby)
>>    LEFT JOIN proffession AS J1031088 ON ( J1031088.id =
>>J1031074.idproffesion)
>>    LEFT JOIN educationlevel AS J1031089 ON ( J1031089.id =
>>J1031074.id_education_level)
>>    LEFT JOIN focg_situatii_speciale AS J1031090 ON ( J1031090.id =
>>J1031074.id_focg_situatii_speciale)
>>    LEFT JOIN focg_internat_prin AS J1031091 ON ( J1031091.id =
>>J1031074.id_focg_internat_prin)
>>    LEFT JOIN formulare_europene AS J1031092 ON ( J1031092.id =
>>J1031074.id_formulare_europene)
>>    LEFT JOIN focg AS J1031093 ON ( J1031093.id = J1031074.id_focg)
>>    LEFT JOIN person AS J1031094 ON ( J1031094.id =
>>J1031074.id_cnp_mama)
>>    LEFT JOIN focg_sursa_internare AS J1031095 ON ( J1031095.id =
>>J1031074.id_focg_sursa_internare)
>>    LEFT JOIN diseasecategory AS J1031096 ON ( J1031096.id =
>>J1031074.id_diseasecategory)
>>    LEFT JOIN icd10 AS J1031097 ON ( J1031097.id = J1031074.id_icd10)
>>    LEFT JOIN focg_contract_modes AS J1031098 ON ( J1031098.id =
>>J1031074.id_focg_contract_modes)
>>    LEFT JOIN focg_criterii_urgenta AS J1031099 ON ( J1031099.id =
>>J1031074.id_focg_criterii_urgenta)
>>    LEFT JOIN exceptie_bilet_internare AS J1031100 ON ( J1031100.id =
>>J1031074.id_exceptie_bilet_internare)
>>    LEFT JOIN focg_tip_externare AS J1031736 ON ( J1031736.id =
>>J1031074.id_focg_tip_externare)
>>    LEFT JOIN focg_stare_externare AS J1031737 ON ( J1031737.id =
>>J1031074.id_focg_stare_externare)
>>    LEFT JOIN physicians AS J1033295 ON ( J1033295.id =
>>J1031074.idphysician_surgeon)
>>    LEFT JOIN hospital_release_statuses AS J1033299 ON ( J1033299.id =
>>J1031074.id_hospital_release_statuses)
>>    LEFT JOIN citizenship AS J1033304 ON ( J1033304.id =
>>J1031074.id_citizenship)
>>    LEFT JOIN drg_tip_cetatenie AS J1033358 ON ( J1033358.id =
>>J1031074.id_drg_tip_cetatenie)
>>    LEFT JOIN stari_spitalizari AS J1033704 ON ( J1033704.id =
>>J1031074.id_stari_spitalizari)
>>    LEFT JOIN focgdepartment AS J1031101 ON ( J1031101.idfocg =
>>J1031074.id)
>>    LEFT JOIN department AS J1033386 ON ( J1033386.id =
>>J1031101.iddepartment)
>>    LEFT JOIN focgdepartment AS J1031102 ON ( J1031102.idfocg =
>>J1031074.id AND J1031102.validfrom < J1031101.validfrom)
>>    LEFT JOIN focgdepartment AS J1033359 ON ( J1033359.idfocg =
>>J1031074.id)
>>    LEFT JOIN department AS J1033387 ON ( J1033387.id =
>>J1033359.iddepartment)
>>    LEFT JOIN focgdepartment AS J1033360 ON ( J1033360.idfocg =
>>J1031074.id AND J1033360.validfrom > J1033359.validfrom)
>>     JOIN focgdepartment AS J1033407 ON ( J1033407.idfocg = J1031074.id
>>AND J1033407.iddepartment IN (/*Executing QueryID 476590*/SELECT
>>DISTINCT(J1034477.id) AS ret FROM department AS J1034477 JOIN
>>personnel_department AS J1034478 ON ( J1034478.id_department =
>>J1034477.id)
>>   JOIN personnel AS J1034479 ON ( J1034479.id = J1034478.id_personnel
>>AND J1034479.id_connected_user = '1')))
>>
>>  WHERE J1031102.id IS NULL AND J1033360.id IS NULL AND ( (
>>DATE(J1031101.validfrom)>= DATE('2016-05-01') AND
>>DATE(J1031101.validfrom)<= DATE('2016-05-31 00:00:00.0') ) OR (
>>J1033359.validto IS NOT NULL AND DATE(J1033359.validto)>=
>>DATE('2016-05-01') AND DATE(J1033359.validto)<= DATE('2016-05-31
>>00:00:00.0') ) OR ( J1033359.validto IS NULL AND
>>DATE(J1031101.validfrom)<= DATE('2016-05-01') ) )
>>
>>    ORDER BY J1031101.validfrom DESC
>>  LIMIT 20 OFFSET 0
>>
>>------ Original Message ------
>>From: "David Rowley" <david.rowley@2ndquadrant.com>
>>To: "Sterpu Victor" <victor@caido.ro>
>>Cc: "Rob Imig" <rimig88@gmail.com>; "PostgreSQL General"
>><pgsql-general@postgresql.org>; "David G. Johnston"
>><david.g.johnston@gmail.com>
>>Sent: 9/5/2016 10:04:54 AM
>>Subject: Re: [GENERAL] Slow query when the select list is big
>>
>>>On 9 May 2016 at 18:46, David G. Johnston
>>><david.g.johnston@gmail.com> wrote:
>>>>  On Sunday, May 8, 2016, Sterpu Victor <victor@caido.ro> wrote:
>>>>>
>>>>>  Yes but it is very big.
>>>>>  I don't understand why the select list is influencing the CPU
>>>>>usage.
>>>>>  I was expecting that only the join and where clauses would
>>>>>influence CPU.
>>>>>
>>>>
>>>>  PostgreSQL is smart enough to optimize away stuff that it knows
>>>>doesn't
>>>>  impact the final query result.
>>>
>>>To be more accurate with what David is saying, PostgreSQL will remove
>>>unused LEFT JOINed relations where the left joined relation can be
>>>proved to not duplicate rows from the right hand side. It would just
>>>be a matter of comparing the EXPLAINs from the query with all the
>>>SELECT items to the one with the single SELECT item to prove that
>>>this
>>>is what's happening.
>>>
>>>Please also note that this only occurs with LEFT JOINs
>>>
>>>It would also be quite helpful for people if you were to include a
>>>copy of the query. It's impossible to reverse engineer what that is
>>>from this EXPLAIN output. I see that your using a windowing function
>>>and performing a LIMIT 1, there may be ways to improve that just by
>>>selecting the single highest j1031101.validfrom row and performing
>>>the
>>>joins to the other table on that single row, but that will depend on
>>>which windowing function you're using as the function may require the
>>>other rows in the window frame to calculate the correct result.
>>>
>>>--
>>>  David Rowley http://www.2ndQuadrant.com/
>>>  PostgreSQL Development, 24x7 Support, Training & Services
>>
>>
>>
>>-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>>To make changes to your subscription:
>>http://www.postgresql.org/mailpref/pgsql-general
>

I tested it now, EXPLAIN ANALYZE is not showing LEFT JOIN at all if I
don't select from the joined tables.
Now is clear why the query is so mutch more efficient when I select less
data.

Thank you

------ Original Message ------
From: "David Rowley" <david.rowley@2ndquadrant.com>
To: "Sterpu Victor" <victor@caido.ro>
Cc: "Rob Imig" <rimig88@gmail.com>; "PostgreSQL General"
<pgsql-general@postgresql.org>; "David G. Johnston"
<david.g.johnston@gmail.com>
Sent: 9/5/2016 10:04:54 AM
Subject: Re: [GENERAL] Slow query when the select list is big

>On 9 May 2016 at 18:46, David G. Johnston <david.g.johnston@gmail.com>
>wrote:
>>  On Sunday, May 8, 2016, Sterpu Victor <victor@caido.ro> wrote:
>>>
>>>  Yes but it is very big.
>>>  I don't understand why the select list is influencing the CPU usage.
>>>  I was expecting that only the join and where clauses would influence
>>>CPU.
>>>
>>
>>  PostgreSQL is smart enough to optimize away stuff that it knows
>>doesn't
>>  impact the final query result.
>
>To be more accurate with what David is saying, PostgreSQL will remove
>unused LEFT JOINed relations where the left joined relation can be
>proved to not duplicate rows from the right hand side. It would just
>be a matter of comparing the EXPLAINs from the query with all the
>SELECT items to the one with the single SELECT item to prove that this
>is what's happening.
>
>Please also note that this only occurs with LEFT JOINs
>
>It would also be quite helpful for people if you were to include a
>copy of the query. It's impossible to reverse engineer what that is
>from this EXPLAIN output. I see that your using a windowing function
>and performing a LIMIT 1, there may be ways to improve that just by
>selecting the single highest j1031101.validfrom row and performing the
>joins to the other table on that single row, but that will depend on
>which windowing function you're using as the function may require the
>other rows in the window frame to calculate the correct result.
>
>--
>  David Rowley http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services

On May 09, Sterpu Victor modulated:
> I tested it now, EXPLAIN ANALYZE is not showing LEFT JOIN at all if
> I don't select from the joined tables.
> Now is clear why the query is so mutch more efficient when I select
> less data.
>
> Thank you
>

With so many joins, you may want to experiment with postgresql
parameter tuning.  These parameters in particular can have a
significant impact on the plan choice and execution time:

   work_mem
   effective_cache_size

   from_collapse_limit
   join_collapse_limit

   geqo_threshold
   geqo_effort

Setting these to appropriately large values can make analytic queries
run much faster.  Of course, setting them too high can also make for
very bad plans which cause the DB server to over subscribe its memory
and start swapping...  it requires a bit of reading and a bit of
experimentation to find ideal settings for your environment.


Karl

It works fine now, on my test server execution time went down from 6.4
seconds to 1.4 seconds and on the production server went down from 3.2
sec to 600ms.
To optimize the query I changed the order of some joins(the joins that
where used to limit rows are at the begining of the query)
I tried some of these parameters, I will try all tomorow.

------ Original Message ------
From: "Karl Czajkowski" <karlcz@isi.edu>
To: "Sterpu Victor" <victor@caido.ro>
Cc: "PostgreSQL General" <pgsql-general@postgresql.org>
Sent: 9/5/2016 8:47:12 PM
Subject: Re: Slow query when the select list is big

>On May 09, Sterpu Victor modulated:
>>  I tested it now, EXPLAIN ANALYZE is not showing LEFT JOIN at all if
>>  I don't select from the joined tables.
>>  Now is clear why the query is so mutch more efficient when I select
>>  less data.
>>
>>  Thank you
>>
>
>With so many joins, you may want to experiment with postgresql
>parameter tuning. These parameters in particular can have a
>significant impact on the plan choice and execution time:
>
>    work_mem
>    effective_cache_size
>
>    from_collapse_limit
>    join_collapse_limit
>
>    geqo_threshold
>    geqo_effort
>
>Setting these to appropriately large values can make analytic queries
>run much faster. Of course, setting them too high can also make for
>very bad plans which cause the DB server to over subscribe its memory
>and start swapping... it requires a bit of reading and a bit of
>experimentation to find ideal settings for your environment.
>
>
>Karl
>