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 ms
After 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"

Attachment: query.sql
Description: Binary data

Reply via email to