explain.depesz.com

PostgreSQL's explain analyze made readable

Result: v9fi : test2

Settings
# exclusive inclusive rows x rows loops node
1. 9.311 15,661.408 ↑ 99.7 13,577 1

HashAggregate (cost=1,798,286.41..1,815,201.33 rows=1,353,193 width=44) (actual time=15,657.075..15,661.408 rows=13,577 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.083 15,652.097 ↑ 99.7 13,577 1

Merge Right Join (cost=1,700,172.13..1,784,754.48 rows=1,353,193 width=44) (actual time=15,631.153..15,652.097 rows=13,577 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: 33865
3. 0.325 2.474 ↑ 1.5 1,011 1

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

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

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

5. 0.755 2.079 ↑ 1.5 1,011 1

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

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

  • Index Cond: (should_retry = true)
  • Filter: should_retry
7. 20.408 15,634.540 ↑ 11.0 47,442 1

Sort (cost=1,699,940.97..1,701,243.79 rows=521,127 width=44) (actual time=15,628.813..15,634.540 rows=47,442 loops=1)

  • Sort Key: dispersion_details.storekeeper_id
  • Sort Method: quicksort Memory: 5011kB
8. 6,907.868 15,614.132 ↑ 11.0 47,442 1

HashAggregate (cost=1,638,731.28..1,645,245.37 rows=521,127 width=44) (actual time=15,598.205..15,614.132 rows=47,442 loops=1)

  • Group Key: dispersion_details.storekeeper_id, storekeepers.order_level_id, order_levels.first_debt_limit
9. 3,659.951 8,706.264 ↓ 1.0 17,139,762 1

Hash Join (cost=197,097.68..1,470,300.58 rows=16,843,070 width=17) (actual time=1,341.959..8,706.264 rows=17,139,762 loops=1)

  • Hash Cond: (dispersion_details.storekeeper_id = storekeepers.id)
10. 3,917.081 4,946.566 ↓ 1.0 17,139,762 1

Bitmap Heap Scan on dispersion_details (cost=181,330.15..1,222,940.84 rows=16,843,070 width=9) (actual time=1,241.238..4,946.566 rows=17,139,762 loops=1)

  • Filter: (NOT was_dispersed)
  • Heap Blocks: exact=736564
11. 1,029.485 1,029.485 ↓ 1.0 17,139,762 1

Bitmap Index Scan on dispersion_dispersion_details_was_dispersed_id_index (cost=0.00..177,119.38 rows=16,843,069 width=0) (actual time=1,029.485..1,029.485 rows=17,139,762 loops=1)

  • Index Cond: (was_dispersed = false)
12. 22.492 99.747 ↑ 1.0 136,478 1

Hash (cost=14,061.56..14,061.56 rows=136,478 width=12) (actual time=99.747..99.747 rows=136,478 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 7921kB
13. 23.647 77.255 ↑ 1.0 136,478 1

Hash Join (cost=1.20..14,061.56 rows=136,478 width=12) (actual time=0.022..77.255 rows=136,478 loops=1)

  • Hash Cond: (storekeepers.order_level_id = order_levels.id)
14. 53.600 53.600 ↑ 1.0 136,478 1

Seq Scan on storekeepers (cost=0.00..12,183.78 rows=136,478 width=8) (actual time=0.005..53.600 rows=136,478 loops=1)

15. 0.001 0.008 ↑ 1.0 9 1

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

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

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