Re: 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(at)caido(dot)ro>
To: "David Rowley" <david(dot)rowley(at)2ndquadrant(dot)com>
Cc: "Rob Imig" <rimig88(at)gmail(dot)com>; "PostgreSQL General"
<pgsql-general(at)postgresql(dot)org>; "David G. Johnston"
<david(dot)g(dot)johnston(at)gmail(dot)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(dot)rowley(at)2ndquadrant(dot)com>
>To: "Sterpu Victor" <victor(at)caido(dot)ro>
>Cc: "Rob Imig" <rimig88(at)gmail(dot)com>; "PostgreSQL General"
><pgsql-general(at)postgresql(dot)org>; "David G. Johnston"
><david(dot)g(dot)johnston(at)gmail(dot)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(dot)g(dot)johnston(at)gmail(dot)com>
>>wrote:
>>> On Sunday, May 8, 2016, Sterpu Victor <victor(at)caido(dot)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(at)postgresql(dot)org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general