[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 that fixed the stats:

 After I ran


 {{{
 ALTER EXTENSION postgis UPDATE; -- which brought me from 3.6.0 to 3.7.0dev
 master
 analyze nodes;
 }}}


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

 Now returns:


 {{{
 {"ndims":2,"size":[97,102],"extent":{"min":[3.2287,50.7289],"max":[7.23561,53.513]},"table_features":133426320,"sample_features":30000,"not_null_features":30000,"histogram_features":30000,"histogram_cells":9894,"cells_covered":30000}
 }}}

 before it was giving me garbage numbers.


 {{{
 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');
 }}}


 Now instead of 0 gives the expected:

 0.07097181809551475


 and

 {{{
 EXPLAIN ANALYZE SELECT n.id FROM nodes AS n
         WHERE n.id = 8646506472  AND
 ST_Intersects(ST_GeomFromEWKT('SRID=4326;POLYGON((6.1198199
 52.612195500000006,6.1198199 53.2038323,7.0927397 53.2038323,7.0927397
 52.612195500000006,6.1198199 52.612195500000006))'), n.geom);
 }}}


 Is now no longer trying to use the spatial index:


 {{{
 Index Scan using pk_nodes on nodes n  (cost=0.57..21.08 rows=1 width=8)
 (actual time=71.461..71.461 rows=0 loops=1)
   Index Cond: (id = '8646506472'::bigint)
   Filter:
 st_intersects('0103000020E61000000100000005000000D5A35F11B27A184013A1116C5C4E4A40D5A35F11B27A18402A2F432D179A4A40D529EA27F75E1C402A2F432D179A4A40D529EA27F75E1C4013A1116C5C4E4A40D5A35F11B27A184013A1116C5C4E4A40'::geometry,
 geom)
   Rows Removed by Filter: 1
 Planning Time: 30.506 ms
 Execution Time: 71.571 ms
 }}}

 I'll do another test before and after to confirm it is all working, but I
 think you fixed it.
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5984#comment:16>
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.