Hello I'm on a PostgreSQL 12.1 and I just restored a database from a backup. When I run a query I get a big execution time: 5.482 msAfter running EXPLAIN ANALYZE I can see that the "Planning Time: 5165.742 ms" and the "Execution Time: 6.244 ms". The database is new(no need to vacuum) and i'm the only one connected to it. I use a single partition on the harddrive.
I also tried this on a postgresql 9.5 and the result was the same. I'm not sure what to do to improve this situation. The query and the explain is attached.
Thank you
"Limit (cost=67.86..67.87 rows=1 width=3972) (actual time=0.033..0.033 rows=0
loops=1)"
" -> Sort (cost=67.86..67.87 rows=1 width=3972) (actual time=0.032..0.032
rows=0 loops=1)"
" Sort Key: j1031101.validfrom DESC"
" Sort Method: quicksort Memory: 25kB"
" -> Nested Loop Left Join (cost=9.12..67.85 rows=1 width=3972)
(actual time=0.022..0.022 rows=0 loops=1)"
" Join Filter: (j1034965.id = j1031074.id_persontype)"
" -> Nested Loop Left Join (cost=9.12..66.71 rows=1 width=3940)
(actual time=0.022..0.022 rows=0 loops=1)"
" -> Nested Loop Left Join (cost=8.98..66.14 rows=1
width=3936) (actual time=0.021..0.021 rows=0 loops=1)"
" -> Nested Loop Left Join (cost=8.84..64.50 rows=1
width=3932) (actual time=0.021..0.021 rows=0 loops=1)"
" Join Filter: (j1033358.id =
j1031074.id_drg_tip_cetatenie)"
" -> Nested Loop Left Join (cost=8.84..63.41
rows=1 width=3900) (actual time=0.021..0.021 rows=0 loops=1)"
" -> Nested Loop Left Join
(cost=8.70..62.84 rows=1 width=3384) (actual time=0.021..0.021 rows=0 loops=1)"
" -> Nested Loop Left Join
(cost=8.55..62.14 rows=1 width=3277) (actual time=0.021..0.021 rows=0 loops=1)"
" -> Nested Loop Left Join
(cost=8.40..60.49 rows=1 width=3270) (actual time=0.021..0.021 rows=0 loops=1)"
" Join Filter:
(j1031737.id = j1031074.id_focg_stare_externare)"
" -> Nested Loop Left
Join (cost=8.40..59.38 rows=1 width=3238) (actual time=0.021..0.021 rows=0
loops=1)"
" Join Filter:
(j1031736.id = j1031074.id_focg_tip_externare)"
" -> Nested Loop
Left Join (cost=8.40..58.29 rows=1 width=3206) (actual time=0.020..0.020
rows=0 loops=1)"
" Join
Filter: (j1031100.id = j1031074.id_exceptie_bilet_internare)"
" -> Nested
Loop Left Join (cost=8.40..57.09 rows=1 width=3174) (actual time=0.020..0.020
rows=0 loops=1)"
" ->
Nested Loop Left Join (cost=8.27..56.53 rows=1 width=3142) (actual
time=0.019..0.019 rows=0 loops=1)"
"
Join Filter: (j1031098.id = j1031074.id_focg_contract_modes)"
"
-> Nested Loop Left Join (cost=8.27..55.46 rows=1 width=3110) (actual
time=0.019..0.019 rows=0 loops=1)"
"
-> Nested Loop Left Join (cost=8.13..54.89 rows=1 width=3078) (actual
time=0.019..0.019 rows=0 loops=1)"
"
-> Nested Loop Left Join (cost=7.99..54.33 rows=1 width=3050)
(actual time=0.019..0.019 rows=0 loops=1)"
"
-> Nested Loop Left Join (cost=7.71..51.35 rows=1
width=3011) (actual time=0.019..0.019 rows=0 loops=1)"
"
Join Filter: (j1031095.id =
j1031074.id_focg_sursa_internare)"
"
-> Nested Loop Left Join (cost=7.71..50.26 rows=1
width=2979) (actual time=0.019..0.019 rows=0 loops=1)"
"
Join Filter: (j1031094.id =
j1031074.id_cnp_mama)"
"
-> Nested Loop Left Join (cost=7.71..49.24
rows=1 width=2969) (actual time=0.019..0.019 rows=0 loops=1)"
"
-> Nested Loop Left Join
(cost=7.57..47.61 rows=1 width=2969) (actual time=0.019..0.019 rows=0 loops=1)"
"
Join Filter: (j1031091.id =
j1031074.id_focg_internat_prin)"
"
-> Nested Loop Left Join
(cost=7.57..46.52 rows=1 width=2937) (actual time=0.019..0.019 rows=0 loops=1)"
"
-> Nested Loop Left Join
(cost=7.44..45.96 rows=1 width=2425) (actual time=0.018..0.018 rows=0 loops=1)"
"
-> Nested Loop Left
Join (cost=7.31..45.40 rows=1 width=2397) (actual time=0.018..0.018 rows=0
loops=1)"
"
-> Nested Loop
Left Join (cost=7.17..44.84 rows=1 width=1881) (actual time=0.017..0.017
rows=0 loops=1)"
"
-> Nested
Loop Left Join (cost=7.02..43.19 rows=1 width=1874) (actual time=0.017..0.017
rows=0 loops=1)"
"
->
Nested Loop Left Join (cost=6.87..41.54 rows=1 width=1867) (actual
time=0.017..0.017 rows=0 loops=1)"
"
-> Nested Loop Left Join (cost=6.73..40.98 rows=1 width=1839) (actual
time=0.017..0.017 rows=0 loops=1)"
"
-> Nested Loop Left Join (cost=6.59..40.41 rows=1 width=1811) (actual
time=0.017..0.017 rows=0 loops=1)"
"
Join Filter: (j1031079.id = j1031074.idensuredstatus)"
"
-> Nested Loop Left Join (cost=6.59..39.30 rows=1 width=1299)
(actual time=0.017..0.017 rows=0 loops=1)"
"
-> Nested Loop Left Join (cost=6.46..38.74 rows=1
width=1271) (actual time=0.017..0.017 rows=0 loops=1)"
"
-> Nested Loop Left Join (cost=6.32..38.17 rows=1
width=1243) (actual time=0.017..0.017 rows=0 loops=1)"
"
-> Nested Loop Left Join (cost=6.19..37.62
rows=1 width=731) (actual time=0.016..0.016 rows=0 loops=1)"
"
Join Filter: (j1031076.id = j1031075.idsex)"
"
-> Nested Loop Left Join (cost=6.19..36.53
rows=1 width=219) (actual time=0.015..0.015 rows=0 loops=1)"
"
-> Nested Loop Left Join
(cost=6.07..35.97 rows=1 width=186) (actual time=0.015..0.015 rows=0 loops=1)"
"
Join Filter: (j1033360.validfrom
> j1033359.validfrom)"
"
Filter: (j1033360.id IS NULL)"
"
-> Nested Loop
(cost=5.91..34.33 rows=1 width=198) (actual time=0.015..0.015 rows=0 loops=1)"
"
-> Nested Loop
(cost=5.79..34.00 rows=2 width=182) (actual time=0.015..0.015 rows=0 loops=1)"
"
Join Filter:
(j1031074.id = j1033359.idfocg)"
"
-> Nested Loop Left
Join (cost=5.64..33.79 rows=1 width=166) (actual time=0.015..0.015 rows=0
loops=1)"
"
Join Filter:
(j1031102.validfrom < j1031101.validfrom)"
"
Filter:
(j1031102.id IS NULL)"
"
-> Nested
Loop Left Join (cost=5.48..32.14 rows=1 width=166) (actual time=0.015..0.015
rows=0 loops=1)"
"
->
Nested Loop Left Join (cost=5.35..31.98 rows=1 width=168) (actual
time=0.015..0.015 rows=0 loops=1)"
"
->
Nested Loop Left Join (cost=5.06..30.94 rows=1 width=168) (actual
time=0.015..0.015 rows=0 loops=1)"
"
-> Nested Loop Anti Join (cost=4.91..29.30 rows=1 width=168) (actual
time=0.015..0.015 rows=0 loops=1)"
"
Join Filter: (j1037912.id < j1037911.id)"
"
-> Nested Loop Left Join (cost=4.76..26.16 rows=1 width=168) (actual
time=0.013..0.013 rows=0 loops=1)"
"
-> Nested Loop (cost=4.61..24.51 rows=1 width=164) (actual
time=0.013..0.013 rows=0 loops=1)"
"
-> Nested Loop (cost=4.47..16.40 rows=41 width=32)
(actual time=0.013..0.013 rows=0 loops=1)"
"
-> Seq Scan on department j1033386
(cost=0.00..1.01 rows=1 width=24) (actual time=0.005..0.005 rows=1 loops=1)"
"
-> Bitmap Heap Scan on focgdepartment j1031101
(cost=4.47..14.98 rows=41 width=16) (actual time=0.006..0.006 rows=0 loops=1)"
"
Recheck Cond: (iddepartment = j1033386.id)"
"
-> Bitmap Index Scan on
foszdepartment_simple_index10 (cost=0.00..4.46 rows=41 width=0) (actual
time=0.004..0.004 rows=0 loops=1)"
"
Index Cond: (iddepartment = j1033386.id)"
"
-> Index Scan using fosz_pkey on focg j1031074
(cost=0.14..0.19 rows=1 width=140) (never executed)"
"
Index Cond: (id = j1031101.idfocg)"
"
Filter: (id_stari_spitalizari = 1)"
"
-> Index Scan using focg_procedures_uni on focg_procedures
j1037911 (cost=0.15..1.64 rows=1 width=8) (never executed)"
"
Index Cond: ((id_focg = j1031074.id) AND
(este_ic_principala = 1))"
"
-> Index Scan using focg_procedures_uni on focg_procedures j1037912
(cost=0.15..1.64 rows=1 width=8) (never executed)"
"
Index Cond: ((id_focg = j1031074.id) AND (este_ic_principala =
1))"
"
-> Index Scan using address_pkey on address j1037908 (cost=0.15..1.64
rows=1 width=8) (never executed)"
"
Index Cond: (id = j1031074.id_address_domiciliu)"
"
->
Index Scan using city_pkey on city j1037909 (cost=0.29..1.02 rows=1 width=8)
(never executed)"
"
Index Cond: (id = j1037908.idcity)"
"
->
Index Scan using citytype_pkey on citytype j1037910 (cost=0.14..0.15 rows=1
width=6) (never executed)"
"
Index Cond: (id = j1037909.citytype)"
"
-> Index Scan
using focgdepartment_uni2 on focgdepartment j1031102 (cost=0.15..1.64 rows=1
width=16) (never executed)"
"
Index
Cond: (idfocg = j1031074.id)"
"
-> Index Scan using
focgdepartment_uni2 on focgdepartment j1033359 (cost=0.15..0.20 rows=1
width=24) (never executed)"
"
Index Cond:
(idfocg = j1031101.idfocg)"
"
-> Index Scan using
department_pkey on department j1033387 (cost=0.12..0.15 rows=1 width=24)
(never executed)"
"
Index Cond: (id =
j1033359.iddepartment)"
"
-> Index Scan using
focgdepartment_uni2 on focgdepartment j1033360 (cost=0.15..1.64 rows=1
width=16) (never executed)"
"
Index Cond: (idfocg =
j1031074.id)"
"
-> Index Scan using person_pkey on
person j1031075 (cost=0.12..0.54 rows=1 width=41) (never executed)"
"
Index Cond: (id =
j1031074.idpatient)"
"
-> Seq Scan on sex j1031076
(cost=0.00..1.04 rows=4 width=520) (never executed)"
"
-> Index Scan using ensuredstatustype_pkey on
ensuredstatustype j1031078 (cost=0.13..0.55 rows=1 width=520) (never executed)"
"
Index Cond: (id =
j1031074.idensuredstatustype)"
"
-> Index Scan using foszinterntype_pkey on
focginterntype j1031082 (cost=0.14..0.55 rows=1 width=36) (never executed)"
"
Index Cond: (id = j1031074.id_focg_tip_internare)"
"
-> Index Scan using foszinterncrit_pkey on focginterncrit
j1031083 (cost=0.14..0.55 rows=1 width=36) (never executed)"
"
Index Cond: (id = j1031074.id_focg_criteriu_internare)"
"
-> Seq Scan on ensuredstatus j1031079 (cost=0.00..1.05 rows=5
width=520) (never executed)"
"
-> Index Scan using org_units_pkey on org_units j1031080
(cost=0.14..0.56 rows=1 width=36) (never executed)"
"
Index Cond: (id = j1031074.id_org_unit)"
"
-> Index Scan using personstate_pkey on personstate j1031081 (cost=0.14..0.55
rows=1 width=36) (never executed)"
"
Index Cond: (id = j1031074.id_categorie_asigurat)"
"
->
Index Scan using physicians_pkey on physicians j1031084 (cost=0.15..1.64
rows=1 width=15) (never executed)"
"
Index Cond: (id = j1031074.idphysiciancurrent)"
"
-> Index
Scan using physicians_pkey on physicians j1031087 (cost=0.15..1.64 rows=1
width=11) (never executed)"
"
Index
Cond: (id = j1031074.idphysician_madeby)"
"
-> Index Scan
using proffession_pkey on proffession j1031088 (cost=0.13..0.55 rows=1
width=520) (never executed)"
"
Index Cond:
(id = j1031074.idproffesion)"
"
-> Index Scan using
focg_situatii_speciale_pkey on focg_situatii_speciale j1031090
(cost=0.13..0.55 rows=1 width=36) (never executed)"
"
Index Cond: (id =
j1031074.id_focg_situatii_speciale)"
"
-> Index Scan using
educationlevel_pkey on educationlevel j1031089 (cost=0.14..0.55 rows=1
width=520) (never executed)"
"
Index Cond: (id =
j1031074.id_education_level)"
"
-> Seq Scan on focg_internat_prin
j1031091 (cost=0.00..1.04 rows=4 width=36) (never executed)"
"
-> Index Scan using fosz_pkey on focg
j1031093 (cost=0.14..1.62 rows=1 width=8) (never executed)"
"
Index Cond: (id = j1031074.id_focg)"
"
-> Seq Scan on person j1031094
(cost=0.00..1.01 rows=1 width=18) (never executed)"
"
-> Seq Scan on focg_sursa_internare j1031095
(cost=0.00..1.04 rows=4 width=36) (never executed)"
"
-> Index Scan using icd10_pkey on icd10 j1031097
(cost=0.28..2.96 rows=1 width=43) (never executed)"
"
Index Cond: (id = j1031074.id_icd10)"
"
-> Index Scan using formulare_europene_pkey on formulare_europene
j1031092 (cost=0.14..0.55 rows=1 width=36) (never executed)"
"
Index Cond: (id = j1031074.id_formulare_europene)"
"
-> Index Scan using diseasecategory_pkey on diseasecategory j1031096
(cost=0.14..0.56 rows=1 width=36) (never executed)"
"
Index Cond: (id = j1031074.id_diseasecategory)"
"
-> Seq Scan on focg_contract_modes j1031098 (cost=0.00..1.03 rows=3
width=36) (never executed)"
" ->
Index Scan using focg_criterii_urgenta_pkey on focg_criterii_urgenta j1031099
(cost=0.14..0.55 rows=1 width=36) (never executed)"
"
Index Cond: (id = j1031074.id_focg_criterii_urgenta)"
" -> Seq
Scan on exceptie_bilet_internare j1031100 (cost=0.00..1.09 rows=9 width=36)
(never executed)"
" -> Seq Scan on
focg_tip_externare j1031736 (cost=0.00..1.04 rows=4 width=36) (never executed)"
" -> Seq Scan on
focg_stare_externare j1031737 (cost=0.00..1.05 rows=5 width=36) (never
executed)"
" -> Index Scan using
physicians_pkey on physicians j1033295 (cost=0.15..1.64 rows=1 width=11)
(never executed)"
" Index Cond: (id =
j1031074.idphysician_surgeon)"
" -> Index Scan using
hospital_release_statuses_pkey on hospital_release_statuses j1033299
(cost=0.14..0.69 rows=1 width=111) (never executed)"
" Index Cond: (id =
j1031074.id_hospital_release_statuses)"
" -> Index Scan using citizenship_pkey on
citizenship j1033304 (cost=0.14..0.56 rows=1 width=520) (never executed)"
" Index Cond: (id =
j1031074.id_citizenship)"
" -> Seq Scan on drg_tip_cetatenie j1033358
(cost=0.00..1.04 rows=4 width=36) (never executed)"
" -> Index Scan using fupu_pkey on fupu j1034618
(cost=0.14..1.63 rows=1 width=8) (never executed)"
" Index Cond: (id = j1031074.id_fupu)"
" -> Index Scan using country_pkey on country j1034962
(cost=0.14..0.56 rows=1 width=8) (never executed)"
" Index Cond: (id = j1031074.id_country)"
" -> Seq Scan on persontype j1034965 (cost=0.00..1.06 rows=6
width=36) (never executed)"
"Planning time: 3769.727 ms"
"Execution time: 1.494 ms"
query.sql
Description: Binary data
