explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EoJ1

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 3,477.410 ↑ 200.0 1 1

Sort (cost=740,433.98..740,434.48 rows=200 width=68) (actual time=3,477.410..3,477.410 rows=1 loops=1)

  • Sort Key: metrics.commod_l2
  • Sort Method: quicksort Memory: 25kB
  • Functions: 72
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 9.580 ms, Inlining 319.145 ms, Optimization 1397.452 ms, Emission 866.690 ms, Total 2592.867 ms
2.          

CTE metrics

3. 0.000 3,477.239 ↑ 256.0 6 1

Finalize GroupAggregate (cost=728,138.41..728,589.00 rows=1,536 width=60) (actual time=3,477.227..3,477.239 rows=6 loops=1)

  • Group Key: mv_transit_roads_aggregated.commod_l2, mv_transit_roads_aggregated.orig_category, mv_transit_roads_aggregated.dest_category
4. 262.336 3,659.802 ↑ 170.7 18 1

Gather Merge (cost=728,138.41..728,496.84 rows=3,072 width=60) (actual time=3,477.196..3,659.802 rows=18 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 0.037 3,397.466 ↑ 256.0 6 3 / 3

Sort (cost=727,138.39..727,142.23 rows=1,536 width=60) (actual time=3,397.464..3,397.466 rows=6 loops=3)

  • Sort Key: mv_transit_roads_aggregated.commod_l2, mv_transit_roads_aggregated.orig_category, mv_transit_roads_aggregated.dest_category
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
6. 94.698 3,397.429 ↑ 256.0 6 3 / 3

Partial HashAggregate (cost=727,041.74..727,057.10 rows=1,536 width=60) (actual time=3,397.419..3,397.429 rows=6 loops=3)

  • Group Key: mv_transit_roads_aggregated.commod_l2, mv_transit_roads_aggregated.orig_category, mv_transit_roads_aggregated.dest_category
7. 131.600 3,302.731 ↑ 1.2 107,303 3 / 3

Nested Loop Left Join (cost=5,671.75..723,886.91 rows=126,193 width=56) (actual time=977.410..3,302.731 rows=107,303 loops=3)

  • Join Filter: NULL::boolean
8. 2,271.087 2,312.704 ↑ 1.2 107,303 3 / 3

Parallel Bitmap Heap Scan on mv_transit_roads_aggregated (cost=5,671.75..722,624.98 rows=126,193 width=56) (actual time=115.196..2,312.704 rows=107,303 loops=3)

  • Recheck Cond: (commod_id = ANY ('{1}'::integer[]))
  • Heap Blocks: exact=35,892
9. 41.617 41.617 ↓ 1.1 321,910 1 / 3

Bitmap Index Scan on idx_mv_transit_roads_aggregated_v10d3_commod_id (cost=0.00..5,596.03 rows=302,863 width=0) (actual time=124.851..124.851 rows=321,910 loops=1)

  • Index Cond: (commod_id = ANY ('{1}'::integer[]))
10. 858.427 858.427 ↓ 0.0 0 321,910 / 3

Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.008..0.008 rows=0 loops=321,910)

  • One-Time Filter: false
11. 0.038 3,477.403 ↑ 200.0 1 1

HashAggregate (cost=11,821.84..11,837.34 rows=200 width=68) (actual time=3,477.403..3,477.403 rows=1 loops=1)

  • Group Key: metrics.commod_l2
12. 0.071 3,477.365 ↑ 1,532.2 4 1

HashAggregate (cost=10,933.13..11,055.71 rows=6,129 width=114) (actual time=3,477.323..3,477.365 rows=4 loops=1)

  • Group Key: metrics_1.orig_category, metrics.commod_l2
13. 0.009 3,477.294 ↑ 557.2 11 1

Nested Loop (cost=80.12..10,871.84 rows=6,129 width=98) (actual time=3,477.285..3,477.294 rows=11 loops=1)

  • Join Filter: (((metrics_1.orig_category)::text = (metrics.orig_category)::text) OR ((metrics_1.orig_category)::text = (metrics.dest_category)::text))
  • Rows Removed by Join Filter: 13
14. 3,477.231 3,477.231 ↑ 256.0 6 1

CTE Scan on metrics (cost=0.00..30.72 rows=1,536 width=156) (actual time=3,477.231..3,477.231 rows=6 loops=1)

15. 0.007 0.054 ↑ 100.0 4 6

Materialize (cost=80.12..90.12 rows=400 width=58) (actual time=0.008..0.009 rows=4 loops=6)

16. 0.008 0.047 ↑ 100.0 4 1

HashAggregate (cost=80.12..84.12 rows=400 width=58) (actual time=0.045..0.047 rows=4 loops=1)

  • Group Key: metrics_1.orig_category
17. 0.002 0.039 ↑ 80.0 5 1

Append (cost=34.56..79.12 rows=400 width=58) (actual time=0.026..0.039 rows=5 loops=1)

18. 0.012 0.027 ↑ 100.0 2 1

HashAggregate (cost=34.56..36.56 rows=200 width=58) (actual time=0.025..0.027 rows=2 loops=1)

  • Group Key: metrics_1.orig_category
19. 0.015 0.015 ↑ 256.0 6 1

CTE Scan on metrics metrics_1 (cost=0.00..30.72 rows=1,536 width=58) (actual time=0.001..0.015 rows=6 loops=1)

20. 0.009 0.010 ↑ 66.7 3 1

HashAggregate (cost=34.56..36.56 rows=200 width=58) (actual time=0.008..0.010 rows=3 loops=1)

  • Group Key: metrics_2.dest_category
21. 0.001 0.001 ↑ 256.0 6 1

CTE Scan on metrics metrics_2 (cost=0.00..30.72 rows=1,536 width=58) (actual time=0.000..0.001 rows=6 loops=1)

Planning time : 0.599 ms