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

 Attached is a sample of the osm_nl data suffering the same issue:

 Both ST_Contains and ST_Intersects refuse to use the btree primary key
 index, though other samples of data make it use the primary key index


 {{{
 ANALYZE test_nodes;
 ANALYZE test_way_nodes;
 EXPLAIN ANALYZE SELECT wn.way_id FROM test_way_nodes AS wn INNER JOIN
 test_nodes AS n ON wn.node_id = n.id
         WHERE wn.node_id = 8646506472  AND
 ST_Contains(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);

 }}}

 yields

 {{{
 Nested Loop  (cost=0.57..29.11 rows=1 width=8) (actual time=0.029..0.030
 rows=0 loops=1)
    ->  Index Scan using idx_test_way_nodes_node_id on test_way_nodes wn
 (cost=0.29..8.30 rows=1 width=16) (actual time=0.028..0.028 rows=0
 loops=1)
          Index Cond: (node_id = '8646506472'::bigint)
    ->  Index Scan using idx_test_nodes_geom on test_nodes n
 (cost=0.28..20.80 rows=1 width=8) (never executed)
          Index Cond: (geom @
 '0103000020E61000000100000005000000D5A35F11B27A184013A1116C5C4E4A40D5A35F11B27A18402A2F432D179A4A40D529EA27F75E1C402A2F432D179A4A40D529EA27F75E1C4013A1116C5C4E4A40D5A35F11B27A184013A1116C5C4E4A40'::geometry)
          Filter: ((id = '8646506472'::bigint) AND
 st_contains('0103000020E61000000100000005000000D5A35F11B27A184013A1116C5C4E4A40D5A35F11B27A18402A2F432D179A4A40D529EA27F75E1C402A2F432D179A4A40D529EA27F75E1C4013A1116C5C4E4A40D5A35F11B27A184013A1116C5C4E4A40
 '::geometry, geom))
  Planning Time: 2.915 ms
  Execution Time: 0.151 ms
 (8 rows)
 }}}


 Unfortunately with this small piece of data you can't tell by the speed
 anything is wrong and the planner evidentally is smart enough to not
 execute teh index but it prevent the primary key index from being used.

 Compare the above to this one,

 {{{
 EXPLAIN ANALYZE SELECT wn.way_id FROM test_way_nodes AS wn INNER JOIN
 test_nodes AS n ON wn.node_id = n.id
         WHERE wn.node_id = 8646506472  AND
 _ST_Contains(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);


 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Nested Loop  (cost=0.85..29.39 rows=1 width=8) (actual time=0.069..0.070
 rows=0 loops=1)
    ->  Index Scan using idx_test_way_nodes_node_id on test_way_nodes wn
 (cost=0.42..8.44 rows=1 width=16) (actual time=0.069..0.069 rows=0
 loops=1)
          Index Cond: (node_id = '8646506472'::bigint)
    ->  Index Scan using test_nodes_pkey on test_nodes n  (cost=0.42..20.94
 rows=1 width=8) (never executed)
          Index Cond: (id = '8646506472'::bigint)
          Filter:
 _st_contains('0103000020E61000000100000005000000D5A35F11B27A184013A1116C5C4E4A40D5A35F11B27A18402A2F432D179A4A40D529EA27F75E1C402A2F432D179A4A40D529EA27F75E1C4013A1116C5C4E4A40D5A35F11B27A184013A1116C5C4E4A40'::geometry,
 geom)
  Planning Time: 22.233 ms
  Execution Time: 0.175 ms
 (8 rows)
 }}}
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5984#comment:1>
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.