What should I do to get my RDS instance running PostgreSQL 9.6 to perform at least as well as my instance running PostgreSQL 9.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),

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.

StackExchange Database Administrators Q#175665, answer score: 2