explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MtAC

Settings
# exclusive inclusive rows x rows loops node
1. 10.714 19,234.939 ↑ 100.7 13,555 1

HashAggregate (cost=2,086,465.05..2,103,523.69 rows=1,364,691 width=44) (actual time=19,229.890..19,234.939 rows=13,555 loops=1)

  • Group Key: dispersion_details.storekeeper_id, storekeepers.order_level_id, order_levels.first_debt_limit, (COALESCE((sum(dispersion_details.total_value)), '0'::numeric) + COALESCE(sum_canceled.total, '0'::numeric))
2. 15.442 19,224.225 ↑ 100.7 13,555 1

Merge Right Join (cost=1,987,517.16..2,072,818.14 rows=1,364,691 width=44) (actual time=19,200.433..19,224.225 rows=13,555 loops=1)

  • Merge Cond: (sum_canceled.storekeeper_id = dispersion_details.storekeeper_id)
  • Filter: ((COALESCE((sum(dispersion_details.total_value)), '0'::numeric) + COALESCE(sum_canceled.total, '0'::numeric)) < order_levels.first_debt_limit)
  • Rows Removed by Filter: 33792
3. 0.426 2.777 ↑ 1.5 1,011 1

Sort (cost=231.16..235.05 rows=1,558 width=36) (actual time=2.612..2.777 rows=1,011 loops=1)

  • Sort Key: sum_canceled.storekeeper_id
  • Sort Method: quicksort Memory: 72kB
4. 0.107 2.351 ↑ 1.5 1,011 1

Subquery Scan on sum_canceled (cost=113.49..148.54 rows=1,558 width=36) (actual time=2.055..2.351 rows=1,011 loops=1)

5. 0.855 2.244 ↑ 1.5 1,011 1

HashAggregate (cost=113.49..132.96 rows=1,558 width=36) (actual time=2.054..2.244 rows=1,011 loops=1)

  • Group Key: paid_lots.storekeeper_id
6. 1.389 1.389 ↑ 1.0 1,633 1

Index Scan using paid_lots_should_retry_index on paid_lots (cost=0.43..105.24 rows=1,649 width=11) (actual time=0.016..1.389 rows=1,633 loops=1)

  • Index Cond: (should_retry = true)
  • Filter: should_retry
7. 22.682 19,206.006 ↑ 11.1 47,347 1

Sort (cost=1,987,286.00..1,988,599.89 rows=525,555 width=44) (actual time=19,197.804..19,206.006 rows=47,347 loops=1)

  • Sort Key: dispersion_details.storekeeper_id
  • Sort Method: quicksort Memory: 5004kB
8. 7,625.993 19,183.324 ↑ 11.1 47,347 1

HashAggregate (cost=1,925,524.14..1,932,093.58 rows=525,555 width=44) (actual time=19,163.834..19,183.324 rows=47,347 loops=1)

  • Group Key: dispersion_details.storekeeper_id, storekeepers.order_level_id, order_levels.first_debt_limit
9. 4,149.168 11,557.331 ↓ 1.0 17,139,037 1

Hash Join (cost=15,767.46..1,754,663.12 rows=17,086,102 width=17) (actual time=127.421..11,557.331 rows=17,139,037 loops=1)

  • Hash Cond: (dispersion_details.storekeeper_id = storekeepers.id)
10. 7,281.826 7,281.826 ↓ 1.0 17,139,037 1

Seq Scan on dispersion_details (cost=0.00..1,503,961.76 rows=17,086,102 width=9) (actual time=0.015..7,281.826 rows=17,139,037 loops=1)

  • Filter: (NOT was_dispersed)
  • Rows Removed by Filter: 45935584
11. 31.550 126.337 ↑ 1.0 136,476 1

Hash (cost=14,061.51..14,061.51 rows=136,476 width=12) (actual time=126.337..126.337 rows=136,476 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 7921kB
12. 27.433 94.787 ↑ 1.0 136,476 1

Hash Join (cost=1.20..14,061.51 rows=136,476 width=12) (actual time=0.033..94.787 rows=136,476 loops=1)

  • Hash Cond: (storekeepers.order_level_id = order_levels.id)
13. 67.342 67.342 ↑ 1.0 136,476 1

Seq Scan on storekeepers (cost=0.00..12,183.76 rows=136,476 width=8) (actual time=0.004..67.342 rows=136,476 loops=1)

14. 0.007 0.012 ↑ 1.0 9 1

Hash (cost=1.09..1.09 rows=9 width=8) (actual time=0.012..0.012 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.005 0.005 ↑ 1.0 9 1

Seq Scan on order_levels (cost=0.00..1.09 rows=9 width=8) (actual time=0.004..0.005 rows=9 loops=1)

Planning time : 0.904 ms
Execution time : 19,250.926 ms