3

I am attempting to switch our Amazon RDS instance from PostgreqSQL 9.3 with PostGIS 2.1 to PostgreSQL 9.6 with PostGIS 2.3. I launched a new instance from a Snapshot and updated the new instance from 9.3 to 9.4, 9.4 to 9.5 and finally from 9.5 to 9.6. I now have both instances running and I'm testing and comparing performance. I'm finding spatial queries in PostgreSQL 9.6 to be much slower. I have reindexed my databases. Is there anything else I should do to get my new instance to perform at least as good as my old instance?

Instance 1:

PostgreSQL 9.3.14 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit POSTGIS="2.1.8 r13780" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 1.11.5, released 2016/07/01" LIBXML="2.9.1" LIBJSON="UNKNOWN" (core procs from "2.1.3 r12547" need upgrade) RASTER (raster procs from "2.1.3 r12547" need upgrade)

Instance 2:

PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit POSTGIS="2.3.2 r15302" GEOS="3.5.1-CAPI-1.9.1 r4246" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.1.3, released 2017/20/01" LIBXML="2.9.1" LIBJSON="0.12" RASTER

A query that aggregates U.S. American Community Survey block group data to a 1/2 mile buffer around a point:

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 is a function that identifies the UTM Zone for a point in order to appropriately reproject into a projection with units in meters rather than degrees)

The query runs in about 220 milliseconds on instance 1, but about 3.7 seconds on instance 2.

Explain from instance 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))

Explain from instance 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))

Instance 1 does an index scan using my gist index on the geometry column of blkgrps_2015 where instance 2 does a bitmap index scan.

Any help is appreciated. Thanks!

  • 2
    To identify performance differences explain (analyze, verbose) is more helpful, then a simple explain because it tells you exactly which step is slower (or faster) – a_horse_with_no_name Jun 8 '17 at 12:29
2

The solution appears to be to run VACUUM FULL ANALYZE;. While I did see significant performance improvements after reindexing the databases, it was probably an unnecessary step. I should have just run VACUUM FULL ANALYZE;

This is the new Explain from instance 2 after running 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))

So, lesson learned, when launching an RDS instance from a Snapshot, run VACUUM FULL ANALYZE; before testing.

  • 1
    Probably just an ANALYZE would have sufficed. – jjanes Jun 9 '17 at 2:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.