explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mOlD

Settings
# exclusive inclusive rows x rows loops node
1. 0.068 4,188.747 ↓ 1.2 736 1

Subquery Scan on feats (cost=1,583,440.20..1,585,995.75 rows=622 width=36) (actual time=4,175.946..4,188.747 rows=736 loops=1)

  • Functions: 110
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 8.972 ms, Inlining 35.411 ms, Optimization 320.391 ms, Emission 185.923 ms, Total 550.697 ms
2. 7.981 4,188.679 ↓ 1.2 736 1

GroupAggregate (cost=1,583,440.20..1,585,989.53 rows=622 width=207) (actual time=4,175.944..4,188.679 rows=736 loops=1)

  • Group Key: network_points.geom, network_points.layer_id, network_points.plan_id, network_points.attributes, (CASE WHEN (('network_point'::text) ~~ '%point'::text) THEN 'point'::text WHEN (('network_point'::text) ~~ '%line'::text) THEN 'line'::text ELSE 'polygon'::text END)
3. 21.603 4,180.698 ↑ 9.6 15,080 1

Sort (cost=1,583,440.20..1,583,802.84 rows=145,054 width=191) (actual time=4,175.903..4,180.698 rows=15,080 loops=1)

  • Sort Key: network_points.geom, network_points.layer_id, network_points.plan_id, network_points.attributes, (CASE WHEN (('network_point'::text) ~~ '%point'::text) THEN 'point'::text WHEN (('network_point'::text) ~~ '%line'::text) THEN 'line'::text ELSE 'polygon'::text END)
  • Sort Method: external merge Disk: 2,384kB
4. 18.430 4,159.095 ↑ 9.6 15,080 1

Gather (cost=1,068.56..1,555,322.06 rows=145,054 width=191) (actual time=300.350..4,159.095 rows=15,080 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
5. 2.606 4,140.665 ↑ 11.3 7,540 2 / 2

Nested Loop (cost=68.56..1,539,816.66 rows=85,326 width=191) (actual time=274.387..4,140.665 rows=7,540 loops=2)

6. 0.443 277.433 ↑ 1.1 3,206 2 / 2

Parallel Append (cost=68.14..6,117.02 rows=3,658 width=175) (actual time=271.813..277.433 rows=3,206 loops=2)

7. 276.599 276.975 ↑ 1.1 3,206 2 / 2

Parallel Bitmap Heap Scan on network_points (cost=68.14..6,076.72 rows=3,636 width=165) (actual time=271.797..276.975 rows=3,206 loops=2)

  • Recheck Cond: (layer_id = ANY ('{9}'::integer[]))
  • Heap Blocks: exact=1,634
8. 0.377 0.377 ↓ 1.0 6,413 1 / 2

Bitmap Index Scan on network_points_layer_id_idx (cost=0.00..66.59 rows=6,182 width=0) (actual time=0.753..0.753 rows=6,413 loops=1)

  • Index Cond: (layer_id = ANY ('{9}'::integer[]))
9. 0.003 0.003 ↓ 0.0 0 1 / 2

Parallel Index Scan using nonnetwork_polygons_layer_id_idx on nonnetwork_polygons (cost=0.42..15.12 rows=19 width=2,086) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: (layer_id = ANY ('{9}'::integer[]))
10. 0.003 0.003 ↓ 0.0 0 1 / 2

Parallel Index Scan using network_lines_layer_id_idx on network_lines (cost=0.42..2.64 rows=1 width=251) (actual time=0.005..0.006 rows=0 loops=1)

  • Index Cond: (layer_id = ANY ('{9}'::integer[]))
11. 0.003 0.003 ↓ 0.0 0 1 / 2

Parallel Index Scan using nonnetwork_points_layer_id_idx on nonnetwork_points (cost=0.42..1.63 rows=1 width=206) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: (layer_id = ANY ('{9}'::integer[]))
12. 0.006 0.006 ↓ 0.0 0 1 / 2

Parallel Index Scan using nonnetwork_lines_layer_id_idx on nonnetwork_lines (cost=0.41..2.62 rows=1 width=382) (actual time=0.013..0.013 rows=0 loops=1)

  • Index Cond: (layer_id = ANY ('{9}'::integer[]))
13. 9.620 3,860.626 ↑ 3.0 2 6,413 / 2

Append (cost=0.42..419.10 rows=6 width=1,888) (actual time=1.147..1.204 rows=2 loops=6,413)

14. 3.207 3.207 ↓ 0.0 0 6,413 / 2

Index Scan using network_points_layer_id_idx on network_points network_points_1 (cost=0.42..27.64 rows=1 width=48) (actual time=0.001..0.001 rows=0 loops=6,413)

  • Index Cond: (layer_id = 613)
  • Filter: st_intersects(network_points.geom, geom)
15. 3.207 3.207 ↓ 0.0 0 6,413 / 2

Index Scan using network_lines_layer_id_idx on network_lines network_lines_1 (cost=0.42..27.64 rows=1 width=124) (actual time=0.001..0.001 rows=0 loops=6,413)

  • Index Cond: (layer_id = 613)
  • Filter: st_intersects(network_points.geom, geom)
16. 3.207 3.207 ↓ 0.0 0 6,413 / 2

Index Scan using nonnetwork_points_layer_id_idx on nonnetwork_points nonnetwork_points_1 (cost=0.42..26.63 rows=1 width=48) (actual time=0.001..0.001 rows=0 loops=6,413)

  • Index Cond: (layer_id = 613)
  • Filter: st_intersects(network_points.geom, geom)
17. 3.207 3.207 ↓ 0.0 0 6,413 / 2

Index Scan using nonnetwork_lines_layer_id_idx on nonnetwork_lines nonnetwork_lines_1 (cost=0.41..27.63 rows=1 width=102) (actual time=0.001..0.001 rows=0 loops=6,413)

  • Index Cond: (layer_id = 613)
  • Filter: st_intersects(network_points.geom, geom)
18. 211.629 3,838.181 ↑ 1.0 2 6,413 / 2

Bitmap Heap Scan on nonnetwork_polygons nonnetwork_polygons_1 (cost=257.30..309.53 rows=2 width=1,888) (actual time=1.140..1.197 rows=2 loops=6,413)

  • Recheck Cond: (layer_id = 613)
  • Filter: st_intersects(network_points.geom, geom)
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=11,620
19. 1.982 3,626.552 ↓ 0.0 0 6,413 / 2

BitmapAnd (cost=257.30..257.30 rows=2 width=0) (actual time=1.131..1.131 rows=0 loops=6,413)

20. 484.181 484.181 ↑ 1.8 15 6,413 / 2

Bitmap Index Scan on nonnetwork_polygons_geom_idx (cost=0.00..1.07 rows=27 width=0) (actual time=0.151..0.151 rows=15 loops=6,413)

  • Index Cond: (geom && network_points.geom)
21. 3,140.388 3,140.388 ↓ 1.0 23,600 6,306 / 2

Bitmap Index Scan on nonnetwork_polygons_layer_id_idx (cost=0.00..250.15 rows=23,324 width=0) (actual time=0.996..0.996 rows=23,600 loops=6,306)

  • Index Cond: (layer_id = 613)
Execution time : 4,195.075 ms