explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iWaS : test

Settings
# exclusive inclusive rows x rows loops node
1. 10.956 19,929.941 ↑ 100.4 13,557 1

HashAggregate (cost=2,095,996.14..2,113,004.82 rows=1,360,695 width=44) (actual time=19,924.777..19,929.941 rows=13,557 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.528 19,918.985 ↑ 100.4 13,557 1

Merge Right Join (cost=1,997,337.96..2,082,389.19 rows=1,360,695 width=44) (actual time=19,895.707..19,918.985 rows=13,557 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: 33826
3. 0.360 2.779 ↑ 1.5 1,011 1

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

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

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

5. 0.891 2.341 ↑ 1.5 1,011 1

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

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

  • Index Cond: (should_retry = true)
  • Filter: should_retry
7. 23.101 19,900.678 ↑ 11.1 47,383 1

Sort (cost=1,997,106.80..1,998,416.84 rows=524,016 width=44) (actual time=19,893.067..19,900.678 rows=47,383 loops=1)

  • Sort Key: dispersion_details.storekeeper_id
  • Sort Method: quicksort Memory: 5006kB
8. 7,988.586 19,877.577 ↑ 11.1 47,383 1

HashAggregate (cost=1,935,536.88..1,942,087.08 rows=524,016 width=44) (actual time=19,858.073..19,877.577 rows=47,383 loops=1)

  • Group Key: dispersion_details.storekeeper_id, storekeepers.order_level_id, order_levels.first_debt_limit
9. 4,295.049 11,888.991 ↑ 1.0 17,139,272 1

Hash Join (cost=15,767.46..1,760,429.03 rows=17,510,785 width=17) (actual time=113.102..11,888.991 rows=17,139,272 loops=1)

  • Hash Cond: (dispersion_details.storekeeper_id = storekeepers.id)
10. 7,481.847 7,481.847 ↑ 1.0 17,139,272 1

Seq Scan on dispersion_details (cost=0.00..1,503,888.28 rows=17,510,785 width=9) (actual time=0.015..7,481.847 rows=17,139,272 loops=1)

  • Filter: (NOT was_dispersed)
  • Rows Removed by Filter: 45935584
11. 28.740 112.095 ↓ 1.0 136,478 1

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

  • Buckets: 262144 Batches: 1 Memory Usage: 7921kB
12. 24.285 83.355 ↓ 1.0 136,478 1

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

  • Hash Cond: (storekeepers.order_level_id = order_levels.id)
13. 59.061 59.061 ↓ 1.0 136,478 1

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

14. 0.005 0.009 ↑ 1.0 9 1

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

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

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

Planning time : 0.839 ms
Execution time : 19,936.469 ms