[PostGIS] #5984: PostGIS selectivity is screwing up queries and forcing it to choose a spatial index when it's inappropriate

[PostGIS]

#5984: PostGIS selectivity is screwing up queries and forcing it to choose a
spatial index when it's inappropriate
-----------------------+---------------------------
  Reporter:  robe      |      Owner:  pramsey
      Type:  defect    |     Status:  new
  Priority:  critical  |  Milestone:  PostGIS 3.4.5
 Component:  postgis   |    Version:  3.5.x
Resolution:            |   Keywords:
-----------------------+---------------------------
Comment (by robe):

 Okay another datapoint with the full nodes table on my windows box
 pg18beta3 seems to replicate the same poor values I was seeing on Debian
 Trixie 3.6 install.
 {{{
 SELECT postgis_full_version(), version();
 vacuum (analyze,verbose) nodes; --ran 3 times
 SELECT _postgis_stats('nodes', 'geom','2');
 SELECT _postgis_selectivity ('nodes', 'geom',
 ST_GeomFromEWKT('SRID=4326;POLYGON((6.11982 52.6121955,6.11982
 53.2038323,7.0927397 53.2038323,7.0927397 52.6121955,6.11982
 52.6121955))'), '2');

 }}}


 {{{
  POSTGIS="3.6.0dev 3.6.0rc1-28-g82b37e677" [EXTENSION] PGSQL="180"
 GEOS="3.14.0beta3-CAPI-1.20.2" PROJ="8.2.1 NETWORK_ENABLED=OFF
 URL_ENDPOINT=https://cdn.proj.org
 USER_WRITABLE_DIRECTORY=C:\Users\lr\AppData\Local/proj DATABA
 SE_PATH=C:\ming64gcc81\projects\proj\rel-
 proj-8.2.1w64gcc81/share/proj/proj.db" (compiled against PROJ 8.2.1)
 LIBXML="2.12.5" LIBJSON="0.12" LIBPROTOBUF="1.2.1" WAGYU="0.5.0
 (Internal)" (core procs from "3.6.0rc1 3.6.0beta1-4
 6-g785b53cd7" need upgrade) | PostgreSQL 18beta3 on x86_64-windows,
 compiled by msvc-19.44.35213, 64-bit

    _postgis_stats

 -------------------------------------------------
 {"ndims":2,"size":[-2147483648,-2147483648],"extent":{"min":[3.33641,50.7274],"max":[7.22925,53.7169]},"table_features":133464968,
 "sample_features":30000,"not_null_features":30000,"histogram_features":30000,"histogram_cells":0,"cells_covered":0}


  _postgis_selectivity
 ----------------------
                     0
 (1 row)

 }}}


 On my pg17 windows 3.4


 {{{
 POSTGIS="3.4.5dev 3.4.4-2-ga02435c79" [EXTENSION] PGSQL="170"
 GEOS="3.14.0beta3-CAPI-1.20.2" (compiled against GEOS 3.13.0) PROJ="8.2.1
 NETWORK_ENABLED=OFF URL_ENDPOINT=https://cdn.proj.org
 USER_WRITABLE_DIRECTORY=C:\Users\l
 r\AppData\Local/proj DATABASE_PATH=C:\ming64gcc81\projects\proj\rel-
 proj-8.2.1w64gcc81/share/proj/proj.db" LIBXML="2.12.5" LIBJSON="0.12"
 LIBPROTOBUF="1.2.1" WAGYU="0.5.0 (Internal)" | PostgreSQL 17.5 on
 x86_64-windows, compi
 led by msvc-19.44.35209, 64-bit
 (1 row)

            _postgis_stats

 -----------------------------------------------------------
 {"ndims":2,"size":[1,1],"extent":{"min":[3.35288,50.7182],"max":[7.24345,53.5632]},"table_features":133635512,"sample_features":30000
 ,"not_null_features":30000,"histogram_features":30000,"histogram_cells":1,"cells_covered":3
 0000}
 (1 row)


 SELECT _postgis_selectivity ('nodes', 'geom',
 ST_GeomFromEWKT('SRID=4326;POLYGON((6.11982 52.6121955,6.11982
 53.2038323,7.0927397 53.2038323,7.0927397 52.6121955,6.11982
 52.6121955))'), '2');
  _postgis_selectivity
 ----------------------
   0.05200462239583333
 (1 row)
 }}}

 If I upgrade this to 3.5.2


 {{{
 SELECT postgis_extensions_upgrade('3.5.2');
 SELECT postgis_full_version(), version();
 vacuum (analyze,verbose) nodes; --ran 3 times
 SELECT _postgis_stats('nodes', 'geom','2');
 SELECT _postgis_selectivity ('nodes', 'geom',
 ST_GeomFromEWKT('SRID=4326;POLYGON((6.11982 52.6121955,6.11982
 53.2038323,7.0927397 53.2038323,7.0927397 52.6121955,6.11982
 52.6121955))'), '2');
 }}}


 It goes bad.

 {{{

  POSTGIS="3.5.2 3.5.1-6-ge07da2483" [EXTENSION] PGSQL="170"
 GEOS="3.14.0beta3-CAPI-1.20.2" (compiled against GEOS 3.13.0) PROJ="8.2.1
 NETWORK_ENABLED=OFF URL_ENDPOINT=https://cdn.proj.org
 USER_WRITABLE_DIRECTORY=C:\Users\lr\A
 ppData\Local/proj DATABASE_PATH=C:\ming64gcc81\projects\proj\rel-
 proj-8.2.1w64gcc81/share/proj/proj.db" (compiled against PROJ 8.2.1)
 LIBXML="2.12.5" LIBJSON="0.12" LIBPROTOBUF="1.2.1" WAGYU="0.5.0
 (Internal)" | PostgreSQL 17
 .5 on x86_64-windows, compiled by msvc-19.44.35209, 64-bit
 (1 row)

 _postgis_stats
 ----------------------
 {"ndims":2,"size":[-2147483648,-2147483648],"extent":{"min":[3.29959,50.7385],"max":[7.23308,53.5535]}
 ,"table_features":133389656,
 "sample_features":30000,"not_null_features":30000,"histogram_features":29998,"histogram_cells"
 :0,"cells_covered":0}

  _postgis_selectivity
 ----------------------
                     0
 }}}

 So I can't tell if this is just luck of the draw or what
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5984#comment:11>
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.