Changes between Initial Version and Version 1 of Ticket #5547, comment 3


Ignore:
Timestamp:
Dec 4, 2023, 9:31:28 AM (7 months ago)
Author:
pramsey

Legend:

Unmodified
Added
Removed
Modified

  • Ticket #5547, comment 3

    initial v1  
    1414
    1515I 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
    16 
    17 explain analyze SELECT fld_zone FROM s_fld_haz_ar WHERE st_intersects(st_geomfromtext(concat('POINT(', '-83.113523', ' ', '40.014991', ')'), 4269), wkb_geometry)
    18 
     16{{{
     17explain analyze
     18  SELECT fld_zone
     19  FROM s_fld_haz_ar
     20  WHERE st_intersects(
     21    st_geomfromtext(
     22      concat('POINT(', '-83.113523', ' ', '40.014991', ')'),
     23      4269),
     24    wkb_geometry)
     25}}}
    1926
    2027this one does a table scan, 11 and 3, Parallel Seq Scan on s_fld_haz_ar
     28{{{
     29select version(),postgis_full_Version()
    2130
    22 select version(),postgis_full_Version()
    2331 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
    24 
     32}}}
    2533
    2634this server uses spatial index, 11 and 2, using s_fld_haz_ar_wkb_geometry_geom_idx
    2735
     36{{{
    2837PostgreSQL 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)
    29 
     38}}}
    3039
    3140they both use the index if i dont create the point string using concat like
    32 
    33 explain analyze SELECT fld_zone FROM s_fld_haz_ar WHERE st_intersects(st_geomfromtext('POINT(-83.113523 40.014991)', 4269), wkb_geometry)
     41{{{
     42explain analyze
     43SELECT fld_zone
     44FROM s_fld_haz_ar
     45WHERE st_intersects(
     46  st_geomfromtext(
     47    'POINT(-83.113523 40.014991)',
     48    4269),
     49  wkb_geometry)