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@xxxxxxxxxxxxxxx>
To: "Sterpu Victor" <victor@xxxxxxxx>
Cc: "Rob Imig" <rimig88@xxxxxxxxx>; "PostgreSQL General"
<pgsql-general@xxxxxxxxxxxxxx>; "David G. Johnston"
<david.g.johnston@xxxxxxxxx>
Sent: 9/5/2016 10:04:54 AM
Subject: Re: Slow query when the select list is big
On 9 May 2016 at 18:46, David G. Johnston <david.g.johnston@xxxxxxxxx>
wrote:
On Sunday, May 8, 2016, Sterpu Victor <victor@xxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
