Re: Slow query when the select list is big - Mailing list 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
>