[postgis-tickets] [PostGIS] #3675: Relationship functions not using an index in some cases

[PostGIS]

#3675: Relationship functions not using an index in some cases
----------------------+---------------------------
  Reporter:  robe     |      Owner:  robe
      Type:  defect   |     Status:  new
  Priority:  blocker  |  Milestone:  PostGIS 2.3.2
 Component:  postgis  |    Version:  2.3.x
Resolution:           |   Keywords:
----------------------+---------------------------

Comment (by jdsterritt):

 After upgrading our test environment to PostgreSQL 9.6 and PostGIS 2.3. We
 noticed numerous issues where the indexes are not being utilized. I wanted
 to provide these additional test cases. We run these statements on large
 datasets, and the processing time has significantly increased.

 {{{
 CREATE TABLE parent_geom(id serial);
 SELECT AddGeometryColumn('parent_geom','geom','3857','polygon',2);
 CREATE INDEX idx_parent_geom ON parent_geom USING gist(geom);

 CREATE TABLE child_geom(id serial, parent_id bigint);
 SELECT AddGeometryColumn('child_geom','geom','3857','polygon',2);
 CREATE INDEX idx_child_geom ON child_geom USING gist(geom);

 CREATE TABLE track(id serial);
 SELECT AddGeometryColumn('track','geom','3857','linestring',2);
 CREATE INDEX idx_track_geom ON track USING gist(geom);

 CREATE TABLE stop(id serial);
 SELECT AddGeometryColumn('stop','geom','3857','point',2);
 CREATE INDEX idx_point_geom ON stop USING gist(geom);


 INSERT INTO parent_geom (geom) VALUES
 (ST_SetSRID(ST_GeomFromText('POLYGON((-9201000 5180000,-9201000
 5181000,-9200000 5181000,-9200000 5180000,-9201000 5180000))'),3857));
 INSERT INTO child_geom (geom) VALUES
 (ST_SetSRID(ST_GeomFromText('POLYGON((-9200500 5180400,-9200500
 5180500,-9200400 5180500,-9200400 5180400,-9200500 5180400))'),3857));
 INSERT INTO track(geom) VALUES
 (ST_SetSRID(ST_GeomFromText('LINESTRING(-9200744 5180268, -9200117
 5181610)'),3857));
 INSERT INTO stop(geom) VALUES (ST_SetSRID(ST_GeomFromText('POINT(-9200744
 5180268)'),3857));
 }}}

 '''Using ST_Centroid'''
 {{{
 EXPLAIN
 UPDATE child_geom as c SET parent_id = p.id FROM parent_geom as p WHERE
 ST_Intersects(ST_Centroid(c.geom), p.geom);
 }}}

 '''Query Plans[[BR]]
 PostgreSQL 9.6.1, compiled by Visual C++ build 1800, 64-bit[[BR]]
 PostGIS - 2.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1 [[BR]]'''
 Update on child_geom c  (cost=0.00..736731.49 rows=478367 width=56)[[BR]]
 ->  Nested Loop  (cost=0.00..736731.49 rows=478367 width=56)[[BR]]
 Join Filter: st_intersects(st_centroid(c.geom), p.geom)[[BR]]
 ->  Seq Scan on parent_geom p  (cost=0.00..22.70 rows=1270 width=42)[[BR]]
 ->  Materialize  (cost=0.00..26.95 rows=1130 width=42)[[BR]]
 ->  Seq Scan on child_geom c  (cost=0.00..21.30 rows=1130 width=42)[[BR]]

 '''PostgreSQL 9.5.5 on x86_64-pc-linux-gnu, (Red Hat 4.8.5-4),
 64-bit[[BR]]
 PostGIS - 2.2 USE_GEOS=1 USE_PROJ=1 USE_STATS=1[[BR]]'''

 Update on child_geom c  (cost=0.15..549.57 rows=478 width=52)[[BR]]
 ->  Nested Loop  (cost=0.15..549.57 rows=478 width=52)[[BR]]
 ->  Seq Scan on child_geom c  (cost=0.00..21.30 rows=1130 width=42)[[BR]]
 ->  Index Scan using idx_parent_geom on parent_geom p  (cost=0.15..0.46
 rows=1 width=42)[[BR]]
 Index Cond: (st_centroid(c.geom) && geom)[[BR]]
 Filter: _st_intersects(st_centroid(c.geom), geom)[[BR]]

 '''Using ST_SnapToGrid'''

 {{{
 EXPLAIN
 SELECT * FROM track as t, parent_geom as p WHERE
 ST_Intersects(ST_SnapToGrid(t.geom, 0.1),p.geom);
 }}}

 '''Query Plans'''

 '''PostgreSQL 9.6.1, compiled by Visual C++ build 1800, 64-bit[[BR]]
 2.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1[[BR]]
 '''
 Nested Loop  (cost=0.00..524241.08 rows=537633 width=72)[[BR]]
 Join Filter: st_intersects(st_snaptogrid(t.geom, '0'::double precision,
 '0'::double precision, '0.1'::double precision, '0.1'::double precision),
 p.geom)[[BR]]
 ->  Seq Scan on track t  (cost=0.00..22.70 rows=1270 width=36)[[BR]]
 ->  Materialize  (cost=0.00..29.05 rows=1270 width=36)[[BR]]
 ->  Seq Scan on parent_geom p  (cost=0.00..22.70 rows=1270 width=36)[[BR]]

 '''PostgreSQL 9.5.5 on x86_64-pc-linux-gnu (Red Hat 4.8.5-4),
 64-bit'''[[BR]]
 '''2.2 USE_GEOS=1 USE_PROJ=1 USE_STATS=1'''[[BR]]
 Nested Loop  (cost=0.15..609.63 rows=538 width=72)[[BR]]
 ->  Seq Scan on track t  (cost=0.00..22.70 rows=1270 width=36)[[BR]]
 ->  Index Scan using idx_parent_geom on parent_geom p  (cost=0.15..0.45
 rows=1 width=36)[[BR]]
 Index Cond: (st_snaptogrid(t.geom, '0'::double precision, '0'::double
 precision, '0.1'::double precision, '0.1'::double precision) &&
 geom)[[BR]]
 Filter: _st_intersects(st_snaptogrid(t.geom, '0'::double precision,
 '0'::double precision, '0.1'::double precision, '0.1'::double precision),
 geom)[[BR]]

--
Ticket URL: <https://trac.osgeo.org/postgis/ticket/3675#comment:6>
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.