postgis 3.4 not using gist index when concatinating string in st_geomfromtext

[tomwlane]

This uses a gist index on wkb_geometry when I pass a string, gist (wkb_geometry)

SELECT fld_zone 
FROM s_fld_haz_ar 
WHERE st_intersects(
  st_geomfromtext('POINT(-83.113523 40.014991)', 4269), 
  wkb_geometry)

concatenating text fields to make the same string for the point, it will not use the gist index

SELECT fld_zone 
FROM s_fld_haz_ar 
WHERE st_intersects(
  st_geomfromtext(
    concat('POINT(', '-83.113523', ' ', '40.014991', ')'), 
    4269), 
  wkb_geometry)

I'm using POSTGIS="3.4.0 3.4.0", i just upgraded

it worked before I upgraded when I was using POSTGIS="2.5.3 r17699"

its not unique to one table so you can probably reproduce it with any tables, the plan generator just won't recognize that concatenation as the same string

I work around it by creating and passing a text variable

DECLARE p text = concat('POINT(', lon,' ', lat,')');
st_intersects(st_geomfromtext(p, 4269), wkb_geometry)

[robe]

Which version of PostgreSQL were you using?

SELECT version(), postgis_full_version();

If you can run both on old and new would help.

Key changes and costing happened between 2.5 < PostgreSQL 12 and PostGIS 3.0 + PostgreSQL > 12

Want to rule out it's one of those kinda things.

[tomwlane]

I did update postgresql at the same time so the one thats failing is 15 and postgis 3 and it worked when i was running 11 and postgis 2,

I don't currently have a way to test 11 and 3, but I may while I'm upgrading the prod servers, I'll plan to try

failing PostgreSQL 15.3, compiled by Visual C++ build 1914, 64-bit | POSTGIS="3.4.0 3.4.0" [EXTENSION] PGSQL="110" (procs need upgrade for use with PostgreSQL "150") GEOS="3.12.0-CAPI-1.18.0" PROJ="8.2.1 NETWORK_ENABLED=OFF URL_ENDPOINT=​https://cdn.proj.org USER_WRITABLE_DIRECTORY=C:\WINDOWS\ServiceProfiles\NetworkService\AppData\Local/proj DATABASE_PATH=C:\Program Files\PostgreSQL\15\share\contrib\postgis-3.4\proj\proj.db" GDAL="GDAL 3.7.1, released 2023/07/06" LIBXML="2.9.14" LIBJSON="0.12" LIBPROTOBUF="1.2.1" WAGYU="0.5.0 (Internal)" (core procs from "3.3.3 3.3.3" need upgrade) RASTER (raster procs from "3.3.3 3.3.3" need upgrade)

working PostgreSQL 11.18, compiled by Visual C++ build 1914, 64-bit | POSTGIS="2.5.3 r17699" PGSQL="100" (procs need upgrade for use with "110") GEOS="3.7.2-CAPI-1.11.0 3.7.2" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.2.4, released 2018/03/19" LIBXML="2.7.8" LIBJSON="0.12" LIBPROTOBUF="1.2.1" (core procs from "2.4.4 r16526" need upgrade) RASTER (raster procs from "2.4.4 r16526" need upgrade)

[tomwlane]

Replying to robe:

Which version of PostgreSQL were you using?

SELECT version(), postgis_full_version();

If you can run both on old and new would help.

Key changes and costing happened between 2.5 < PostgreSQL 12 and PostGIS 3.0 + PostgreSQL > 12

Want to rule out it's one of those kinda things.

I verified it still fails to use the index with old postgresql 11 and new postgis 3, so it appears its caused by the postgis 3 update alone

explain analyze 
  SELECT fld_zone 
  FROM s_fld_haz_ar 
  WHERE st_intersects(
    st_geomfromtext(
      concat('POINT(', '-83.113523', ' ', '40.014991', ')'), 
      4269), 
    wkb_geometry)

this one does a table scan, 11 and 3, Parallel Seq Scan on s_fld_haz_ar

select version(),postgis_full_Version()

 PostgreSQL 11.18, compiled by Visual C++ build 1914, 64-bit | POSTGIS="3.3.3 3.3.3" [EXTENSION] PGSQL="110" GEOS="3.11.2-CAPI-1.17.2" PROJ="8.2.1" GDAL="GDAL 3.6.4, released 2023/04/17" LIBXML="2.9.14" LIBJSON="0.16" LIBPROTOBUF="1.2.1" WAGYU="0.5.0 (Internal)" RASTER

this server uses spatial index, 11 and 2, using s_fld_haz_ar_wkb_geometry_geom_idx

PostgreSQL 11.18, compiled by Visual C++ build 1914, 64-bit | POSTGIS="2.5.3 r17699" PGSQL="100" (procs need upgrade for use with "110") GEOS="3.7.2-CAPI-1.11.0 3.7.2" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.2.4, released 2018/03/19" LIBXML="2.7.8" LIBJSON="0.12" LIBPROTOBUF="1.2.1" (core procs from "2.4.4 r16526" need upgrade) RASTER (raster procs from "2.4.4 r16526" need upgrade)

they both use the index if i dont create the point string using concat like

explain analyze 
SELECT fld_zone 
FROM s_fld_haz_ar 
WHERE st_intersects(
  st_geomfromtext(
    'POINT(-83.113523 40.014991)', 
    4269), 
  wkb_geometry)

[strk]

Tom, do you see the procs from ... need upgrade messages ? It means you want to run SELECT postgis_extensions_upgrade(); - basically your upgrade procedure wasn't completed.

Please wrap your code snippets between {{{ and }}} so they are rendered by Trac in monospace and newlines are respected, thanks.

[pramsey]

String concat is a great way to build SQL injection into your app, maybe use

ST_Point(lon, lat, srid)

instead? or

ST_SetSRID(ST_MakePoint(lon, lat), srid)

[pramsey]

# explain select count(*) from ldb_000b21a_e where st_intersects(st_geomfromtext(concat('POINT(' || 8978052 || ' ' || 2146588 || ')')), geom);
                                          QUERY PLAN                                          
----------------------------------------------------------------------------------------------
 Aggregate  (cost=22.06..22.07 rows=1 width=8)
   ->  Index Scan using geom_x on ldb_000b21a_e  (cost=0.91..22.06 rows=1 width=0)
         Index Cond: (geom && st_geomfromtext(concat('POINT(8978052 2146588)'::text)))
         Filter: st_intersects(st_geomfromtext(concat('POINT(8978052 2146588)'::text)), geom)
(4 rows)