[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.3.6
 Component:  postgis   |    Version:  3.4.x
Resolution:            |   Keywords:
-----------------------+---------------------------
Description changed by pramsey:

Old description:

> 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)

New description:

 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#comment:7>
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.