[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:
-----------------------+---------------------------

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)

--
Comment (by 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.
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5547#comment:1>
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.