|
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.
"Limit (cost=190115.90..190115.93 rows=1 width=6391)"
" -> WindowAgg (cost=190115.90..190115.93 rows=1 width=6391)" " -> Sort (cost=190115.90..190115.90 rows=1 width=6391)" " Sort Key: j1031101.validfrom DESC" " -> Nested Loop Left Join (cost=100.22..190115.89 rows=1 width=6391)" " Join Filter: (j1033704.id = j1031074.id_stari_spitalizari)" " -> Nested Loop (cost=100.22..190114.84 rows=1 width=6359)" " Join Filter: (j1033407.iddepartment = j1034477.id)" " -> Nested Loop (cost=77.25..190091.74 rows=1 width=6363)" " -> Nested Loop Left Join (cost=76.95..190091.37 rows=1 width=6359)" " Join Filter: (j1033360.validfrom > j1033359.validfrom)" " Filter: (j1033360.id IS NULL)" " -> Nested Loop Left Join (cost=76.66..190091.00 rows=1 width=6367)" " Join Filter: (j1033387.id = j1033359.iddepartment)" " -> Nested Loop Left Join (cost=76.66..190088.83 rows=1 width=6351)" " Filter: (((date(j1031101.validfrom) >= '2016-05-01'::date) AND (date(j1031101.validfrom) <= '2016-05-31'::date)) OR ((j1033359.validto IS NOT NULL) AND (date(j1033359.validto) >= '2016-05-01'::date) AND (date(j1033359.validto) <= '2016-05-31'::date)) OR ((j1033359.validto IS NULL) AND (date(j1031101.validfrom) <= '2016-05-01'::date)))" " -> Nested Loop Left Join (cost=76.37..190088.44 rows=1 width=6331)" " Join Filter: (j1033358.id = j1031074.id_drg_tip_cetatenie)" " -> Nested Loop Left Join (cost=76.37..190087.35 rows=1 width=6299)" " Join Filter: (j1033304.id = j1031074.id_citizenship)" " -> Nested Loop Left Join (cost=76.37..190085.36 rows=1 width=5783)" " Join Filter: (j1033299.id = j1031074.id_hospital_release_statuses)" " -> Nested Loop Left Join (cost=76.37..190081.60 rows=1 width=5676)" " Join Filter: (j1033295.id = j1031074.idphysician_surgeon)" " -> Nested Loop Left Join (cost=76.37..190077.63 rows=1 width=5669)" " Join Filter: (j1031737.id = j1031074.id_focg_stare_externare)" " -> Nested Loop Left Join (cost=76.37..190076.52 rows=1 width=5637)" " Join Filter: (j1031736.id = j1031074.id_focg_tip_externare)" " -> Nested Loop Left Join (cost=76.37..190075.43 rows=1 width=5605)" " Join Filter: (j1031102.validfrom < j1031101.validfrom)" " Filter: (j1031102.id IS NULL)" " -> Hash Left Join (cost=76.08..155225.61 rows=94433 width=5605)" " Hash Cond: (j1031101.iddepartment = j1033386.id)" " -> Nested Loop Left Join (cost=73.91..153924.99 rows=94433 width=5589)" " -> Hash Left Join (cost=73.61..123840.99 rows=82075 width=5577)" " Hash Cond: (j1031074.id_exceptie_bilet_internare = j1031100.id)" " -> Hash Left Join (cost=72.48..123531.99 rows=82075 width=5545)" " Hash Cond: (j1031074.id_focg_criterii_urgenta = j1031099.id)" " -> Hash Left Join (cost=71.14..122977.79 rows=82075 width=5513)" " Hash Cond: (j1031074.id_focg_contract_modes = j1031098.id)" " -> Hash Left Join (cost=70.07..122285.19 rows=82075 width=5481)" " Hash Cond: (j1031074.id_icd10 = j1031097.id)" " -> Hash Left Join (cost=27.50..121681.03 rows=82075 width=5442)" " Hash Cond: (j1031074.id_diseasecategory = j1031096.id)" " -> Hash Left Join (cost=25.60..121308.32 rows=82075 width=5410)" " Hash Cond: (j1031074.id_focg_sursa_internare = j1031095.id)" " -> Nested Loop Left Join (cost=24.51..120178.73 rows=82075 width=5378)" " -> Nested Loop Left Join (cost=24.21..88054.73 rows=82075 width=5364)" " -> Hash Left Join (cost=23.92..40002.73 rows=82075 width=5360)" " Hash Cond: (j1031074.id_formulare_europene = j1031092.id)" " -> Hash Left Join (cost=22.79..39693.80 rows=82075 width=5328)" " Hash Cond: (j1031074.id_focg_internat_prin = j1031091.id)" " -> Hash Left Join (cost=21.70..39145.44 rows=82075 width=5296)" " Hash Cond: (j1031074.id_focg_situatii_speciale = j1031090.id)" " -> Hash Left Join (cost=20.61..38834.27 rows=82075 width=5264)" " Hash Cond: (j1031074.id_education_level = j1031089.id)" " -> Hash Left Join (cost=19.38..38318.84 rows=82075 width=4748)" " Hash Cond: (j1031074.idproffesion = j1031088.id)" " -> Hash Left Join (cost=18.20..37659.97 rows=82075 width=4232)" " Hash Cond: (j1031074.idphysician_madeby = j1031087.id)" " -> Hash Left Join (cost=14.23..37016.58 rows=82075 width=4225)" " Hash Cond: (j1031074.idphysiciancurrent = j1031084.id)" " -> Hash Left Join (cost=10.26..36297.22 rows=82075 width=4218)" " Hash Cond: (j1031074.id_focg_criteriu_internare = j1031083.id)" " -> Hash Left Join (cost=8.95..35533.59 rows=82075 width=4186)" " Hash Cond: (j1031074.id_focg_tip_internare = j1031082.id)" " -> Hash Left Join (cost=7.65..34768.02 rows=82075 width=4154)" " Hash Cond: (j1031074.id_categorie_asigurat = j1031081.id)" " -> Hash Left Join (cost=5.98..34151.50 rows=82075 width=4122)" " Hash Cond: (j1031074.id_org_unit = j1031080.id)" " -> Hash Left Join (cost=3.88..33323.12 rows=82075 width=4090)" " Hash Cond: (j1031074.idensuredstatus = j1031079.id)" " -> Hash Left Join (cost=2.76..32508.58 rows=82075 width=3574)" " Hash Cond: (j1031074.idensuredstatustype = j1031078.id)" " -> Hash Left Join (cost=1.68..31625.10 rows=82075 width=3058)" " Hash Cond: (j1031075.idsex = j1031076.id)" " -> Merge Left Join (cost=0.58..30495.48 rows=82075 width=2546)" " Merge Cond: (j1031074.idpatient = j1031075.id)" " -> Index Scan using fosz_simple_index33 on focg j1031074 (cost=0.29..23019.37 rows=82075 width=2517)" " -> Index Scan using person_pkey on person j1031075 (cost=0.29..6237.94 rows=85376 width=37)" " -> Hash (cost=1.04..1.04 rows=4 width=520)" " -> Seq Scan on sex j1031076 (cost=0.00..1.04 rows=4 width=520)" " -> Hash (cost=1.04..1.04 rows=4 width=520)" " -> Seq Scan on ensuredstatustype j1031078 (cost=0.00..1.04 rows=4 width=520)" " -> Hash (cost=1.05..1.05 rows=5 width=520)" " -> Seq Scan on ensuredstatus j1031079 (cost=0.00..1.05 rows=5 width=520)" " -> Hash (cost=1.49..1.49 rows=49 width=36)" " -> Seq Scan on org_units j1031080 (cost=0.00..1.49 rows=49 width=36)" " -> Hash (cost=1.30..1.30 rows=30 width=36)" " -> Seq Scan on personstate j1031081 (cost=0.00..1.30 rows=30 width=36)" " -> Hash (cost=1.13..1.13 rows=13 width=36)" " -> Seq Scan on focginterntype j1031082 (cost=0.00..1.13 rows=13 width=36)" " -> Hash (cost=1.14..1.14 rows=14 width=36)" " -> Seq Scan on focginterncrit j1031083 (cost=0.00..1.14 rows=14 width=36)" " -> Hash (cost=2.32..2.32 rows=132 width=15)" " -> Seq Scan on physicians j1031084 (cost=0.00..2.32 rows=132 width=15)" " -> Hash (cost=2.32..2.32 rows=132 width=11)" " -> Seq Scan on physicians j1031087 (cost=0.00..2.32 rows=132 width=11)" " -> Hash (cost=1.08..1.08 rows=8 width=520)" " -> Seq Scan on proffession j1031088 (cost=0.00..1.08 rows=8 width=520)" " -> Hash (cost=1.10..1.10 rows=10 width=520)" " -> Seq Scan on educationlevel j1031089 (cost=0.00..1.10 rows=10 width=520)" " -> Hash (cost=1.04..1.04 rows=4 width=36)" " -> Seq Scan on focg_situatii_speciale j1031090 (cost=0.00..1.04 rows=4 width=36)" " -> Hash (cost=1.04..1.04 rows=4 width=36)" " -> Seq Scan on focg_internat_prin j1031091 (cost=0.00..1.04 rows=4 width=36)" " -> Hash (cost=1.06..1.06 rows=6 width=36)" " -> Seq Scan on formulare_europene j1031092 (cost=0.00..1.06 rows=6 width=36)" " -> Index Scan using fosz_pkey on focg j1031093 (cost=0.29..0.58 rows=1 width=8)" " Index Cond: (id = j1031074.id_focg)" " -> Index Scan using person_pkey on person j1031094 (cost=0.29..0.38 rows=1 width=18)" " Index Cond: (id = j1031074.id_cnp_mama)" " -> Hash (cost=1.04..1.04 rows=4 width=36)" " -> Seq Scan on focg_sursa_internare j1031095 (cost=0.00..1.04 rows=4 width=36)" " -> Hash (cost=1.40..1.40 rows=40 width=36)" " -> Seq Scan on diseasecategory j1031096 (cost=0.00..1.40 rows=40 width=36)" " -> Hash (cost=26.70..26.70 rows=1270 width=43)" " -> Seq Scan on icd10 j1031097 (cost=0.00..26.70 rows=1270 width=43)" " -> Hash (cost=1.03..1.03 rows=3 width=36)" " -> Seq Scan on focg_contract_modes j1031098 (cost=0.00..1.03 rows=3 width=36)" " -> Hash (cost=1.15..1.15 rows=15 width=36)" " -> Seq Scan on focg_criterii_urgenta j1031099 (cost=0.00..1.15 rows=15 width=36)" " -> Hash (cost=1.06..1.06 rows=6 width=36)" " -> Seq Scan on exceptie_bilet_internare j1031100 (cost=0.00..1.06 rows=6 width=36)" " -> Index Scan using focgdepartment_idfocg on focgdepartment j1031101 (cost=0.29..0.36 rows=1 width=16)" " Index Cond: (idfocg = j1031074.id)" " -> Hash (cost=1.52..1.52 rows=52 width=24)" " -> Seq Scan on department j1033386 (cost=0.00..1.52 rows=52 width=24)" " -> Index Scan using focgdepartment_idfocg on focgdepartment j1031102 (cost=0.29..0.36 rows=1 width=16)" " Index Cond: (idfocg = j1031074.id)" " -> Seq Scan on focg_tip_externare j1031736 (cost=0.00..1.04 rows=4 width=36)" " -> Seq Scan on focg_stare_externare j1031737 (cost=0.00..1.05 rows=5 width=36)" " -> Seq Scan on physicians j1033295 (cost=0.00..2.32 rows=132 width=11)" " -> Seq Scan on hospital_release_statuses j1033299 (cost=0.00..2.78 rows=78 width=111)" " -> Seq Scan on citizenship j1033304 (cost=0.00..1.44 rows=44 width=520)" " -> Seq Scan on drg_tip_cetatenie j1033358 (cost=0.00..1.04 rows=4 width=36)" " -> Index Scan using focgdepartment_idfocg on focgdepartment j1033359 (cost=0.29..0.36 rows=1 width=24)" " Index Cond: (idfocg = j1031074.id)" " -> Seq Scan on department j1033387 (cost=0.00..1.52 rows=52 width=24)" " -> Index Scan using focgdepartment_idfocg on focgdepartment j1033360 (cost=0.29..0.36 rows=1 width=16)" " Index Cond: (idfocg = j1031074.id)" " -> Index Scan using focgdepartment_idfocg on focgdepartment j1033407 (cost=0.29..0.36 rows=1 width=8)" " Index Cond: (idfocg = j1031074.id)" " -> HashAggregate (cost=22.98..23.02 rows=4 width=4)" " Group Key: j1034477.id" " -> Nested Loop (cost=4.73..22.97 rows=4 width=4)" " -> Nested Loop (cost=4.59..22.28 rows=4 width=4)" " -> Index Scan using personnel_uni_user on personnel j1034479 (cost=0.28..8.29 rows=1 width=4)" " Index Cond: (id_connected_user = 1)" " -> Bitmap Heap Scan on personnel_department j1034478 (cost=4.31..13.95 rows=4 width=8)" " Recheck Cond: (id_personnel = j1034479.id)" " -> Bitmap Index Scan on personnel_department_id_personnel (cost=0.00..4.31 rows=4 width=0)" " Index Cond: (id_personnel = j1034479.id)" " -> Index Only Scan using department_pkey on department j1034477 (cost=0.14..0.16 rows=1 width=4)" " Index Cond: (id = j1034478.id_department)" " -> Seq Scan on stari_spitalizari j1033704 (cost=0.00..1.02 rows=2 width=36)" ------ Original Message ------
From: "Rob Imig" <rimig88@xxxxxxxxx>
To: "Sterpu Victor" <victor@xxxxxxxx>; "PostgreSQL General" <pgsql-general@xxxxxxxxxxxxxx>
Sent: 9/5/2016 9:03:10 AM
Subject: Re: Slow query when the select list is big
Can you share the full query and output of EXPLAIN ? Not much data here yet. |
