[postgis-tickets] [PostGIS] #5547: postgis 3.4 not using gist index when concatinating string in st_geomfromtext

[PostGIS]

#5547: postgis 3.4 not using gist index when concatinating string in
st_geomfromtext
-----------------------+---------------------------
  Reporter:  tomwlane  |      Owner:  pramsey
      Type:  defect    |     Status:  new
  Priority:  medium    |  Milestone:  PostGIS 3.4.1
 Component:  postgis   |    Version:  3.4.x
Resolution:            |   Keywords:
-----------------------+---------------------------
Comment (by tomwlane):

 Replying to [comment:1 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)
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5547#comment:3>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.