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

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