[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
 Keywords:            |
----------------------+---------------------------
 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)
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5547>
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.