explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PEa5

Settings
# exclusive inclusive rows x rows loops node
1. 17.843 333.301 ↑ 94,106.0 1 1

GroupAggregate (cost=294,961.43..298,960.94 rows=94,106 width=68) (actual time=333.300..333.301 rows=1 loops=1)

  • Group Key: orig_rank.rank, dest_rank.rank, mv_transit_roads_aggregated.commod_l2, mv_transit_roads_aggregated.orig_category, mv_transit_roads_aggregated.dest_category
  • Functions: 30
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 4.974 ms, Inlining 0.000 ms, Optimization 1.642 ms, Emission 23.138 ms, Total 29.753 ms
2. 40.629 315.458 ↑ 1.1 85,591 1

Sort (cost=294,961.43..295,196.70 rows=94,106 width=40) (actual time=311.221..315.458 rows=85,591 loops=1)

  • Sort Key: orig_rank.rank, dest_rank.rank, mv_transit_roads_aggregated.commod_l2, mv_transit_roads_aggregated.orig_category, mv_transit_roads_aggregated.dest_category
  • Sort Method: quicksort Memory: 9,759kB
3. 18.063 274.829 ↑ 1.1 85,591 1

Nested Loop Left Join (cost=1,823.28..287,187.34 rows=94,106 width=40) (actual time=67.007..274.829 rows=85,591 loops=1)

  • Join Filter: NULL::boolean
4. 17.028 256.766 ↑ 1.1 85,591 1

Hash Left Join (cost=1,823.28..286,246.28 rows=94,106 width=40) (actual time=67.004..256.766 rows=85,591 loops=1)

  • Hash Cond: ((mv_transit_roads_aggregated.dest_category)::text = (dest_rank.enterprise_category)::text)
5. 52.139 239.717 ↑ 1.1 85,591 1

Hash Left Join (cost=1,792.58..285,967.41 rows=94,106 width=38) (actual time=66.968..239.717 rows=85,591 loops=1)

  • Hash Cond: ((mv_transit_roads_aggregated.orig_category)::text = (orig_rank.enterprise_category)::text)
6. 138.345 162.340 ↑ 1.1 85,591 1

Bitmap Heap Scan on mv_transit_roads_aggregated (cost=1,761.88..285,688.55 rows=94,106 width=36) (actual time=41.705..162.340 rows=85,591 loops=1)

  • Recheck Cond: (commod_id = ANY ('{83}'::integer[]))
  • Heap Blocks: exact=83,741
7. 23.995 23.995 ↑ 1.1 85,591 1

Bitmap Index Scan on idx_mv_transit_roads_aggregated_v10d3_commod_id (cost=0.00..1,738.36 rows=94,106 width=0) (actual time=23.995..23.995 rows=85,591 loops=1)

  • Index Cond: (commod_id = ANY ('{83}'::integer[]))
8. 0.019 25.238 ↑ 102.2 9 1

Hash (cost=19.20..19.20 rows=920 width=60) (actual time=25.237..25.238 rows=9 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
9. 25.219 25.219 ↑ 102.2 9 1

Seq Scan on enterprise_category_rank orig_rank (cost=0.00..19.20 rows=920 width=60) (actual time=25.213..25.219 rows=9 loops=1)

10. 0.003 0.021 ↑ 102.2 9 1

Hash (cost=19.20..19.20 rows=920 width=60) (actual time=0.021..0.021 rows=9 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
11. 0.018 0.018 ↑ 102.2 9 1

Seq Scan on enterprise_category_rank dest_rank (cost=0.00..19.20 rows=920 width=60) (actual time=0.017..0.018 rows=9 loops=1)

12. 0.000 0.000 ↓ 0.0 0 85,591

Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.000..0.000 rows=0 loops=85,591)

  • One-Time Filter: false
Planning time : 0.518 ms