explain.depesz.com

PostgreSQL's explain analyze made readable

Result: h14Z

Settings
# exclusive inclusive rows x rows loops node
1. 3.778 30,704.963 ↑ 654.7 2,080 1

HashAggregate (cost=1,486,646.35..1,503,667.30 rows=1,361,676 width=44) (actual time=30,702.271..30,704.963 rows=2,080 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. 2.721 30,701.185 ↑ 654.7 2,080 1

Merge Right Join (cost=1,387,917.03..1,473,029.59 rows=1,361,676 width=44) (actual time=30,697.528..30,701.185 rows=2,080 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: 4787
3. 0.409 2.762 ↑ 1.5 1,011 1

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

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

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

5. 0.884 2.262 ↑ 1.5 1,011 1

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

  • Group Key: paid_lots.storekeeper_id
6. 1.378 1.378 ↑ 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.015..1.378 rows=1,633 loops=1)

  • Index Cond: (should_retry = true)
  • Filter: should_retry
7. 3.144 30,695.702 ↑ 76.4 6,867 1

Sort (cost=1,387,685.87..1,388,996.86 rows=524,394 width=44) (actual time=30,694.896..30,695.702 rows=6,867 loops=1)

  • Sort Key: dispersion_details.storekeeper_id
  • Sort Method: quicksort Memory: 721kB
8. 3,083.743 30,692.558 ↑ 76.4 6,867 1

HashAggregate (cost=1,326,068.81..1,332,623.74 rows=524,394 width=44) (actual time=30,688.467..30,692.558 rows=6,867 loops=1)

  • Group Key: dispersion_details.storekeeper_id, storekeepers.order_level_id, order_levels.first_debt_limit
9. 2,817.729 27,608.815 ↓ 4.8 5,388,619 1

Hash Join (cost=191,064.96..1,314,918.42 rows=1,115,039 width=17) (actual time=1,365.914..27,608.815 rows=5,388,619 loops=1)

  • Hash Cond: (dispersion_details.storekeeper_id = storekeepers.id)
10. 23,659.473 24,760.537 ↑ 1.0 17,141,600 1

Bitmap Heap Scan on dispersion_details (cost=187,529.64..1,234,916.15 rows=17,417,751 width=9) (actual time=1,335.218..24,760.537 rows=17,141,600 loops=1)

  • Filter: (NOT was_dispersed)
  • Heap Blocks: exact=736593
11. 1,101.064 1,101.064 ↑ 1.0 17,141,600 1

Bitmap Index Scan on dispersion_dispersion_details_was_dispersed_id_index (cost=0.00..183,175.20 rows=17,417,751 width=0) (actual time=1,101.064..1,101.064 rows=17,141,600 loops=1)

  • Index Cond: (was_dispersed = false)
12. 2.661 30.549 ↓ 1.1 10,040 1

Hash (cost=3,426.06..3,426.06 rows=8,741 width=12) (actual time=30.549..30.549 rows=10,040 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 567kB
13. 2.819 27.888 ↓ 1.1 10,040 1

Hash Join (cost=1.62..3,426.06 rows=8,741 width=12) (actual time=0.051..27.888 rows=10,040 loops=1)

  • Hash Cond: (storekeepers.order_level_id = order_levels.id)
14. 25.054 25.054 ↓ 1.1 10,040 1

Index Scan using storekeepers_state_index on storekeepers (cost=0.42..3,304.67 rows=8,741 width=8) (actual time=0.018..25.054 rows=10,040 loops=1)

  • Index Cond: (state = 1)
  • Filter: is_active
  • Rows Removed by Filter: 1429
15. 0.007 0.015 ↑ 1.0 9 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.008 0.008 ↑ 1.0 9 1

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

Planning time : 0.995 ms
Execution time : 30,716.864 ms