[postgis-tickets] [PostGIS] #4635: ST_3DDwithin speed slow down between 2.5 and 3.0

[PostGIS]

#4635: ST_3DDwithin speed slow down between 2.5 and 3.0
----------------------+---------------------------
  Reporter:  robe     |      Owner:  pramsey
      Type:  defect   |     Status:  new
  Priority:  high     |  Milestone:  PostGIS 3.0.2
 Component:  postgis  |    Version:  3.0.x
Resolution:           |   Keywords:
----------------------+---------------------------

Comment (by tomka):

 I was just looking into this again and wanted to make the initial query
 and the resulting plans a bit simpler, like we did on the mailing list.
 Therefore I am basically reposting the last results from a few days ago
 into this ticket, so they don't get lost. The test was done in a database
 with 50 million geometry entries (simple lines) using 11.7/2.5.3,
 12.2/2.5.3 and 12.2/3.0.1. All databases had REINDEX run, VACUUM FULL was
 run as well and so was ANALYZE.

 This is relevant part of above query:

 {{{
 EXPLAIN (ANALYZE, BUFFERS)
 SELECT te.id, te.parent_id
 FROM treenode_edge te
 WHERE te.edge &&& ST_MakeLine(ARRAY[
     ST_MakePoint(471548.0, 330140.0, 160440.0),
     ST_MakePoint(542460.0, 290140.0, 160400.0)] ::geometry[])
 AND ST_3DDWithin(te.edge, ST_MakePolygon(ST_MakeLine(ARRAY[
     ST_MakePoint(471548.0,  290140.0,    160420.0),
     ST_MakePoint(542460.0, 290140.0,    160420.0),
     ST_MakePoint(542460.0, 330140.0, 160420.0),
     ST_MakePoint(471548.0,  330140.0, 160420.0),
     ST_MakePoint(471548.0,  290140.0,    160420.0)]::geometry[])),
     20.0)
 AND te.project_id = 1;
 }}}

 And these are the plans for the three different test setups. Since the
 result for 11/2.5 and 12/2.5 are basically the same (with all data in
 memory), I only post the 12/2.5 plan and the 12/3 plan, each yielding
 about 1000 result rows (difference due to very small difference in DB
 entries):

 12/2.5:
 {{{
   Index Scan using treenode_edge_gix on treenode_edge te
 (cost=0.42..391.68 rows=1 width=16) (actual time=0.936..27.404   rows=1005
 loops=1)
     Index Cond: (edge &&&
 '01020000800200000000000000F0C71C41000000007026144100000000C095034100000000F88D20410000000070B511410000000080940341'::geometry)
     Filter: ((edge &&
 '0103000080010000000500000000000000A0C71C410000000020B51141000000008094034100000000A0C71C4100000000C0261441000000008094034100000000208E204100000000C026144100000000C095034100000000208E20410000000020B5114100000000C095034100000000A0C71C410000000020B511410000
 000080940341'::geometry) AND (project_id = 1) AND
 ('0103000080010000000500000000000000F0C71C410000000070B51141000000002095034100000000F88D20410000000070B51141000000002095034100000000F88D20410000000070261441000000002095034100000000F0C71C410000000070261441000000002095034100000000F0C71C410000000070B51141000
 0000020950341'::geometry && st_expand(edge, '20'::double precision)) AND
 _st_3ddwithin(edge,
 '0103000080010000000500000000000000F0C71C410000000070B51141000000002095034100000000F88D20410000000070B51141000000002095034100000000F88D20410000000070261441000000002095034100000000F0C71C410000000070261441000000002095034100000000F0C71C410000000070B511410000
 000020950341'::geometry, '20'::double precision))
     Rows Removed by Filter: 5
     Buffers: shared hit=1370
   Planning Time: 0.782 ms
   Execution Time: 27.518 ms
 }}}

 12/3:
 {{{
   Index Scan using treenode_edge_gix on treenode_edge te
 (cost=0.67..28.71 rows=1 width=16) (actual time=0.730..163.358 rows=1004
 loops=1)
     Index Cond: ((edge &&&
 '01020000800200000000000000F0C71C41000000007026144100000000C095034100000000F88D20410000000070B511410000000080940341'::geometry)
 AND (edge &&&
 st_expand('0103000080010000000500000000000000F0C71C410000000070B51141000000002095034100000000F88D20410000000070B51141000000002095034100000000F88D20410000000070261441000000002095034100000000F0C71C410000000070261441000000002095034100000000F0C71C410000000070
 B511410000000020950341'::geometry, '20'::double precision)))
     Filter: ((project_id = 1) AND st_3ddwithin(edge,
 '0103000080010000000500000000000000F0C71C410000000070B51141000000002095034100000000F88D20410000000070B51141000000002095034100000000F88D20410000000070261441000000002095034100000000F0C71C410000000070261441000000002095034100000000F0C71C410000000070B511410000
 000020950341'::geometry, '20'::double precision))
     Rows Removed by Filter: 5
     Buffers: shared hit=6546
   Planning Time: 1.312 ms
   Execution Time: 163.471 ms
 }}}
 The only difference is that more pages are hit in 12/3.0 it seems
 (Buffers: shared hit
 1370 vs 6546). This difference becomes even more apparent after the VACUUM
 FULL in 12/3.0 (doesn't change for 11/3.0 after VACUUM FULL):
 {{{
   Index Scan using treenode_edge_gix on treenode_edge te
 (cost=0.67..28.71 rows=1 width=16) (actual time=2.673..206.723 rows=1004
 loops=1)
     Index Cond: ((edge &&&
 '01020000800200000000000000F0C71C41000000007026144100000000C095034100000000F88D20410000000070B511410000000080940341'::geometry)
 AND (edge &&&
 st_expand('0103000080010000000500000000000000F0C71C410000000070B51141000000002095034100000000F88D20410000000070B51141000000002095034100000000F88D20410000000070261441000000002095034100000000F0C71C410000000070261441000000002095034100000000F0C71C410000000070
 B511410000000020950341'::geometry, '20'::double precision)))
     Filter: ((project_id = 1) AND st_3ddwithin(edge,
 '0103000080010000000500000000000000F0C71C410000000070B51141000000002095034100000000F88D20410000000070B51141000000002095034100000000F88D20410000000070261441000000002095034100000000F0C71C410000000070261441000000002095034100000000F0C71C410000000070B511410000
 000020950341'::geometry, '20'::double precision))
     Rows Removed by Filter: 5
     Buffers: shared hit=8195
   Planning Time: 1.209 ms
   Execution Time: 207.005 ms
 }}}
 This reduces the noise a bit and and highlights the plan differences a bit
 better then my original query.

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