Re: Slow query when the select list is big

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(at)gmail(dot)com>
To: "Sterpu Victor" <victor(at)caido(dot)ro>; "PostgreSQL General"
<pgsql-general(at)postgresql(dot)org>
Sent: 9/5/2016 9:03:10 AM
Subject: Re: [GENERAL] Slow query when the select list is big

>Can you share the full query and output of EXPLAIN ? Not much data here
>yet.
>On Mon, May 9, 2016 at 6:58 AM Sterpu Victor <victor(at)caido(dot)ro> wrote:
>>I have a big query that takes about 7 seconds to run(time sending the
>>data to the client is not counted).
>>Postgres uses 100% of 1 CPU when solving this query. I tried to run
>>the query on a HDD and on a SSD with no difference. HDD show about 10%
>>usage while the query runs.
>>
>>The query has a big "select" list and no "group by" clause. If I
>>delete all selects except one the query runs in under a second(it
>>doesn't matter what field remains selected).
>>It seems that the query is slow because of the long select, can I do
>>something to make the query faster?
>>
>>Thank you.
>>