Continued performance regression from 3.5.0 with 3.5.1 using ST_Contains

[Paul Ramsey]

What was previous “fast” version? What were reference performance numbers on *that* version?
Can you provide the affected data?
That last “small” query looks pretty fast (half a milisecond). 
P

> On Dec 26, 2024, at 9:39 AM, Daryl Herzmann <akrherz at gmail.com> wrote:
> 
> Howdy All,
> 
> I saw some list traffic about a performance regression introduced with
> postgis 3.5.0 using ST_Within and I figured what I was observing was
> perhaps the same issue.  I am now using postgis 3.5.1 and am still
> seeing my performance problem, so I figured I had better chime in here
> to see what I could be doing wrong :)
> 
> My environment is Rocky Linux 9 64bit with pgrpms:
> 
> POSTGIS="3.5.1 48ab069" [EXTENSION] PGSQL="170"
> GEOS="3.13.0-CAPI-1.19.0" PROJ="9.4.1 NETWORK_ENABLED=OFF
> URL_ENDPOINT=https://cdn.proj.org
> USER_WRITABLE_DIRECTORY=/var/lib/pgsql/.local/s
> hare/proj DATABASE_PATH=/usr/proj94/share/proj/proj.db" (compiled
> against PROJ 9.5.1) LIBXML="2.9.13" LIBJSON="0.14" LIBPROTOBUF="1.3.3"
> WAGYU="0.5.0 (Internal)"
> 
> I notice that proj version mismatch, so I am unsure if that is at play or not...
> 
> A common explain analyze looks like so:
> 
> postgis=# explain (analyze, buffers) select count(*) from
> spc_outlook_geometries where st_contains(geom, ST_Point(-95, 42,
> 4326));
> 
>     QUERY PLAN
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Aggregate  (cost=14.80..14.81 rows=1 width=8) (actual
> time=11745.230..11745.231 rows=1 loops=1)
>   Buffers: shared hit=572500 read=23000
>   ->  Index Scan using spc_outlook_geometries_gix on
> spc_outlook_geometries  (cost=0.28..14.80 rows=1 width=0) (actual
> time=0.122..11738.404 rows=49009 loops=1)
>         Index Cond: (geom ~
> '0101000020E61000000000000000C057C00000000000004540'::geometry)
>         Filter: st_contains(geom,
> '0101000020E61000000000000000C057C00000000000004540'::geometry)
>         Rows Removed by Filter: 116312
>         Buffers: shared hit=572500 read=23000
> Planning Time: 0.172 ms
> Execution Time: 11745.475 ms
> (9 rows)
> 
> The relation looks like so.
> 
> postgis=# \d spc_outlook_geometries
>                     Table "public.spc_outlook_geometries"
>     Column     |            Type             | Collation | Nullable | Default
> ----------------+-----------------------------+-----------+----------+---------
> spc_outlook_id | integer                     |           |          |
> threshold      | character varying(4)        |           |          |
> category       | character varying(64)       |           |          |
> geom           | geometry(MultiPolygon,4326) |           |          |
> geom_layers    | geometry(MultiPolygon,4326) |           |          |
> Indexes:
>    "spc_outlook_geometries_combo_idx" btree (threshold, category)
>    "spc_outlook_geometries_gix" gist (geom)
>    "spc_outlook_geometries_idx" btree (spc_outlook_id)
>    "spc_outlook_geometries_layers_gix" gist (geom_layers)
> Check constraints:
>    "_sog_geom_isvalid" CHECK (st_isvalid(geom))
>    "_sog_geom_layers_isvalid" CHECK (st_isvalid(geom_layers))
> Foreign-key constraints:
>    "spc_outlook_geometries_threshold_fkey" FOREIGN KEY (threshold)
> REFERENCES spc_outlook_thresholds(threshold)
> 
> Even a "cheaper" query takes considerable time.
> 
> postgis=# explain (analyze, buffers) select st_area(geom) from
> spc_outlook_geometries where st_contains(geom, ST_Point(-95, 42,
> 4326)) LIMIT 10;
> 
> QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------------------------------------------------
> Limit  (cost=0.28..14.93 rows=1 width=8) (actual time=0.115..0.643
> rows=10 loops=1)
>   Buffers: shared hit=90
>   ->  Index Scan using spc_outlook_geometries_gix on
> spc_outlook_geometries  (cost=0.28..14.93 rows=1 width=8) (actual
> time=0.115..0.641 rows=10 loops=1)
>         Index Cond: (geom ~
> '0101000020E61000000000000000C057C00000000000004540'::geometry)
>         Filter: st_contains(geom,
> '0101000020E61000000000000000C057C00000000000004540'::geometry)
>         Rows Removed by Filter: 57
>         Buffers: shared hit=90
> Planning Time: 0.086 ms
> Execution Time: 0.657 ms
> 
> thank you for your time!
> daryl