[postgis-users] Queries slow after PostGIS upgrade

[Kevin Berger]

We have run EXPLAIN ANALYZE for both systems. Results are below. The execution plans for both systems are different.

Non-AWS (8 seconds)
Unique  (cost=1251555.88..1251561.48 rows=200 width=20) (actual time=7768.991..7785.457 rows=10287 loops=1)
  Buffers: shared hit=3241 read=267051, temp written=60388
  CTE aoi
    ->  Index Scan using packageitem_pkey on packageitem  (cost=0.42..8.45 rows=1 width=32) (actual time=36.254..37.102 rows=1 loops=1)
          Index Cond: (id = 105812)
          Filter: (shape <> ''::text)
          Buffers: shared hit=187
  CTE product
    ->  Seq Scan on TITLES_MERGED  (cost=0.00..301259.50 rows=3363650 width=231) (actual time=0.054..2731.102 rows=2112234 loops=1)
          Buffers: shared hit=572 read=267051
  ->  Sort  (cost=950287.93..950290.73 rows=1121 width=20) (actual time=7768.975..7774.185 rows=10287 loops=1)
        Sort Key: product.PID
        Sort Method: quicksort  Memory: 867kB
        Buffers: shared hit=3241 read=267051, temp written=60388
        ->  Nested Loop  (cost=0.00..950231.15 rows=1121 width=20) (actual time=615.084..7755.414 rows=10287 loops=1)
              Join Filter: ((aoi.geom && product.geom) AND _st_intersects(aoi.geom, product.geom))
              Rows Removed by Join Filter: 2101947
              Buffers: shared hit=3241 read=267051, temp written=60388
              ->  CTE Scan on aoi  (cost=0.00..0.02 rows=1 width=32) (actual time=36.310..37.159 rows=1 loops=1)
                    Buffers: shared hit=187
              ->  CTE Scan on product  (cost=0.00..67273.00 rows=3363650 width=52) (actual time=0.057..6102.740 rows=2112234 loops=1)
                    Buffers: shared hit=572 read=267051, temp written=60388
Planning time: 0.488 ms
Execution time: 8058.153 ms

AWS (175 seconds)
Unique  (cost=6525.68..6536.22 rows=2108 width=6) (actual time=175018.980..175021.624 rows=10281 loops=1)
  Buffers: shared hit=177333
  ->  Sort  (cost=6525.68..6530.95 rows=2108 width=6) (actual time=175018.978..175019.657 rows=10281 loops=1)
        Sort Key: TITLES_MERGED.PID
        Sort Method: quicksort  Memory: 866kB
        Buffers: shared hit=177333
        ->  Nested Loop  (cost=2.09..6409.30 rows=2108 width=6) (actual time=25.016..174998.424 rows=10281 loops=1)
              Buffers: shared hit=177333
              ->  Index Scan using packageitem_pkey on packageitem  (cost=0.42..8.44 rows=1 width=87) (actual time=0.015..0.017 rows=1 loops=1)
                    Index Cond: (id = 105812)
                    Filter: (shape <> ''::text)
                    Buffers: shared hit=4
              ->  Index Scan using TITLES_MERGED_geom_1515720593664 on TITLES_MERGED  (cost=1.67..6398.76 rows=211 width=233) (actual time=12.766..174975.642 rows=10281 loops=1)
                    Index Cond: (geom && st_geomfromgeojson((((packageitem.shape)::jsonb || '{crs: {type: name, properties: {name: EPSG:3401}}}'::jsonb))::text))
                    Filter: st_intersects(st_geomfromgeojson((((packageitem.shape)::jsonb || '{crs: {type: name, properties: {name: EPSG:3401}}}'::jsonb))::text), geom)
                    Rows Removed by Filter: 5560
                    Buffers: shared hit=177146
Planning Time: 0.184 ms
Execution Time: 175022.182 ms

From: Kevin Berger
Sent: Wednesday, October 26, 2022 10:27 AM
To: postgis-users at lists.osgeo.org
Subject: Queries slow after PostGIS upgrade

We are migrating from a Windows Postgres Version 9.6.1 PostGIS Version 2.3.1 solution to an AWS RDS solution, Postgres Version 13.7 PostGIS Version 3.1.5. We used dump files to transfer the database contents to AWS, using pg_dump.exe to create schema dump files, then using psql to restore the dump files to the RDS instance.

However, we are finding that PostGIS database queries are taking significantly longer on RDS than on Windows (although querying non-PostGIS attribute data is actually faster). We are thinking this might be due to the PostGIS versioning, going from version 2 to version 3.

Any suggestions that we can do on the PostGIS side to improve query performance would be appreciated. Thanks.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20221027/dbf844fb/attachment.htm>