We implemented Chris's suggestion with product as (... with aoi as (... you give with product as MATERIALIZED (... with aoi as MATERIALIZED (... and see the explain analyze?
The query is now running in 4-5 seconds so using MATERIALIZED appears to have resolved our issue. Thanks for your suggestion Chris. From: Kevin Berger Sent: Thursday, October 27, 2022 8:32 AM To: '[email protected]' <[email protected]<mailto:[email protected]>> Subject: RE: Queries slow after PostGIS upgrade 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]<mailto:[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
