explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Jtkb

Settings
# exclusive inclusive rows x rows loops node
1. 287.114 242,707.876 ↑ 1.0 1 1

Aggregate (cost=4,846,493.32..4,846,493.33 rows=1 width=32) (actual time=242,707.876..242,707.876 rows=1 loops=1)

2. 36.835 242,420.762 ↓ 4.2 30,066 1

Subquery Scan on q (cost=4,575,002.19..4,846,475.26 rows=7,223 width=44) (actual time=122,884.519..242,420.762 rows=30,066 loops=1)

3. 92,950.446 242,383.927 ↓ 4.2 30,066 1

GroupAggregate (cost=4,575,002.19..4,846,403.03 rows=7,223 width=253) (actual time=122,884.500..242,383.927 rows=30,066 loops=1)

  • Group Key: v_transit_roads.link_id, v_transit_roads.street_name, v_transit_roads.road_speed, v_transit_roads.road_rank, v_transit_roads.truck, v_transit_roads.surface, v_transit_roads.road_km, v_transit_roads.geometry
4.          

CTE boundaries

5. 0.088 0.088 ↑ 1.0 1 1

Index Scan using idx_v_transit_boundaries_code on v_transit_boundaries (cost=0.28..8.30 rows=1 width=75,373) (actual time=0.086..0.088 rows=1 loops=1)

  • Index Cond: (code = ANY ('{LGA_31000}'::text[]))
6. 102,702.508 149,433.481 ↓ 3.9 19,374,368 1

Sort (cost=4,574,993.90..4,587,308.14 rows=4,925,696 width=209) (actual time=122,884.175..149,433.481 rows=19,374,368 loops=1)

  • Sort Key: v_transit_roads.link_id, v_transit_roads.street_name, v_transit_roads.road_speed, v_transit_roads.road_rank, v_transit_roads.truck, v_transit_roads.surface, v_transit_roads.road_km, v_transit_roads.geometry
  • Sort Method: external merge Disk: 3110752kB
7. 10,798.194 46,730.973 ↓ 3.9 19,374,368 1

Nested Loop Left Join (cost=44,618.50..2,512,222.09 rows=4,925,696 width=209) (actual time=803.199..46,730.973 rows=19,374,368 loops=1)

8. 21,865.146 35,932.779 ↓ 3.9 19,374,368 1

Hash Left Join (cost=44,618.50..2,413,708.16 rows=4,925,696 width=209) (actual time=803.102..35,932.779 rows=19,374,368 loops=1)

  • Hash Cond: (road_segments.trip_id = road_movements.trip_id)
9. 3,790.129 13,295.776 ↓ 3.9 19,374,368 1

Nested Loop (cost=761.19..2,083,562.80 rows=4,925,696 width=193) (actual time=7.061..13,295.776 rows=19,374,368 loops=1)

10. 299.818 305.451 ↓ 4.2 30,066 1

Bitmap Heap Scan on v_transit_roads (cost=760.62..34,129.40 rows=7,223 width=185) (actual time=6.377..305.451 rows=30,066 loops=1)

  • Recheck Cond: (geometry && '0103000020110F00000100000005000000E17ADC72B5327041CDCCCC56F6E247C1E17ADC72B5327041EC51F818D67B48C185EB216BD1457041EC51F818D67B48C185EB216BD1457041CDCCCC56F6E247C1E17ADC72B5327041CDCCCC56F6E247C1'::geometry)
  • Filter: _st_intersects(geometry, '0103000020110F00000100000005000000E17ADC72B5327041CDCCCC56F6E247C1E17ADC72B5327041EC51F818D67B48C185EB216BD1457041EC51F818D67B48C185EB216BD1457041CDCCCC56F6E247C1E17ADC72B5327041CDCCCC56F6E247C1'::geometry)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=3969
11. 5.633 5.633 ↓ 1.4 30,067 1

Bitmap Index Scan on v_transit_roads_geometry_geom_idx (cost=0.00..758.81 rows=21,670 width=0) (actual time=5.633..5.633 rows=30,067 loops=1)

  • Index Cond: (geometry && '0103000020110F00000100000005000000E17ADC72B5327041CDCCCC56F6E247C1E17ADC72B5327041EC51F818D67B48C185EB216BD1457041EC51F818D67B48C185EB216BD1457041CDCCCC56F6E247C1E17ADC72B5327041CDCCCC56F6E247C1'::geometry)
12. 9,200.196 9,200.196 ↑ 8.1 644 30,066

Index Only Scan using idx_road_segments_key on road_segments (cost=0.57..231.50 rows=5,224 width=16) (actual time=0.009..0.306 rows=644 loops=30,066)

  • Index Cond: (link_id = v_transit_roads.link_id)
  • Heap Fetches: 0
13. 222.509 771.857 ↑ 1.0 582,725 1

Hash (cost=32,589.25..32,589.25 rows=582,725 width=32) (actual time=771.856..771.857 rows=582,725 loops=1)

  • Buckets: 65536 Batches: 16 Memory Usage: 2784kB
14. 549.348 549.348 ↑ 1.0 582,725 1

Seq Scan on road_movements (cost=0.00..32,589.25 rows=582,725 width=32) (actual time=0.025..549.348 rows=582,725 loops=1)

15. 0.000 0.000 ↑ 1.0 1 19,374,368

CTE Scan on boundaries (cost=0.00..0.02 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=19,374,368)

Planning time : 6.903 ms