pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Sterpu Victor <victor@caido.ro>
To: pgsql-performance@lists.postgresql.org
Subject: Postgresql planning time too high
Date: Fri, 22 Nov 2019 11:21:03 +0000
Message-ID: <emd93eb533-aade-42ef-8609-2dccab1463d4@victor-pc> (raw )
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"
Attachments:
[text/plain] explain.txt (32.3K, ../emd93eb533-aade-42ef-8609-2dccab1463d4@victor-pc/3-explain.txt)
download | inline:
"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"
[application/octet-stream] query.sql (6.6K, ../emd93eb533-aade-42ef-8609-2dccab1463d4@victor-pc/4-query.sql)
download
view thread (4+ messages) latest in thread
Message-ID: <emd93eb533-aade-42ef-8609-2dccab1463d4@victor-pc>
Permalink: ../emd93eb533-aade-42ef-8609-2dccab1463d4@victor-pc/
Also on: postgresql.org/message-id/emd93eb533-aade-42ef-8609-2dccab1463d4@victor-pc
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: victor@caido.ro, pgsql-performance@lists.postgresql.org
Subject: Re: Postgresql planning time too high
In-Reply-To: <emd93eb533-aade-42ef-8609-2dccab1463d4@victor-pc>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox