explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XmSS

Settings
# exclusive inclusive rows x rows loops node
1. 5.067 123,253.254 ↑ 66.7 3 1

GroupAggregate (cost=21,716,131.57..21,727,809.57 rows=200 width=36) (actual time=123,253.246..123,253.254 rows=3 loops=1)

  • Group Key: trips.commodity
2. 0.055 123,248.187 ↑ 83,246.4 7 1

Sort (cost=21,716,131.57..21,717,588.38 rows=582,725 width=44) (actual time=123,248.186..123,248.187 rows=7 loops=1)

  • Sort Key: trips.commodity
  • Sort Method: quicksort Memory: 25kB
3. 0.016 123,248.132 ↑ 83,246.4 7 1

Subquery Scan on trips (cost=21,539,413.06..21,660,328.49 rows=582,725 width=44) (actual time=123,242.381..123,248.132 rows=7 loops=1)

4. 7.862 123,248.116 ↑ 83,246.4 7 1

HashAggregate (cost=21,539,413.06..21,602,055.99 rows=582,725 width=52) (actual time=123,242.378..123,248.116 rows=7 loops=1)

  • Group Key: road_movements.trip_id
5.          

CTE boundaries

6. 0.000 0.000 ↓ 0.0 0

Seq Scan on v_transit_boundaries (cost=0.00..1,218.40 rows=3,404 width=78,632) (never executed)

7. 0.000 123,240.254 ↑ 1,689.5 3,167 1

Nested Loop Left Join (cost=143,930.16..21,471,312.89 rows=5,350,541 width=40) (actual time=3,810.993..123,240.254 rows=3,167 loops=1)

  • Join Filter: NULL::boolean
8. 317.953 123,371.554 ↑ 1,689.5 3,167 1

Gather (cost=143,930.16..20,936,258.79 rows=5,350,541 width=40) (actual time=3,810.984..123,371.554 rows=3,167 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
9. 4.987 123,053.601 ↑ 2,113.2 633 5 / 5

Parallel Hash Join (cost=142,930.16..20,400,204.69 rows=1,337,635 width=40) (actual time=13,071.512..123,053.601 rows=633 loops=5)

  • Hash Cond: (road_segments.link_id = v_transit_roads.link_id)
10. 2.225 121,525.027 ↑ 2,113.2 633 5 / 5

Parallel Hash Join (cost=70,557.29..20,324,320.33 rows=1,337,635 width=48) (actual time=11,545.591..121,525.027 rows=633 loops=5)

  • Hash Cond: (road_segments.trip_id = road_movements.trip_id)
11. 14,383.965 120,611.651 ↑ 42,843.2 633 5 / 5

Parallel Hash Join (cost=5,730.17..20,188,299.04 rows=27,119,777 width=24) (actual time=10,633.068..120,611.651 rows=633 loops=5)

  • Hash Cond: (road_segments.trip_id = road_segments_1.trip_id)
12. 106,227.318 106,227.318 ↑ 1.3 118,754,299 5 / 5

Parallel Seq Scan on road_segments (cost=0.00..19,792,384.00 rows=148,442,880 width=16) (actual time=0.026..106,227.318 rows=118,754,299 loops=5)

13. 0.154 0.368 ↑ 2,188.0 1 5 / 5

Parallel Hash (cost=5,505.90..5,505.90 rows=2,188 width=8) (actual time=0.367..0.368 rows=1 loops=5)

  • Buckets: 8,192 Batches: 1 Memory Usage: 160kB
14. 0.188 0.214 ↑ 2,188.0 1 5 / 5

Parallel Bitmap Heap Scan on road_segments road_segments_1 (cost=32.64..5,505.90 rows=2,188 width=8) (actual time=0.211..0.214 rows=1 loops=5)

  • Recheck Cond: (link_id = 1,201,710,493)
15. 0.026 0.026 ↑ 750.1 7 1 / 5

Bitmap Index Scan on idx_road_segments_link_id (cost=0.00..31.33 rows=5,251 width=0) (actual time=0.131..0.131 rows=7 loops=1)

  • Index Cond: (link_id = 1,201,710,493)
16. 78.175 911.151 ↑ 1.6 116,545 5 / 5

Parallel Hash (cost=45,559.58..45,559.58 rows=187,976 width=40) (actual time=911.150..911.151 rows=116,545 loops=5)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 51,744kB
17. 832.976 832.976 ↑ 1.6 116,545 5 / 5

Parallel Seq Scan on road_movements (cost=0.00..45,559.58 rows=187,976 width=40) (actual time=33.107..832.976 rows=116,545 loops=5)

18. 75.151 1,523.587 ↑ 1.2 174,222 5 / 5

Parallel Hash (cost=50,050.73..50,050.73 rows=217,777 width=8) (actual time=1,523.587..1,523.587 rows=174,222 loops=5)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 42,336kB
19. 1,448.436 1,448.436 ↑ 1.2 174,222 5 / 5

Parallel Seq Scan on v_transit_roads (cost=0.00..50,050.73 rows=217,777 width=8) (actual time=0.358..1,448.436 rows=174,222 loops=5)

20. 0.000 0.000 ↓ 0.0 0 3,167

Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.000..0.000 rows=0 loops=3,167)

  • One-Time Filter: false