explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QN08

Settings
# exclusive inclusive rows x rows loops node
1. 18.442 47,371.699 ↑ 94,106.0 1 1

GroupAggregate (cost=294,961.43..298,960.94 rows=94,106 width=68) (actual time=47,371.698..47,371.699 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 3.024 ms, Inlining 0.000 ms, Optimization 1.608 ms, Emission 24.054 ms, Total 28.686 ms
2. 103.214 47,353.257 ↑ 1.1 85,591 1

Sort (cost=294,961.43..295,196.70 rows=94,106 width=40) (actual time=47,348.754..47,353.257 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. 55.533 47,250.043 ↑ 1.1 85,591 1

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

  • Join Filter: NULL::boolean
4. 45.356 47,194.510 ↑ 1.1 85,591 1

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

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

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

  • Hash Cond: ((mv_transit_roads_aggregated.orig_category)::text = (orig_rank.enterprise_category)::text)
6. 46,951.404 46,978.077 ↑ 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=43.787..46,978.077 rows=85,591 loops=1)

  • Recheck Cond: (commod_id = ANY ('{83}'::integer[]))
  • Heap Blocks: exact=83,741
7. 26.673 26.673 ↑ 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=26.673..26.673 rows=85,591 loops=1)

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

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

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

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

10. 0.005 0.024 ↑ 102.2 9 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
11. 0.019 0.019 ↑ 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.019 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 : 1.876 ms