explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YP9B

Settings
# exclusive inclusive rows x rows loops node
1. 4.237 6,609.310 ↑ 9.6 15,080 1

Nested Loop (cost=8,393.25..4,823,348.79 rows=145,054 width=20) (actual time=222.197..6,609.310 rows=15,080 loops=1)

  • Functions: 54
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 7.513 ms, Inlining 4.739 ms, Optimization 132.804 ms, Emission 76.810 ms, Total 221.866 ms
2.          

CTE target_features

3. 2.527 228.682 ↓ 1.0 23,600 1

Append (cost=0.42..8,323.69 rows=23,328 width=1,888) (actual time=214.680..228.682 rows=23,600 loops=1)

4. 0.016 0.016 ↓ 0.0 0 1

Index Scan using network_points_layer_id_idx on network_points network_points_1 (cost=0.42..2.64 rows=1 width=48) (actual time=0.016..0.016 rows=0 loops=1)

  • Index Cond: (layer_id = 613)
5. 0.006 0.006 ↓ 0.0 0 1

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

  • Index Cond: (layer_id = 613)
6. 0.006 0.006 ↓ 0.0 0 1

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

  • Index Cond: (layer_id = 613)
7. 0.005 0.005 ↓ 0.0 0 1

Index Scan using nonnetwork_lines_layer_id_idx on nonnetwork_lines nonnetwork_lines_1 (cost=0.41..2.63 rows=1 width=102) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: (layer_id = 613)
8. 226.122 226.122 ↓ 1.0 23,600 1

Index Scan using nonnetwork_polygons_layer_id_idx on nonnetwork_polygons nonnetwork_polygons_1 (cost=0.42..8,197.51 rows=23,324 width=1,888) (actual time=214.646..226.122 rows=23,600 loops=1)

  • Index Cond: (layer_id = 613)
9. 256.673 256.673 ↓ 1.0 23,600 1

CTE Scan on target_features (cost=0.00..466.56 rows=23,328 width=48) (actual time=214.685..256.673 rows=23,600 loops=1)

10. 47.200 6,348.400 ↑ 5.0 1 23,600

Append (cost=69.56..206.34 rows=5 width=50) (actual time=0.229..0.269 rows=1 loops=23,600)

11. 401.200 3,138.800 ↑ 1.0 1 23,600

Bitmap Heap Scan on network_points (cost=69.56..95.68 rows=1 width=40) (actual time=0.124..0.133 rows=1 loops=23,600)

  • Recheck Cond: (layer_id = ANY ('{9}'::integer[]))
  • Filter: st_intersects(geom, target_features.geom)
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=23,460
12. 251.240 2,737.600 ↓ 0.0 0 23,600

BitmapAnd (cost=69.56..69.56 rows=1 width=0) (actual time=0.116..0.116 rows=0 loops=23,600)

13. 1,132.800 1,132.800 ↓ 3.4 282 23,600

Bitmap Index Scan on network_points_geom_idx (cost=0.00..1.18 rows=83 width=0) (actual time=0.048..0.048 rows=282 loops=23,600)

  • Index Cond: (geom && target_features.geom)
14. 1,353.560 1,353.560 ↓ 1.0 6,413 4,940

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

  • Index Cond: (layer_id = ANY ('{9}'::integer[]))
15. 23.600 23.600 ↓ 0.0 0 23,600

Index Scan using network_lines_layer_id_idx on network_lines (cost=0.42..27.64 rows=1 width=116) (actual time=0.001..0.001 rows=0 loops=23,600)

  • Index Cond: (layer_id = ANY ('{9}'::integer[]))
  • Filter: st_intersects(geom, target_features.geom)
16. 23.600 23.600 ↓ 0.0 0 23,600

Index Scan using nonnetwork_points_layer_id_idx on nonnetwork_points (cost=0.42..26.63 rows=1 width=40) (actual time=0.001..0.001 rows=0 loops=23,600)

  • Index Cond: (layer_id = ANY ('{9}'::integer[]))
  • Filter: st_intersects(geom, target_features.geom)
17. 23.600 23.600 ↓ 0.0 0 23,600

Index Scan using nonnetwork_lines_layer_id_idx on nonnetwork_lines (cost=0.41..27.63 rows=1 width=94) (actual time=0.001..0.001 rows=0 loops=23,600)

  • Index Cond: (layer_id = ANY ('{9}'::integer[]))
  • Filter: st_intersects(geom, target_features.geom)
18. 23.600 3,091.600 ↓ 0.0 0 23,600

Bitmap Heap Scan on nonnetwork_polygons (cost=2.62..28.73 rows=1 width=1,880) (actual time=0.131..0.131 rows=0 loops=23,600)

  • Recheck Cond: (layer_id = ANY ('{9}'::integer[]))
  • Filter: st_intersects(geom, target_features.geom)
19. 212.400 3,068.000 ↓ 0.0 0 23,600

BitmapAnd (cost=2.62..2.62 rows=1 width=0) (actual time=0.130..0.130 rows=0 loops=23,600)

20. 2,832.000 2,832.000 ↓ 15.6 422 23,600

Bitmap Index Scan on nonnetwork_polygons_geom_idx (cost=0.00..0.59 rows=27 width=0) (actual time=0.120..0.120 rows=422 loops=23,600)

  • Index Cond: (geom && target_features.geom)
21. 23.600 23.600 ↓ 0.0 0 23,600

Bitmap Index Scan on nonnetwork_polygons_layer_id_idx (cost=0.00..1.77 rows=33 width=0) (actual time=0.001..0.001 rows=0 loops=23,600)

  • Index Cond: (layer_id = ANY ('{9}'::integer[]))
Execution time : 6,619.528 ms