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

 I was mistaken 3.4 looks a little messed up too but not as messed up as
 3.5 and 3.6.


 {{{
 -- I did a analyze check before upgrade from 3.3 to 3.4 and it looked okay
  UPDATE pg_extension SET extversion = 'ANY' WHERE extname = 'postgis';
  ALTER EXTENSION postgis UPDATE TO "3.4.2";
 SELECT postgis_full_version();
 -- POSTGIS="3.4.2 c19ce56" [EXTENSION] PGSQL="160" GEOS="3.15.0dev-
 CAPI-1.21.0" (compiled against GEOS 3.12.1)

 analyze nodes;

 SELECT _postgis_stats('nodes', 'geom','2');
 --
 {"ndims":2,"size":[1,1],"extent":{"min":[3.35799,50.7174],"max":[7.22904,53.6371]},"table_features":133295792,"sample_features":30000,"not_
 null_features":30000,"histogram_features":30000,"histogram_cells":1,"cells_covered":30000}

 SELECT _postgis_selectivity ('nodes', 'geom',
 ST_GeomFromEWKT('SRID=4326;POLYGON((6.11982 52.6121955,6.11982
 53.2038323,7.0927
 397 53.2038323,7.0927397 52.6121955,6.11982 52.6121955))'), '2');

 -- 0.050928621419270834
 (1 row)

 }}}

 But since the selectivity is not 0, it used the right plans which is why I
 probably didn't notice.
 I reran analyze again to see if it would improve the selectivity
 correctness.

 and change was not that different


 {{{
 0.05216378173828125

 {"ndims":2,"size":[1,1],"extent":{"min":[3.3428,50.7134],"max":[7.22912,53.5528]},"table_features":133346496,"sample_features":30000,"not_n
 ull_features":30000,"histogram_features":30000,"histogram_cells":1,"cells_covered":30000}
 }}}


 Then I upgraded to 3.5.4


 {{{
  ALTER EXTENSION postgis UPDATE TO "3.5.4";

 SELECT postgis_full_version();

 -- POSTGIS="3.5.4 3.5.4" [EXTENSION] PGSQL="160" GEOS="3.15.0dev-
 CAPI-1.21.0" PROJ="9.6.0 NETWORK_ENABLED=OFF
 URL_ENDPOINT=https://cdn.proj.or
 g 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" LIBP
 ROTOBUF="1.5.1" WAGYU="0.5.0 (Internal)"
 (1 row)

 analyze nodes;
 SELECT _postgis_stats('nodes', 'geom','2') ;

 --
 {"ndims":2,"size":[-2147483648,-2147483648],"extent":{"min":[2.87586,50.6011],"max":[7.25607,53.8865]},"table_features":133392376,"sample_f
 eatures":30000,"not_null_features":30000,"histogram_features":30000,"histogram_cells":0,"cells_covered":0}
 (1 row)


 SELECT _postgis_selectivity ('nodes', 'geom',
 ST_GeomFromEWKT('SRID=4326;POLYGON((6.11982 52.6121955,6.11982
 53.2038323,7.092
 7397 53.2038323,7.0927397 52.6121955,6.11982 52.6121955))'), '2');

 -- 0
 (1 row)

 ALTER EXTENSION postgis UPDATE TO "3.7.0dev";
 analyze nodes;

 SELECT _postgis_stats('nodes', 'geom','2');

 --
 {"ndims":2,"size":[96,102],"extent":{"min":[3.35549,50.7377],"max":[7.26203,53.5679]},"table_features":133439184,"sample_features":30000,"n
 ot_null_features":30000,"histogram_features":29998,"histogram_cells":9792,"cells_covered":29998}

  SELECT _postgis_selectivity ('nodes', 'geom',
 ST_GeomFromEWKT('SRID=4326;POLYGON((6.11982 52.6121955,6.11982
 53.2038323,7.0927
 397 53.2038323,7.0927397 52.6121955,6.11982 52.6121955))'), '2');

 --   0.07374775973540136
 }}}

 and as you see real crapola starts at 3.5 and then upgrading to 3.7, looks
 good again.
   So I think we need to backport to 3.4, 3.5, and 3.6 and then we can
 close this ticket out.
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5984#comment:18>
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.