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
