Re: Slow query when the select list is big - Mailing list pgsql-general
| From | Sterpu Victor |
|---|---|
| Subject | Re: Slow query when the select list is big |
| Date | |
| Msg-id | em3e58935e-4cd3-4170-af86-2d61e21d2ff0@victor-pc Whole thread Raw |
| In response to | Re: Slow query when the select list is big (Rob Imig <rimig88@gmail.com>) |
| Responses |
Re: Slow query when the select list is big
(John R Pierce <pierce@hogranch.com>)
Re: Slow query when the select list is big ("David G. Johnston" <david.g.johnston@gmail.com>) |
| List | pgsql-general |
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)"
" -> 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@gmail.com>
To: "Sterpu Victor" <victor@caido.ro>; "PostgreSQL General" <pgsql-general@postgresql.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@caido.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.
pgsql-general by date: