explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5Zgr

Settings
# exclusive inclusive rows x rows loops node
1. 0.129 9,966.360 ↑ 5,917.6 57 1

Sort (cost=767,882.51..768,725.77 rows=337,306 width=377) (actual time=9,966.356..9,966.360 rows=57 loops=1)

  • Sort Key: ((((((s.income + s.expenses) * s.split_amount) + s.transfers) + s.payouts) + s.previous_balance)) DESC
  • Sort Method: quicksort Memory: 40kB
2.          

CTE datetime_range

3. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=1)

4.          

CTE summary

5. 99.814 9,964.522 ↑ 5,917.6 57 1

GroupAggregate (cost=565,836.54..599,567.14 rows=337,306 width=204) (actual time=9,800.408..9,964.522 rows=57 loops=1)

  • Group Key: (COALESCE(t.contract_id, r_1.contract_id, tr.contract_id, rg.contract_id)), s_1.split_amount, s_1.ledger_id, rg.id
6.          

Initplan (for GroupAggregate)

7. 0.003 0.003 ↑ 1.0 1 1

CTE Scan on datetime_range (cost=0.00..0.02 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1)

8. 253.358 9,864.705 ↑ 1.9 177,216 1

Sort (cost=565,836.52..566,679.78 rows=337,306 width=120) (actual time=9,798.614..9,864.705 rows=177,216 loops=1)

  • Sort Key: (COALESCE(t.contract_id, r_1.contract_id, tr.contract_id, rg.contract_id)), s_1.split_amount, s_1.ledger_id, rg.id
  • Sort Method: external merge Disk: 11760kB
9. 331.776 9,611.347 ↑ 1.9 177,216 1

Hash Left Join (cost=4,983.81..514,110.59 rows=337,306 width=120) (actual time=46.500..9,611.347 rows=177,216 loops=1)

  • Hash Cond: (COALESCE(t.contract_id, r_1.contract_id, tr.contract_id, rg.contract_id) = s_1.contract_id)
  • Filter: ((((t.transaction_type)::text = 'transfer'::text) AND (t.payee_id = 1002)) OR (((t.transaction_type)::text = ANY ('{income,expense}'::text[])) AND (s_1.contract_id IS NOT NULL)))
  • Rows Removed by Filter: 194130
10. 617.849 9,279.349 ↓ 1.1 371,346 1

Hash Left Join (cost=4,949.65..511,055.09 rows=339,001 width=100) (actual time=35.222..9,279.349 rows=371,346 loops=1)

  • Hash Cond: ((SubPlan 3) = rg.id)
11. 148.194 6,432.574 ↓ 1.1 371,346 1

Hash Left Join (cost=4,805.05..368,186.42 rows=339,001 width=96) (actual time=34.302..6,432.574 rows=371,346 loops=1)

  • Hash Cond: (r_1.track_id = tr.id)
12. 156.180 6,280.715 ↓ 1.1 371,346 1

Hash Left Join (cost=4,532.72..367,024.00 rows=339,001 width=96) (actual time=30.625..6,280.715 rows=371,346 loops=1)

  • Hash Cond: (t.customer_id = cu.id)
13. 226.985 6,124.516 ↓ 1.1 371,346 1

Hash Left Join (cost=4,530.49..362,360.52 rows=339,001 width=62) (actual time=30.594..6,124.516 rows=371,346 loops=1)

  • Hash Cond: (t.release_id = r_1.id)
14. 5,868.926 5,868.926 ↓ 1.1 371,346 1

Seq Scan on royalties_transaction t (cost=0.00..356,940.01 rows=339,001 width=54) (actual time=1.933..5,868.926 rows=371,346 loops=1)

  • Filter: (customer_id = 7)
  • Rows Removed by Filter: 2229052
15. 8.762 28.605 ↓ 1.0 28,239 1

Hash (cost=4,181.33..4,181.33 rows=27,933 width=12) (actual time=28.605..28.605 rows=28,239 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1291kB
16. 19.843 19.843 ↓ 1.0 28,239 1

Seq Scan on royalties_release r_1 (cost=0.00..4,181.33 rows=27,933 width=12) (actual time=0.019..19.843 rows=28,239 loops=1)

17. 0.002 0.019 ↑ 1.0 1 1

Hash (cost=2.21..2.21 rows=1 width=42) (actual time=0.019..0.019 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.017 0.017 ↑ 1.0 1 1

Seq Scan on royalties_customer cu (cost=0.00..2.21 rows=1 width=42) (actual time=0.014..0.017 rows=1 loops=1)

  • Filter: (id = 7)
  • Rows Removed by Filter: 20
19. 1.446 3.665 ↓ 1.0 6,898 1

Hash (cost=188.26..188.26 rows=6,726 width=8) (actual time=3.665..3.665 rows=6,898 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 307kB
20. 2.219 2.219 ↓ 1.0 6,898 1

Seq Scan on royalties_track tr (cost=0.00..188.26 rows=6,726 width=8) (actual time=0.006..2.219 rows=6,898 loops=1)

21. 0.265 0.889 ↑ 1.1 1,413 1

Hash (cost=125.93..125.93 rows=1,493 width=8) (actual time=0.888..0.889 rows=1,413 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 69kB
22. 0.624 0.624 ↑ 1.1 1,413 1

Seq Scan on royalties_releasegroup rg (cost=0.00..125.93 rows=1,493 width=8) (actual time=0.056..0.624 rows=1,413 loops=1)

23.          

SubPlan (for Hash Left Join)

24. 742.679 2,228.037 ↑ 1.0 1 742,679

Aggregate (cost=8.31..8.32 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=742,679)

25. 1,485.358 1,485.358 ↑ 1.0 1 742,679

Index Scan using royalties_releasegroup_releases_release_id_708c93d5 on royalties_releasegroup_releases (cost=0.29..8.30 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=742,679)

  • Index Cond: (release_id = t.release_id)
26. 0.049 0.222 ↓ 1.0 60 1

Hash (cost=33.42..33.42 rows=59 width=40) (actual time=0.222..0.222 rows=60 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
27. 0.011 0.173 ↓ 1.0 60 1

Subquery Scan on s_1 (cost=32.09..33.42 rows=59 width=40) (actual time=0.123..0.173 rows=60 loops=1)

28. 0.091 0.162 ↓ 1.0 60 1

HashAggregate (cost=32.09..32.83 rows=59 width=40) (actual time=0.122..0.162 rows=60 loops=1)

  • Group Key: s_2.contract_id, s_2.balance_group_id
29. 0.063 0.071 ↑ 1.0 60 1

Bitmap Heap Scan on royalties_split s_2 (cost=4.75..31.64 rows=60 width=13) (actual time=0.013..0.071 rows=60 loops=1)

  • Recheck Cond: (payee_id = 1002)
  • Filter: (split_amount > '0'::numeric)
  • Heap Blocks: exact=7
30. 0.008 0.008 ↑ 1.0 60 1

Bitmap Index Scan on royalties_split_payee_id_729ebb89 (cost=0.00..4.73 rows=60 width=0) (actual time=0.008..0.008 rows=60 loops=1)

  • Index Cond: (payee_id = 1002)
31. 0.321 9,966.231 ↑ 5,917.6 57 1

Hash Left Join (cost=212.90..19,744.43 rows=337,306 width=377) (actual time=9,801.505..9,966.231 rows=57 loops=1)

  • Hash Cond: (s.ledger_id = l.id)
32. 0.068 9,965.716 ↑ 5,917.6 57 1

Hash Left Join (cost=187.63..8,709.94 rows=337,306 width=241) (actual time=9,801.302..9,965.716 rows=57 loops=1)

  • Hash Cond: (s.release_id = r.id)
33. 0.134 9,965.176 ↑ 5,917.6 57 1

Hash Left Join (cost=43.04..7,677.40 rows=337,306 width=217) (actual time=9,800.825..9,965.176 rows=57 loops=1)

  • Hash Cond: (s.contract_id = c.id)
34. 9,964.641 9,964.641 ↑ 5,917.6 57 1

CTE Scan on summary s (cost=0.00..6,746.12 rows=337,306 width=204) (actual time=9,800.413..9,964.641 rows=57 loops=1)

35. 0.202 0.401 ↓ 1.0 1,340 1

Hash (cost=26.35..26.35 rows=1,335 width=17) (actual time=0.401..0.401 rows=1,340 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 83kB
36. 0.199 0.199 ↓ 1.0 1,340 1

Seq Scan on royalties_contract c (cost=0.00..26.35 rows=1,335 width=17) (actual time=0.015..0.199 rows=1,340 loops=1)

37. 0.204 0.472 ↑ 1.1 1,413 1

Hash (cost=125.93..125.93 rows=1,493 width=28) (actual time=0.472..0.472 rows=1,413 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 104kB
38. 0.268 0.268 ↑ 1.1 1,413 1

Seq Scan on royalties_releasegroup r (cost=0.00..125.93 rows=1,493 width=28) (actual time=0.006..0.268 rows=1,413 loops=1)

39. 0.098 0.194 ↓ 1.0 814 1

Hash (cost=15.12..15.12 rows=812 width=12) (actual time=0.194..0.194 rows=814 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 45kB
40. 0.096 0.096 ↓ 1.0 814 1

Seq Scan on royalties_splitbalancegroup l (cost=0.00..15.12 rows=812 width=12) (actual time=0.007..0.096 rows=814 loops=1)

Planning time : 3.691 ms
Execution time : 9,969.156 ms