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: [email protected]
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.
_______________________________________________
postgis-users mailing list
[email protected]
https://lists.osgeo.org/mailman/listinfo/postgis-users
