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

 I wouldn't spend any time fiddling with btree_gist or other hacks. What we
 want to do is (a) show the behaviour on a tractable subset, which I think
 your attachment does (b) characterize the differences between a pg15/gis33
 system and a pg17/gis36 system.

 Using your test data, I think I have demonstrated that the behaviour
 difference is there. I have also run a _postgis_selectivity on both
 systems and gotten a value of 0 on *both*. This is different from what Bas
 reported.

 The relevant query, using the query box you identified, is this
 {{{
 select _postgis_selectivity(
   'test_nodes',
   'geom',
 '0103000020E61000000100000005000000D5A35F11B27A184013A1116C5C4E4A40D5A35F11B27A18402A2F432D179A4A40D529EA27F75E1C402A2F432D179A4A40D529EA27F75E1C4013A1116C5C4E4A40D5A35F11B27A184013A1116C5C4E4A40'::geometry);
 }}}
 Just running a count of all nodes contained by that box returns 42
 {{{
 select count(*)
 from test_nodes
 where
 st_contains('0103000020E61000000100000005000000D5A35F11B27A184013A1116C5C4E4A40D5A35F11B27A18402A2F432D179A4A40D529EA27F75E1C402A2F432D179A4A40D529EA27F75E1C4013A1116C5C4E4A40D5A35F11B27A184013A1116C5C4E4A40'::geometry,
 geom);
 }}}
 Compared to the total table size, that 42 nodes is about 6.8% of the
 total.

 Given that currently I am seeing a zero selectivity on both pg15 and pg17
 but different query plans I am currently suspicious of the Pg version as
 the determinant of the bad plan.
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5984#comment:5>
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.