[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 I retested and confirmed the same behavior.  So I think we just need
 to backport the changes to 3.6 and 3.5.  I'll double-check 3.4.  I know
 3.3 wasn't impacted by this cause I started with a 3.3 database as my
 template.


 {{{
 SELECT pg_terminate_backend(pid) FROM pg_stat_activity where datname =
 'osm_nl_33';

 CREATE DATABASE osm_nl_test TEMPLATE osm_nl_33;
 \c osm_nl_test
 analyze nodes;

 SELECT postgis_full_version();
 -- POSTGIS="3.3.9dev 3.3.7-53-g9a6079a60" [EXTENSION] PGSQL="160"
 GEOS="3.15.0dev-CAPI-1.21.0" PROJ="9.6.0" LIBXML="2.9.14" LIBJSON="0.18"
 LIBPROTOBUF="1.5.1" WAGYU="0.5.0 (Internal)"
 SELECT _postgis_stats('nodes', 'geom','2') ;
 --
 {"ndims":2,"size":[96,103],"extent":{"min":[3.32454,50.725],"max":[7.22931,53.8452]},"table_features":133481792,"sample_features":30000,"not_null_features":30000,"histogram_features":29999,"histogram_cells":9888,"cells_covered":29999}
 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');
 -- 0.0735942255590728

 UPDATE pg_extension SET extversion = 'ANY';
 ALTER EXTENSION postgis UPDATE TO "3.6.0";
 analyze nodes;

 SELECT postgis_full_version();
 -- POSTGIS="3.6.0 3.6.0" [EXTENSION] PGSQL="160" GEOS="3.15.0dev-
 CAPI-1.21.0" PROJ="9.6.0 NETWORK_ENABLED=OFF
 URL_ENDPOINT=https://cdn.proj.org USER_WRITABLE_DIRECTORY=/tmp/proj
 DATABASE_PATH=/usr/share/proj/proj.db" (compiled against PROJ 9.6.0)
 LIBXML="2.9.14" LIBJSON="0.18" LIBPROTOBUF="1.5.1" WAGYU="0.5.0
 (Internal)"

 SELECT _postgis_stats('nodes', 'geom','2') ;
 --
 {"ndims":2,"size":[-2147483648,-2147483648],"extent":{"min":[3.34292,50.7306],"max":[7.23458,53.553]},"table_features":133608672,"sample_features":30000,"not_null_features":30000,"histogram_features":30000,"histogram_cells":0,"cells_covered":0}

 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');
 -- 0

 ALTER EXTENSION postgis UPDATE TO "3.7.0dev";
 SELECT postgis_full_version();
 -- POSTGIS="3.7.0dev 3.6.0rc2-175-gb185e01e9" [EXTENSION] PGSQL="160"
 GEOS="3.15.0dev-CAPI-1.21.0" PROJ="9.6.0 NETWORK_ENABLED=OFF
 URL_ENDPOINT=https://cdn.proj.org USER_WRITABLE_DIRECTORY=/tmp/proj
 DATABASE_PATH=/usr/share/proj/proj.db" (compiled against PROJ 9.6.0)
 LIBXML="2.9.14" LIBJSON="0.18" LIBPROTOBUF="1.5.1" WAGYU="0.5.0
 (Internal)"

 analyze nodes;

 SELECT _postgis_stats('nodes', 'geom','2') ;
 --
 {"ndims":2,"size":[98,101],"extent":{"min":[3.20275,50.7382],"max":[7.26312,53.5539]},"table_features":133429664,"sample_features":30000,"not_null_features":30000,"histogram_features":29999,"histogram_cells":9898,"cells_covered":29999}

 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');
 -- 0.07800694427262457
 }}}
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5984#comment:17>
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.