1

In Postgresql 10.1 with PostGIS 2.4.1 I select line geometry distributed in space with the help of a 3D bounding box. These edges are then joined against the respective node information table to get more information of the points involved. Both the edge table and the node table have about 18,753,793 rows and will likely grow tenfold in the next year. In my bounding box query I use a CTE to compute all intersected edges, but the CTE Scan in the query plan below takes longer than I want it to. I also find that the estimates involving it (and also the the PostGIS edges) are often off by a factor of 10 or more. My statistics target is 1000, increasing it doesn't change things. Statistics are up to date.

I wonder if the the planner could come up with a better plan with more accurate statistics. When I use a TEMPORARY TABLE instead of the CTE, the estimates are better and the outer HashAggregate (see query plan below) is faster, the overall performance is roughly the same. I noticed though that the outer sequential scan on the temporary table says Buffers: local hit=28 and the CTE version does say Buffers: shared hit=5375. Is there a way to have the outer CTE Scan behave similarly in the CTE version or is this not really comparable?

And Is there any way of improving the CTE estimates, if they can help with performance at all?

A typical query takes about 115ms and looks like this:

WITH bb_edge AS (
    SELECT te.id
    FROM treenode_edge te 
    WHERE te.edge && ST_MakeEnvelope( -133095.0,  -55879.0, 239973.8, 176863.4)
      AND floatrange(ST_ZMin(te.edge),
         ST_ZMax(te.edge), '[]') && floatrange(82550.0, 82600.0, '[)')
      AND ST_3DDWithin(te.edge, ST_MakePolygon(ST_MakeLine(ARRAY[
          ST_MakePoint( -133095.0,   -55879.0,    82575.0),
          ST_MakePoint(239973.8,  -55879.0,    82575.0),
          ST_MakePoint(239973.8, 176863.4, 82575.0),
          ST_MakePoint( -133095.0,  176863.4, 82575.0),
          ST_MakePoint( -133095.0,   -55879.0,    82575.0)]::geometry[])),
          25.0)
      AND te.project_id = 1
)
SELECT
    t1.id,
    t1.parent_id,
    t1.location_x,
    t1.location_y,
    t1.location_z,
    t1.confidence,
    t1.radius,
    t1.skeleton_id,
    EXTRACT(EPOCH FROM t1.edition_time),
    t1.user_id
  FROM
    (SELECT id
        FROM bb_edge
      UNION (
        SELECT t.parent_id
        FROM bb_edge e
        JOIN treenode t
        ON t.id = e.id
        )
      UNION
      SELECT UNNEST(ARRAY[6429696, 1030658, 5140907, 6817796]::bigint[])
    ) all_nodes(id)
JOIN treenode t1
  ON all_nodes.id = t1.id;

Tables:

The treenode table is defiend as:

   Column     |           Type           | Collation | Nullable |               Default                
---------------+--------------------------+-----------+----------+--------------------------------------
 id            | bigint                   |           | not null | nextval('location_id_seq'::regclass)
 project_id    | integer                  |           | not null | 
 location_x    | real                     |           | not null | 
 location_y    | real                     |           | not null | 
 location_z    | real                     |           | not null | 
 editor_id     | integer                  |           | not null | 
 user_id       | integer                  |           | not null | 
 creation_time | timestamp with time zone |           | not null | now()
 edition_time  | timestamp with time zone |           | not null | now()
 skeleton_id   | integer                  |           | not null | 
 radius        | real                     |           | not null | 0
 confidence    | smallint                 |           | not null | 5
 parent_id     | bigint                   |           |          | 
 txid          | bigint                   |           |          | txid_current()
Indexes:
    "treenode_pkey" PRIMARY KEY, btree (id)
    "creation_time_index" btree (creation_time)
    "treenode_creation_time_index" btree (creation_time)
    "treenode_edition_time_index" btree (edition_time)
    "treenode_location_x_index" btree (project_id, location_x)
    "treenode_location_y_index" btree (project_id, location_y)
    "treenode_location_z_index" btree (project_id, location_z)
    "treenode_parent_id" btree (parent_id)
    "treenode_project_id_skeleton_id_index" btree (project_id, skeleton_id)
    "treenode_project_id_user_id_index" btree (project_id, user_id)
    "treenode_skeleton_id_index" btree (skeleton_id)

And treenode_edge is defined as:

   Column   |         Type          | Collation | Nullable | Default 
------------+-----------------------+-----------+----------+---------
 id         | bigint                |           | not null | 
 project_id | integer               |           | not null | 
 edge       | geometry(LineStringZ) |           |          | 
Indexes:
    "treenode_edge_pkey" PRIMARY KEY, btree (id)
    "treenode_edge_2d_gist" gist (edge)
    "treenode_edge_gix" gist (edge gist_geometry_ops_nd)
    "treenode_edge_project_id_index" btree (project_id)
    "treenode_edge_z_range_gist" gist (floatrange(st_zmin(edge::box3d), st_zmax(edge::box3d), '[]'::text))

Query plans

This is a query plan for the query above:

 Nested Loop  (cost=14156.20..17949.63 rows=850 width=50) (actual time=67.462..111.054 rows=9515 loops=1)
   Buffers: shared hit=68381
   CTE bb_edge
     ->  Bitmap Heap Scan on treenode_edge te  (cost=148.71..12460.99 rows=375 width=8) (actual time=7.028..29.633 rows=6226 loops=1)
           Recheck Cond: (floatrange(st_zmin((edge)::box3d), st_zmax((edge)::box3d), '[]'::text) && '[82550,82600)'::floatrange)
           Filter: ((edge && '0103000000010000000500000000000000383F00C100000000E048EBC000000000383F00C133333333FB960541666666662E4B0D4133333333FB960541666666662E4B0D4100000000E048EBC000000000383F00C100000000E048EBC0'::geometry) AND (edge && '0103000080010000000500000000000000004000C100000000004CEBC0000000006027F44000000000004000C133333333C3970541000000006027F44066666666F64B0D4133333333C397054100000000802AF44066666666F64B0D4100000000004CEBC000000000802AF44000000000004000C100000000004CEBC0000000006027F440'::geometry) AND (project_id = 1) AND ('0103000080010000000500000000000000383F00C100000000E048EBC000000000F028F440666666662E4B0D4100000000E048EBC000000000F028F440666666662E4B0D4133333333FB96054100000000F028F44000000000383F00C133333333FB96054100000000F028F44000000000383F00C100000000E048EBC000000000F028F440'::geometry && st_expand(edge, '25'::double precision)) AND _st_3ddwithin(edge, '0103000080010000000500000000000000383F00C100000000E048EBC000000000F028F440666666662E4B0D4100000000E048EBC000000000F028F440666666662E4B0D4133333333FB96054100000000F028F44000000000383F00C133333333FB96054100000000F028F44000000000383F00C100000000E048EBC000000000F028F440'::geometry, '25'::double precision))
           Heap Blocks: exact=5115
           Buffers: shared hit=5375
           ->  Bitmap Index Scan on treenode_edge_z_range_gist  (cost=0.00..148.62 rows=5627 width=0) (actual time=6.271..6.271 rows=6226 loops=1)
                 Index Cond: (floatrange(st_zmin((edge)::box3d), st_zmax((edge)::box3d), '[]'::text) && '[82550,82600)'::floatrange)
                 Buffers: shared hit=260
   ->  HashAggregate  (cost=1694.77..1703.27 rows=850 width=8) (actual time=67.445..69.925 rows=9516 loops=1)
         Group Key: bb_edge.id
         Buffers: shared hit=30293
         ->  Append  (cost=0.00..1692.64 rows=850 width=8) (actual time=7.031..62.249 rows=12456 loops=1)
               Buffers: shared hit=30293
               ->  CTE Scan on bb_edge  (cost=0.00..7.50 rows=375 width=8) (actual time=7.031..31.854 rows=6226 loops=1)
                     Buffers: shared hit=5375
               ->  Nested Loop  (cost=0.44..1676.13 rows=375 width=8) (actual time=0.020..29.011 rows=6226 loops=1)
                     Buffers: shared hit=24918
                     ->  CTE Scan on bb_edge e  (cost=0.00..7.50 rows=375 width=8) (actual time=0.001..1.082 rows=6226 loops=1)
                     ->  Index Scan using treenode_pkey on treenode t  (cost=0.44..4.45 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=6226)
                           Index Cond: (id = e.id)
                           Buffers: shared hit=24918
               ->  ProjectSet  (cost=0.00..0.52 rows=100 width=8) (actual time=0.009..0.010 rows=4 loops=1)
                     ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)
   ->  Index Scan using treenode_pkey on treenode t1  (cost=0.44..4.44 rows=1 width=50) (actual time=0.003..0.003 rows=1 loops=9516)
         Index Cond: (id = bb_edge.id)
         Buffers: shared hit=38088
 Planning time: 1.618 ms
 Execution time: 111.884 ms

If I replace the CTE with a TEMPORARY TABLE (Instead of WITH ... it would be CREATE TEMPORARY TABLE bb_edge ON COMMIT DROP AS ...) I get two query plans, first for creating the temporary table:

 Bitmap Heap Scan on treenode_edge te  (cost=148.71..12458.81 rows=375 width=8) (actual time=6.936..28.972 rows=6226 loops=1)
   Recheck Cond: (floatrange(st_zmin((edge)::box3d), st_zmax((edge)::box3d), '[]'::text) && '[82550,82600)'::floatrange)
   Filter: ((edge && '0103000000010000000500000000000000383F00C100000000E048EBC000000000383F00C133333333FB960541666666662E4B0D4133333333FB960541666666662E4B0D4100000000E048EBC000000000383F00C100000000E048EBC0'::geometry) AND (edge && '0103000080010000000500000000000000004000C100000000004CEBC0000000006027F44000000000004000C133333333C3970541000000006027F44066666666F64B0D4133333333C397054100000000802AF44066666666F64B0D4100000000004CEBC000000000802AF44000000000004000C100000000004CEBC0000000006027F440'::geometry) AND (project_id = 1) AND ('0103000080010000000500000000000000383F00C100000000E048EBC000000000F028F440666666662E4B0D4100000000E048EBC000000000F028F440666666662E4B0D4133333333FB96054100000000F028F44000000000383F00C133333333FB96054100000000F028F44000000000383F00C100000000E048EBC000000000F028F440'::geometry && st_expand(edge, '25'::double precision)) AND _st_3ddwithin(edge, '0103000080010000000500000000000000383F00C100000000E048EBC000000000F028F440666666662E4B0D4100000000E048EBC000000000F028F440666666662E4B0D4133333333FB96054100000000F028F44000000000383F00C133333333FB96054100000000F028F44000000000383F00C100000000E048EBC000000000F028F440'::geometry, '25'::double precision))
   Heap Blocks: exact=5115
   Buffers: shared hit=5375
   ->  Bitmap Index Scan on treenode_edge_z_range_gist  (cost=0.00..148.61 rows=5626 width=0) (actual time=6.179..6.179 rows=6226 loops=1)
         Index Cond: (floatrange(st_zmin((edge)::box3d), st_zmax((edge)::box3d), '[]'::text) && '[82550,82600)'::floatrange)
         Buffers: shared hit=260
 Planning time: 1.291 ms
 Execution time: 33.778 ms

And for the concatenation and joining:

 Nested Loop  (cost=28129.22..29024.28 rows=200 width=50) (actual time=36.208..79.080 rows=9585 loops=1)
   Buffers: shared hit=63287, local hit=56
   ->  HashAggregate  (cost=28128.78..28130.78 rows=200 width=8) (actual time=36.190..38.490 rows=9586 loops=1)
         Group Key: bb_edge.id
         Buffers: shared hit=24918, local hit=56
         ->  HashAggregate  (cost=27841.77..27969.33 rows=12756 width=8) (actual time=32.001..33.482 rows=9586 loops=1)
               Group Key: bb_edge.id
               Buffers: shared hit=24918, local hit=56
               ->  Append  (cost=0.00..27809.88 rows=12756 width=8) (actual time=0.013..28.144 rows=12771 loops=1)
                     Buffers: shared hit=24918, local hit=56
                     ->  Seq Scan on bb_edge  (cost=0.00..91.28 rows=6328 width=8) (actual time=0.012..0.672 rows=6226 loops=1)
                           Buffers: local hit=28
                     ->  Nested Loop  (cost=0.44..27590.52 rows=6328 width=8) (actual time=0.023..26.007 rows=6226 loops=1)
                           Buffers: shared hit=24918, local hit=28
                           ->  Seq Scan on bb_edge e  (cost=0.00..91.28 rows=6328 width=8) (actual time=0.007..0.974 rows=6226 loops=1)
                                 Buffers: local hit=28
                           ->  Index Scan using treenode_pkey on treenode t  (cost=0.44..4.35 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=6226)
                                 Index Cond: (id = e.id)
                                 Buffers: shared hit=24918
                     ->  ProjectSet  (cost=0.00..0.52 rows=100 width=8) (actual time=0.007..0.043 rows=319 loops=1)
                           ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)
   ->  Index Scan using treenode_pkey on treenode t1  (cost=0.44..4.46 rows=1 width=50) (actual time=0.003..0.003 rows=1 loops=9586)
         Index Cond: (id = bb_edge.id)
         Buffers: shared hit=38369
 Planning time: 0.880 ms
 Execution time: 79.871 ms

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.