Re: Slow query when the select list is big - Mailing list pgsql-general

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