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 <akrh...@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
