Continued performance regression from 3.5.0 with 3.5.1 using ST_Contains

[Daryl Herzmann]

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