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

Previous
From: Rob Imig
Date:
Subject: Re: Slow query when the select list is big
Next
From: Achilleas Mantzios
Date:
Subject: Re: Thoughts on "Love Your Database"