explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bC7A

Settings
# exclusive inclusive rows x rows loops node
1. 34.824 4,399.201 ↑ 1.0 1 1

Aggregate (cost=27,804,833.56..27,804,833.57 rows=1 width=40) (actual time=4,399.201..4,399.201 rows=1 loops=1)

  • Filter: (st_linemerge(st_union(sly.line_geom)) IS NOT NULL)
2.          

CTE related_streets

3. 0.005 3.444 ↑ 223,566.7 3 1

ProjectSet (cost=1,647.88..5,319.97 rows=670,700 width=4) (actual time=3.417..3.444 rows=3 loops=1)

4. 0.398 3.439 ↑ 6,707.0 1 1

GroupAggregate (cost=1,647.88..1,849.09 rows=6,707 width=64) (actual time=3.413..3.439 rows=1 loops=1)

  • Group Key: (CASE WHEN (sly_1.street_name IS NOT NULL) THEN (sly_1.street_name)::text ELSE (sly_1.osm_ids[1])::text END)
  • Filter: (182 = ANY (array_agg(sly_1.street_library_id)))
  • Rows Removed by Filter: 623
5. 1.297 3.041 ↑ 8.5 792 1

Sort (cost=1,647.88..1,664.65 rows=6,707 width=36) (actual time=2.982..3.041 rows=792 loops=1)

  • Sort Key: (CASE WHEN (sly_1.street_name IS NOT NULL) THEN (sly_1.street_name)::text ELSE (sly_1.osm_ids[1])::text END)
  • Sort Method: quicksort Memory: 76kB
6. 1.744 1.744 ↑ 8.5 792 1

Seq Scan on streets_library sly_1 (cost=0.00..1,221.61 rows=6,707 width=36) (actual time=0.010..1.744 rows=792 loops=1)

7. 0.368 4,364.377 ↑ 80.8 592 1

Nested Loop (cost=15,095.53..27,792,642.00 rows=47,810 width=52) (actual time=2,251.919..4,364.377 rows=592 loops=1)

8. 17.882 4,362.825 ↑ 80.8 592 1

Hash Join (cost=15,095.25..27,777,672.76 rows=47,810 width=56) (actual time=2,251.908..4,362.825 rows=592 loops=1)

  • Hash Cond: (sly.street_library_id = related_streets.street_library_id)
9. 426.330 4,341.486 ↑ 1.0 92,668 1

Seq Scan on segments_library sly (cost=0.00..27,761,794.62 rows=95,619 width=52) (actual time=0.240..4,341.486 rows=92,668 loops=1)

  • Filter: ((direction = 0) AND ((SubPlan 3) IS TRUE))
  • Rows Removed by Filter: 351800
10.          

SubPlan (for Seq Scan)

11. 3,915.156 3,915.156 ↓ 0.0 0 186,436

Index Scan using idx_records_matched_position on records r (cost=0.29..62.39 rows=1 width=0) (actual time=0.021..0.021 rows=0 loops=186,436)

  • Index Cond: (matched_position && st_expand(sly.line_geom, '2.79999999999999996e-05'::double precision))
  • Filter: ((sly.line_geom && st_expand(matched_position, '2.79999999999999996e-05'::double precision)) AND _st_dwithin(sly.line_geom, matched_position, '2.79999999999999996e-05'::double precision) AND (NOT (SubPlan 2)))
  • Rows Removed by Filter: 0
12.          

SubPlan (for Index Scan)

13. 0.000 0.000 ↓ 0.0 0 93,102

Index Scan using idx_records_created_at on records (cost=0.42..2.64 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=93,102)

  • Index Cond: (created_at = r.is_latest_until)
14. 0.003 3.457 ↑ 66.7 3 1

Hash (cost=15,092.75..15,092.75 rows=200 width=4) (actual time=3.457..3.457 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.007 3.454 ↑ 66.7 3 1

HashAggregate (cost=15,090.75..15,092.75 rows=200 width=4) (actual time=3.453..3.454 rows=3 loops=1)

  • Group Key: related_streets.street_library_id
16. 3.447 3.447 ↑ 223,566.7 3 1

CTE Scan on related_streets (cost=0.00..13,414.00 rows=670,700 width=4) (actual time=3.419..3.447 rows=3 loops=1)

17. 1.184 1.184 ↑ 1.0 1 592

Index Scan using streets_library_pkey on streets_library sl (cost=0.28..0.31 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=592)

  • Index Cond: (street_library_id = sly.street_library_id)
Planning time : 0.929 ms
Execution time : 4,399.357 ms