Postgresql

我應該怎麼做才能讓執行 PostgreSQL 9.6 的 RDS 實例的性能至少與執行 PostgreSQL 9.3 的實例一樣好？

  • June 11, 2017

我正在嘗試將我們的 Amazon RDS 實例從帶有 PostGIS 2.1 的 PostgreqSQL 9.3 切換到帶有 PostGIS 2.3 的 PostgreSQL 9.6。我從快照啟動了一個新實例，並將新實例從 9.3 更新到 9.4、9.4 到 9.5，最後從 9.5 更新到 9.6。我現在有兩個實例都在執行，我正在測試和比較性能。我發現 PostgreSQL 9.6 中的空間查詢要慢得多。我已經重新索引了我的數據庫。我還應該做些什麼來讓新實例的性能至少與舊實例一樣好？

實例 1：

x86_64-unknown-linux-gnu 上的 PostgreSQL 9.3.14，由 gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16) 編譯，64 位 POSTGIS=“2.1.8 r13780” GEOS=“3.5.0 -CAPI-1.9.0 r4084” PROJ=“Rel. 4.9.2，2015 年 9 月 8 日” GDAL=“GDAL 1.11.5，2016/07/01 發布” LIBXML=“2.9.1” LIBJSON=“UNKNOWN”（核心來自“2.1.3 r12547”的procs需要升級）RASTER（來自“2.1.3 r12547”的光柵procs需要升級）

實例 2：

x86_64-pc-linux-gnu 上的 PostgreSQL 9.6.2，由 gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16) 編譯，64 位 POSTGIS=“2.3.2 r15302” GEOS=“3.5.1 -CAPI-1.9.1 r4246” PROJ=“Rel. 4.9.3，2016 年 8 月 15 日” GDAL=“GDAL 2.1.3，2017/20/01 發布” LIBXML=“2.9.1” LIBJSON=“0.12” RASTER

將 US American Community Survey 塊組數據聚合到一個點周圍 1/2 英里緩衝區的查詢：

select sum(frac*b01003_001) as b01003_001
from (
   select geoid,
   ST_Area(ST_Intersection(geom, ST_Transform(  
       ST_Buffer(  
           ST_Transform( 
               ST_SetSRID(ST_Point(-87.6809189, 41.9101084),4326)
           ,utmzone(ST_SetSRID(ST_Point(-87.6809189, 41.9101084),4326)))
       , (0.5*1609.344), 12)
   , 4326)))/ST_Area(geom) as frac
   from blkgrps_2015
   where ST_Intersects(geom, ST_Transform(  
       ST_Buffer(  
           ST_Transform( 
               ST_SetSRID(ST_Point(-87.6809189, 41.9101084),4326)
           ,utmzone(ST_SetSRID(ST_Point(-87.6809189, 41.9101084),4326)))
       , (0.5*1609.344), 12)
   , 4326))
) a
join acs2015.g_2015_5 b on a.geoid = b.state||b.county||b.tract||b.blkgrp and b.sumlevel = '150'
join acs2015.e_2015_5_0003000 on b.logrecno = acs2015.e_2015_5_0003000.logrecno and b.stusab = acs2015.e_2015_5_0003000.stusab

（utmzone 是一個函式，用於標識一個點的 UTM 區域，以便適當地重新投影到以米為單位而不是度數的投影中）

查詢在實例 1 上執行大約 220 毫秒，但在實例 2 上執行大約 3.7 秒。

從實例 1 解釋：

Aggregate  (cost=30566.88..30566.89 rows=1 width=4822)
 ->  Nested Loop  (cost=17.52..30566.62 rows=1 width=4822)
       ->  Hash Join  (cost=17.10..30565.61 rows=1 width=4825)
             Hash Cond: (((((b.state)::text || (b.county)::text) || (b.tract)::text) || (b.blkgrp)::text) = (blkgrps_2015.geoid)::text)
             ->  Seq Scan on g_2015_5 b  (cost=0.00..28898.08 rows=220057 width=27)
                   Filter: ((sumlevel)::text = '150'::text)
             ->  Hash  (cost=17.09..17.09 rows=1 width=4827)
                   ->  Index Scan using blkgrps_2015_gist on blkgrps_2015  (cost=0.28..17.09 rows=1 width=4827)
                         Index Cond: (geom && '0103000020E61000000100000031000000396F3E38F5EA55C04AB9C64D80F444408567CE6AF6EA55C06BB0B14A61F44440249A5B50FAEA55C0E9D24EC742F44440FD31CDD700EB55C083C6434925F44440218783E409EB55C08A1CBC5109F444403FEED54E15EB55C01DC1335BEFF34440BBC5C0E422EB55C0F8C65ED7D7F34440FFBFC06A32EB55C067B7372DC3F34440EAA6D79C43EB55C009E43CB7B1F344409320B62F56EB55C08E69E4C1A3F34440195E05D269EB55C056A34D8A99F34440DB13CB2D7EEB55C0B1C2353D93F344403AA9E1E992EB55C0A01734F690F344406E377EABA7EB55C0D65F41BF92F34440AFB3BD17BCEB55C0EA298C9098F34440208232D5CFEB55C08A0A9B50A2F344406AAD6B8DE2EB55C0880EBCD4AFF34440F9196FEEF3EB55C0F383BFE1C0F34440E14320AC03EC55C01FECFA2CD5F34440DC698D8111EC55C09CAD8F5DECF34440D7751D321DEC55C06EF5EF0D06F444401C7A998A26EC55C0DD269BCD21F4444032370D622DEC55C0F2450A233FF44440B3CF7A9A31EC55C062F6C38D5DF444407B915E2133EC55C002FA8E887CF44440AD8B00F031EC55C07D93B98B9BF44440AE83920B2EEC55C0BAD46B0FBAF44440B1BC198527EC55C0FBAEFA8DD7F44440D2E924791EEC55C078953186F3F4444069864F0F13EC55C0E6AA887D0DF5444019AF947905EC55C0FDC53D0225F54440066C74F3F5EB55C04A1747AD39F544409026EFC0E4EB55C0EEE116244BF5444021BE5B2DD2EB55C0CF85281A59F5444024561D8ABEEB55C03D09505263F54440B6863E2DAAEB55C0C65DC69F69F544408910F86F95EB55C09AC3EDE66BF54440D68F29AD80EB55C0CBEACA1D6AF54440F4E8CA3F6CEB55C0F9BC304C64F54440A6495D8158EB55C07C0E9E8B5AF54440199C62C845EB55C0FBCDCD064DF54440D42BE26634EB55C08F9EFBF83BF54440C30101A924EB55C07315E0AC27F544409724B4D316EB55C03712697B10F54440277792230BEB55C01FD333CAF6F44440A961CBCB01EB55C04A78CF09DBF444400BCF46F5FAEA55C0019CCFB3BDF444403450F2BDF6EA55C0AD77B7489FF44440396F3E38F5EA55C04AB9C64D80F44440'::geometry)
                         Filter: _st_intersects(geom, '0103000020E61000000100000031000000396F3E38F5EA55C04AB9C64D80F444408567CE6AF6EA55C06BB0B14A61F44440249A5B50FAEA55C0E9D24EC742F44440FD31CDD700EB55C083C6434925F44440218783E409EB55C08A1CBC5109F444403FEED54E15EB55C01DC1335BEFF34440BBC5C0E422EB55C0F8C65ED7D7F34440FFBFC06A32EB55C067B7372DC3F34440EAA6D79C43EB55C009E43CB7B1F344409320B62F56EB55C08E69E4C1A3F34440195E05D269EB55C056A34D8A99F34440DB13CB2D7EEB55C0B1C2353D93F344403AA9E1E992EB55C0A01734F690F344406E377EABA7EB55C0D65F41BF92F34440AFB3BD17BCEB55C0EA298C9098F34440208232D5CFEB55C08A0A9B50A2F344406AAD6B8DE2EB55C0880EBCD4AFF34440F9196FEEF3EB55C0F383BFE1C0F34440E14320AC03EC55C01FECFA2CD5F34440DC698D8111EC55C09CAD8F5DECF34440D7751D321DEC55C06EF5EF0D06F444401C7A998A26EC55C0DD269BCD21F4444032370D622DEC55C0F2450A233FF44440B3CF7A9A31EC55C062F6C38D5DF444407B915E2133EC55C002FA8E887CF44440AD8B00F031EC55C07D93B98B9BF44440AE83920B2EEC55C0BAD46B0FBAF44440B1BC198527EC55C0FBAEFA8DD7F44440D2E924791EEC55C078953186F3F4444069864F0F13EC55C0E6AA887D0DF5444019AF947905EC55C0FDC53D0225F54440066C74F3F5EB55C04A1747AD39F544409026EFC0E4EB55C0EEE116244BF5444021BE5B2DD2EB55C0CF85281A59F5444024561D8ABEEB55C03D09505263F54440B6863E2DAAEB55C0C65DC69F69F544408910F86F95EB55C09AC3EDE66BF54440D68F29AD80EB55C0CBEACA1D6AF54440F4E8CA3F6CEB55C0F9BC304C64F54440A6495D8158EB55C07C0E9E8B5AF54440199C62C845EB55C0FBCDCD064DF54440D42BE26634EB55C08F9EFBF83BF54440C30101A924EB55C07315E0AC27F544409724B4D316EB55C03712697B10F54440277792230BEB55C01FD333CAF6F44440A961CBCB01EB55C04A78CF09DBF444400BCF46F5FAEA55C0019CCFB3BDF444403450F2BDF6EA55C0AD77B7489FF44440396F3E38F5EA55C04AB9C64D80F44440'::geometry)
       ->  Index Scan using e_2015_5_0003000_pkey on e_2015_5_0003000  (cost=0.42..1.00 rows=1 width=19)
             Index Cond: (((stusab)::text = (b.stusab)::text) AND ((logrecno)::text = (b.logrecno)::text))

從實例 2 解釋：

Aggregate  (cost=443049.07..443049.08 rows=1 width=8)
 ->  Merge Join  (cost=188362.17..258031.69 rows=3083623 width=40)
       Merge Cond: ((blkgrps_2015.geoid)::text = (((((b.state)::text || (b.county)::text) || (b.tract)::text) || (b.blkgrp)::text)))
       ->  Sort  (cost=71139.31..71176.10 rows=14716 width=74)
             Sort Key: blkgrps_2015.geoid
             ->  Bitmap Heap Scan on blkgrps_2015  (cost=1639.07..70120.59 rows=14716 width=74)
                   Recheck Cond: (geom && '0103000020E61000000100000031000000386F3E38F5EA55C04BB9C64D80F444408467CE6AF6EA55C06AB0B14A61F44440239A5B50FAEA55C0EAD24EC742F44440FC31CDD700EB55C083C6434925F444401F8783E409EB55C0881CBC5109F444403EEED54E15EB55C01CC1335BEFF34440B9C5C0E422EB55C0F5C65ED7D7F34440FEBFC06A32EB55C068B7372DC3F34440EAA6D79C43EB55C00AE43CB7B1F344409120B62F56EB55C08F69E4C1A3F34440185E05D269EB55C056A34D8A99F34440DA13CB2D7EEB55C0B2C2353D93F3444038A9E1E992EB55C0A01734F690F344406D377EABA7EB55C0D55F41BF92F34440ADB3BD17BCEB55C0E9298C9098F344401F8232D5CFEB55C08A0A9B50A2F3444069AD6B8DE2EB55C0890EBCD4AFF34440F7196FEEF3EB55C0F483BFE1C0F34440DF4320AC03EC55C01DECFA2CD5F34440DA698D8111EC55C09DAD8F5DECF34440D6751D321DEC55C06EF5EF0D06F444401B7A998A26EC55C0DD269BCD21F4444030370D622DEC55C0F2450A233FF44440B1CF7A9A31EC55C061F6C38D5DF444407A915E2133EC55C002FA8E887CF44440AC8B00F031EC55C07D93B98B9BF44440AC83920B2EEC55C0BAD46B0FBAF44440B0BC198527EC55C0FAAEFA8DD7F44440D0E924791EEC55C078953186F3F4444068864F0F13EC55C0E6AA887D0DF5444017AF947905EC55C0FDC53D0225F54440056C74F3F5EB55C04C1747AD39F544408F26EFC0E4EB55C0EEE116244BF544401FBE5B2DD2EB55C0D185281A59F5444022561D8ABEEB55C03D09505263F54440B5863E2DAAEB55C0C75DC69F69F544408810F86F95EB55C09AC3EDE66BF54440D58F29AD80EB55C0CBEACA1D6AF54440F4E8CA3F6CEB55C0F9BC304C64F54440A5495D8158EB55C07D0E9E8B5AF54440189C62C845EB55C0FACDCD064DF54440D32BE26634EB55C0919EFBF83BF54440C20101A924EB55C07415E0AC27F544409524B4D316EB55C03712697B10F54440267792230BEB55C01FD333CAF6F44440A861CBCB01EB55C04B78CF09DBF4444009CF46F5FAEA55C0019CCFB3BDF444403450F2BDF6EA55C0AD77B7489FF44440386F3E38F5EA55C04BB9C64D80F44440'::geometry)
                   Filter: _st_intersects(geom, '0103000020E61000000100000031000000386F3E38F5EA55C04BB9C64D80F444408467CE6AF6EA55C06AB0B14A61F44440239A5B50FAEA55C0EAD24EC742F44440FC31CDD700EB55C083C6434925F444401F8783E409EB55C0881CBC5109F444403EEED54E15EB55C01CC1335BEFF34440B9C5C0E422EB55C0F5C65ED7D7F34440FEBFC06A32EB55C068B7372DC3F34440EAA6D79C43EB55C00AE43CB7B1F344409120B62F56EB55C08F69E4C1A3F34440185E05D269EB55C056A34D8A99F34440DA13CB2D7EEB55C0B2C2353D93F3444038A9E1E992EB55C0A01734F690F344406D377EABA7EB55C0D55F41BF92F34440ADB3BD17BCEB55C0E9298C9098F344401F8232D5CFEB55C08A0A9B50A2F3444069AD6B8DE2EB55C0890EBCD4AFF34440F7196FEEF3EB55C0F483BFE1C0F34440DF4320AC03EC55C01DECFA2CD5F34440DA698D8111EC55C09DAD8F5DECF34440D6751D321DEC55C06EF5EF0D06F444401B7A998A26EC55C0DD269BCD21F4444030370D622DEC55C0F2450A233FF44440B1CF7A9A31EC55C061F6C38D5DF444407A915E2133EC55C002FA8E887CF44440AC8B00F031EC55C07D93B98B9BF44440AC83920B2EEC55C0BAD46B0FBAF44440B0BC198527EC55C0FAAEFA8DD7F44440D0E924791EEC55C078953186F3F4444068864F0F13EC55C0E6AA887D0DF5444017AF947905EC55C0FDC53D0225F54440056C74F3F5EB55C04C1747AD39F544408F26EFC0E4EB55C0EEE116244BF544401FBE5B2DD2EB55C0D185281A59F5444022561D8ABEEB55C03D09505263F54440B5863E2DAAEB55C0C75DC69F69F544408810F86F95EB55C09AC3EDE66BF54440D58F29AD80EB55C0CBEACA1D6AF54440F4E8CA3F6CEB55C0F9BC304C64F54440A5495D8158EB55C07D0E9E8B5AF54440189C62C845EB55C0FACDCD064DF54440D32BE26634EB55C0919EFBF83BF54440C20101A924EB55C07415E0AC27F544409524B4D316EB55C03712697B10F54440267792230BEB55C01FD333CAF6F44440A861CBCB01EB55C04B78CF09DBF4444009CF46F5FAEA55C0019CCFB3BDF444403450F2BDF6EA55C0AD77B7489FF44440386F3E38F5EA55C04BB9C64D80F44440'::geometry)
                   ->  Bitmap Index Scan on blkgrps_2015_gist  (cost=0.00..1635.39 rows=44148 width=0)
                         Index Cond: (geom && '0103000020E61000000100000031000000386F3E38F5EA55C04BB9C64D80F444408467CE6AF6EA55C06AB0B14A61F44440239A5B50FAEA55C0EAD24EC742F44440FC31CDD700EB55C083C6434925F444401F8783E409EB55C0881CBC5109F444403EEED54E15EB55C01CC1335BEFF34440B9C5C0E422EB55C0F5C65ED7D7F34440FEBFC06A32EB55C068B7372DC3F34440EAA6D79C43EB55C00AE43CB7B1F344409120B62F56EB55C08F69E4C1A3F34440185E05D269EB55C056A34D8A99F34440DA13CB2D7EEB55C0B2C2353D93F3444038A9E1E992EB55C0A01734F690F344406D377EABA7EB55C0D55F41BF92F34440ADB3BD17BCEB55C0E9298C9098F344401F8232D5CFEB55C08A0A9B50A2F3444069AD6B8DE2EB55C0890EBCD4AFF34440F7196FEEF3EB55C0F483BFE1C0F34440DF4320AC03EC55C01DECFA2CD5F34440DA698D8111EC55C09DAD8F5DECF34440D6751D321DEC55C06EF5EF0D06F444401B7A998A26EC55C0DD269BCD21F4444030370D622DEC55C0F2450A233FF44440B1CF7A9A31EC55C061F6C38D5DF444407A915E2133EC55C002FA8E887CF44440AC8B00F031EC55C07D93B98B9BF44440AC83920B2EEC55C0BAD46B0FBAF44440B0BC198527EC55C0FAAEFA8DD7F44440D0E924791EEC55C078953186F3F4444068864F0F13EC55C0E6AA887D0DF5444017AF947905EC55C0FDC53D0225F54440056C74F3F5EB55C04C1747AD39F544408F26EFC0E4EB55C0EEE116244BF544401FBE5B2DD2EB55C0D185281A59F5444022561D8ABEEB55C03D09505263F54440B5863E2DAAEB55C0C75DC69F69F544408810F86F95EB55C09AC3EDE66BF54440D58F29AD80EB55C0CBEACA1D6AF54440F4E8CA3F6CEB55C0F9BC304C64F54440A5495D8158EB55C07D0E9E8B5AF54440189C62C845EB55C0FACDCD064DF54440D32BE26634EB55C0919EFBF83BF54440C20101A924EB55C07415E0AC27F544409524B4D316EB55C03712697B10F54440267792230BEB55C01FD333CAF6F44440A861CBCB01EB55C04B78CF09DBF4444009CF46F5FAEA55C0019CCFB3BDF444403450F2BDF6EA55C0AD77B7489FF44440386F3E38F5EA55C04BB9C64D80F44440'::geometry)
       ->  Materialize  (cost=117222.86..117432.40 rows=41908 width=136)
             ->  Sort  (cost=117222.86..117327.63 rows=41908 width=136)
                   Sort Key: (((((b.state)::text || (b.county)::text) || (b.tract)::text) || (b.blkgrp)::text))
                   ->  Nested Loop  (cost=55.29..111138.89 rows=41908 width=136)
                         ->  Bitmap Heap Scan on g_2015_5 b  (cost=54.86..8064.96 rows=2895 width=192)
                               Recheck Cond: ((sumlevel)::text = '150'::text)
                               ->  Bitmap Index Scan on sumlev_2015_idx  (cost=0.00..54.14 rows=2895 width=0)
                                     Index Cond: ((sumlevel)::text = '150'::text)
                         ->  Index Scan using e_2015_5_0003000_pkey on e_2015_5_0003000  (cost=0.42..35.46 rows=14 width=72)
                               Index Cond: (((stusab)::text = (b.stusab)::text) AND ((logrecno)::text = (b.logrecno)::text))

實例 1 使用我在 blkgrps_2015 的幾何列上的 gist 索引進行索引掃描，其中實例 2 進行點陣圖索引掃描。

任何幫助表示讚賞。謝謝！

解決方案似乎是執行VACUUM FULL ANALYZE;。雖然我在重新索引數據庫後確實看到了顯著的性能改進，但這可能是一個不必要的步驟。我應該跑VACUUM FULL ANALYZE;

這是執行後實例 2 的新解釋VACUUM FULL ANALYZE：

Aggregate  (cost=19242.48..19242.49 rows=1 width=8)
 ->  Nested Loop  (cost=4183.20..19242.42 rows=1 width=4878)
       ->  Hash Join  (cost=4182.77..19241.42 rows=1 width=4881)
             Hash Cond: (((((b.state)::text || (b.county)::text) || (b.tract)::text) || (b.blkgrp)::text) = (blkgrps_2015.geoid)::text)
             ->  Bitmap Heap Scan on g_2015_5 b  (cost=4174.21..17562.74 rows=222682 width=27)
                   Recheck Cond: ((sumlevel)::text = '150'::text)
                   ->  Bitmap Index Scan on sumlev_2015_idx  (cost=0.00..4118.54 rows=222682 width=0)
                         Index Cond: ((sumlevel)::text = '150'::text)
             ->  Hash  (cost=8.55..8.55 rows=1 width=4883)
                   ->  Index Scan using blkgrps_2015_gist on blkgrps_2015  (cost=0.28..8.55 rows=1 width=4883)
                         Index Cond: (geom && '0103000020E61000000100000031000000386F3E38F5EA55C04BB9C64D80F444408467CE6AF6EA55C06AB0B14A61F44440239A5B50FAEA55C0EAD24EC742F44440FC31CDD700EB55C083C6434925F444401F8783E409EB55C0881CBC5109F444403EEED54E15EB55C01CC1335BEFF34440B9C5C0E422EB55C0F5C65ED7D7F34440FEBFC06A32EB55C068B7372DC3F34440EAA6D79C43EB55C00AE43CB7B1F344409120B62F56EB55C08F69E4C1A3F34440185E05D269EB55C056A34D8A99F34440DA13CB2D7EEB55C0B2C2353D93F3444038A9E1E992EB55C0A01734F690F344406D377EABA7EB55C0D55F41BF92F34440ADB3BD17BCEB55C0E9298C9098F344401F8232D5CFEB55C08A0A9B50A2F3444069AD6B8DE2EB55C0890EBCD4AFF34440F7196FEEF3EB55C0F483BFE1C0F34440DF4320AC03EC55C01DECFA2CD5F34440DA698D8111EC55C09DAD8F5DECF34440D6751D321DEC55C06EF5EF0D06F444401B7A998A26EC55C0DD269BCD21F4444030370D622DEC55C0F2450A233FF44440B1CF7A9A31EC55C061F6C38D5DF444407A915E2133EC55C002FA8E887CF44440AC8B00F031EC55C07D93B98B9BF44440AC83920B2EEC55C0BAD46B0FBAF44440B0BC198527EC55C0FAAEFA8DD7F44440D0E924791EEC55C078953186F3F4444068864F0F13EC55C0E6AA887D0DF5444017AF947905EC55C0FDC53D0225F54440056C74F3F5EB55C04C1747AD39F544408F26EFC0E4EB55C0EEE116244BF544401FBE5B2DD2EB55C0D185281A59F5444022561D8ABEEB55C03D09505263F54440B5863E2DAAEB55C0C75DC69F69F544408810F86F95EB55C09AC3EDE66BF54440D58F29AD80EB55C0CBEACA1D6AF54440F4E8CA3F6CEB55C0F9BC304C64F54440A5495D8158EB55C07D0E9E8B5AF54440189C62C845EB55C0FACDCD064DF54440D32BE26634EB55C0919EFBF83BF54440C20101A924EB55C07415E0AC27F544409524B4D316EB55C03712697B10F54440267792230BEB55C01FD333CAF6F44440A861CBCB01EB55C04B78CF09DBF4444009CF46F5FAEA55C0019CCFB3BDF444403450F2BDF6EA55C0AD77B7489FF44440386F3E38F5EA55C04BB9C64D80F44440'::geometry)
                         Filter: _st_intersects(geom, '0103000020E61000000100000031000000386F3E38F5EA55C04BB9C64D80F444408467CE6AF6EA55C06AB0B14A61F44440239A5B50FAEA55C0EAD24EC742F44440FC31CDD700EB55C083C6434925F444401F8783E409EB55C0881CBC5109F444403EEED54E15EB55C01CC1335BEFF34440B9C5C0E422EB55C0F5C65ED7D7F34440FEBFC06A32EB55C068B7372DC3F34440EAA6D79C43EB55C00AE43CB7B1F344409120B62F56EB55C08F69E4C1A3F34440185E05D269EB55C056A34D8A99F34440DA13CB2D7EEB55C0B2C2353D93F3444038A9E1E992EB55C0A01734F690F344406D377EABA7EB55C0D55F41BF92F34440ADB3BD17BCEB55C0E9298C9098F344401F8232D5CFEB55C08A0A9B50A2F3444069AD6B8DE2EB55C0890EBCD4AFF34440F7196FEEF3EB55C0F483BFE1C0F34440DF4320AC03EC55C01DECFA2CD5F34440DA698D8111EC55C09DAD8F5DECF34440D6751D321DEC55C06EF5EF0D06F444401B7A998A26EC55C0DD269BCD21F4444030370D622DEC55C0F2450A233FF44440B1CF7A9A31EC55C061F6C38D5DF444407A915E2133EC55C002FA8E887CF44440AC8B00F031EC55C07D93B98B9BF44440AC83920B2EEC55C0BAD46B0FBAF44440B0BC198527EC55C0FAAEFA8DD7F44440D0E924791EEC55C078953186F3F4444068864F0F13EC55C0E6AA887D0DF5444017AF947905EC55C0FDC53D0225F54440056C74F3F5EB55C04C1747AD39F544408F26EFC0E4EB55C0EEE116244BF544401FBE5B2DD2EB55C0D185281A59F5444022561D8ABEEB55C03D09505263F54440B5863E2DAAEB55C0C75DC69F69F544408810F86F95EB55C09AC3EDE66BF54440D58F29AD80EB55C0CBEACA1D6AF54440F4E8CA3F6CEB55C0F9BC304C64F54440A5495D8158EB55C07D0E9E8B5AF54440189C62C845EB55C0FACDCD064DF54440D32BE26634EB55C0919EFBF83BF54440C20101A924EB55C07415E0AC27F544409524B4D316EB55C03712697B10F54440267792230BEB55C01FD333CAF6F44440A861CBCB01EB55C04B78CF09DBF4444009CF46F5FAEA55C0019CCFB3BDF444403450F2BDF6EA55C0AD77B7489FF44440386F3E38F5EA55C04BB9C64D80F44440'::geometry)
       ->  Index Scan using e_2015_5_0003000_pkey on e_2015_5_0003000  (cost=0.42..0.99 rows=1 width=19)
             Index Cond: (((stusab)::text = (b.stusab)::text) AND ((logrecno)::text = (b.logrecno)::text))

因此，吸取的教訓是，從快照啟動 RDS 實例時，應VACUUM FULL ANALYZE;在測試之前執行。

引用自：https://dba.stackexchange.com/questions/175665

相關問答

Postgresql

無法在 PgSQL AWS RDS 中撤銷權限或刪除使用者

  • October 27, 2021
檢視問答
Postgresql

Postgres 數據庫標記為 STORAGE FULL 而有 45 Gb 可用

  • August 14, 2021
檢視問答
Postgresql

為什麼 RDS PostgreSQL 總是在重啟時恢復？

  • April 8, 2021
檢視問答
Postgresql

為什麼 PostgreSQL 的自動解釋在 AWS RDS 中不起作用？

  • April 8, 2020
檢視問答
Postgresql

在物化視圖中將多個列組合在一起

  • December 17, 2019
檢視問答
Postgresql

我們的應用伺服器在連接到帶有 9.6 客戶端的 Postgres 11.4 伺服器時會出現問題嗎？

  • September 25, 2019
檢視問答