I have a set of 7849 tridimensional segments and 71 tridimensional shapes. My goal is to count the number of intersections between the segments and the shapes using Postgis. The segments were modeled using a table of LinestringZ, the shapes were modeled using a table of PolyhedralSurfaceZ and the query that I used was:

Approach 1:

FROM geological_shapes as g, segments as s 
WHERE ST_3DIntersects(g.geom, s.geom);`
Time: 470,594 seconds (almost 8 minutes)

The performance of the query didn’t look very good, so I tried another approach by splitting each one of the PolyhedralSurfaceZ into PolygonZs (about 3 Million Triangles in total). After that, I tried the following queries:

Approach 2:

    SELECT DISTINCT s.id, t.shape_id 
    FROM trianglesfromshapes as t, segments as s 
    WHERE ST_3DIntersects(s.geom, t.geom)
) AS result;
Time: 121,592 seconds (about 2 minutes)

Approach 3:

FROM segments s, geological_shapes g 
WHERE g.geom && s.geom AND EXISTS(
FROM trianglesFromShapes t 
WHERE t.shape_id = g.id AND _ST_3DINTERSECTS(t.geom, s.geom));
Time: 701,059 seconds (about 11 minutes)

As you can see, the second approach was much faster than the first and third ones. This doesn’t make much sense to me, because the second query apparently does much more work than the others.

My reasoning is that the first version was slow because the index would store bounding boxes for each surface and, thus, during each intersection test the segment would be tested for intersection with all the triangles in the objects not filtered by the index. On the other hand, if the triangles were individually indexed many tests could be avoided. However this hypothesis isn’t compatible with the bad performance of the third approach, which indicates that there are still other causes of this behavior.

What could be causing such behavior? Are there any better ways to perform such a query or to represent this type of data (3d segments and 3d shapes)?

PS: all the geometries are indexed and, also, I have an index for the ids.

Edit: Added the EXPLAIN ANALYZE result of the second and third approaches.


QUERY PLAN                                                                     
 Aggregate  (cost=42513196.22..42513196.23 rows=1 width=8) (actual time=127059.3
17..127059.317 rows=1 loops=1)
   ->  Unique  (cost=42275981.91..42506232.89 rows=557066 width=16) (actual time
=126977.760..127057.875 rows=17898 loops=1)
         ->  Sort  (cost=42275981.91..42352732.24 rows=30700131 width=16) (actual time=126977.758..127024.522 rows=324059 loops=1)
               Sort Key: s.id, t.shape_id
               Sort Method: external merge  Disk: 8264kB
               ->  Gather  (cost=1000.15..36884171.80 rows=30700131 width=16) (actual time=341.852..126515.215 rows=324059 loops=1)
                     Workers Planned: 2
                     Workers Launched: 2
                     ->  Nested Loop  (cost=0.15..33813158.70 rows=12791721 width=16) (actual time=174.978..126161.558 rows=108020 loops=3)
                           ->  Parallel Seq Scan on trianglesfromshapes t  (cost=0.00..88165.11 rows=1339611 width=152) (actual time=41.287..2626.030 rows=1071684 loops=3)
                           ->  Index Scan using segments_geom_idx on segments s  (cost=0.15..25.17 rows=1 width=72) (actual time=0.112..0.115 rows=0 loops=3215052)
                                 Index Cond: (geom && t.geom)
                                 Filter: _st_3dintersects(geom, t.geom)
                                 Rows Removed by Filter: 28
 Planning time: 955.678 ms
 Execution time: 127172.717 ms


QUERY PLAN                                                                       
 Aggregate  (cost=1205527.20..1205527.21 rows=1 width=8) (actual time=1023328.980..1023328.980 rows=1 loops=1)
   ->  Hash Semi Join  (cost=216182.15..1205435.60 rows=36643 width=0) (actual time=31508.701..1023295.570 rows=17898 loops=1)
         Hash Cond: (g.id = t.shape_id)
         Join Filter: _st_3dintersects(t.geom, s.geom)
         Rows Removed by Join Filter: 555598249
         ->  Nested Loop  (cost=0.15..418.13 rows=109930 width=72) (actual time=17.850..8108.313 rows=22110 loops=1)
               ->  Seq Scan on geological_shapes g  (cost=0.00..1.71 rows=71 width=5071682) (actual time=0.019..0.116 rows=71 loops=1)
               ->  Index Scan using segments_geom_idx on segments s  (cost=0.15..5.86 rows=1 width=64) (actual time=0.040..0.318 rows=311 loops=71)
                     Index Cond: (g.geom && geom)
         ->  Hash  (cost=106919.67..106919.67 rows=3215067 width=152) (actual time=23204.472..23204.473 rows=3215052 loops=1)
               Buckets: 32768 (originally 32768)  Batches: 512 (originally 256)  Memory Usage: 60883kB
               ->  Seq Scan on trianglesfromshapes t  (cost=0.00..106919.67 rows=3215067 width=152) (actual time=0.009..16709.551 rows=3215052 loops=1)
 Planning time: 1.063 ms
 Execution time: 1023348.535 ms

  • 1
    Please add the EXPLAIN ANALYZE output for approaches 2 & 3 to your question body; that's your primary source of performance analysis. Do you have an N-D-Index on your 'shapes' geometries, or 2D-GIST? Did you run VACUUM ANALYZE one each table? The planner may choose an identical plan for appr. 2 & 3, but apparently doesn't...for dozens of possible reasons, including outdated table stats, but also grouping/merging behavior, and/or the pre-filter by bbox, since the EXISTS might get denied an index scan due to the pre-selection. – geozelot Jul 1 at 8:21
  • @geozelot I have a 2D-GIST index on the geometries. Would an N-D-Index be more efficient in this situation? I tried running VACUUM ANALYZE on the tables, but the time of the queries stayed basically the same. – m318 Jul 3 at 17:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.