[postgis-users] Queries slow after PostGIS upgrade

[Chris Mair]

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

Hi,

looks like this is a WITH query,
can you you try to run it again on AWS 
where instead of

  with product as (...
  with aoi as (...

you give

  with product as MATERIALIZED (...
  with aoi as MATERIALIZED (...

and see the explain analyze?

Chris.


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