explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HrHY

Settings
# exclusive inclusive rows x rows loops node
1. 0.055 1,830.725 ↑ 4,257.4 24 1

Sort (cost=344,635.72..344,891.16 rows=102,177 width=378) (actual time=1,830.723..1,830.725 rows=24 loops=1)

  • Sort Key: ((((((s.income + s.expenses) * s.split_amount) + s.transfers) + s.payouts) + s.previous_balance)) DESC
  • Sort Method: quicksort Memory: 31kB
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. 78.931 1,827.167 ↑ 4,257.4 24 1

GroupAggregate (cost=301,977.22..312,194.92 rows=102,177 width=204) (actual time=1,703.132..1,827.167 rows=24 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.004 0.004 ↑ 1.0 1 1

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

8. 133.380 1,748.232 ↑ 1.1 96,014 1

Sort (cost=301,977.20..302,232.64 rows=102,177 width=86) (actual time=1,699.584..1,748.232 rows=96,014 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: 6936kB
9. 95.146 1,614.852 ↑ 1.1 96,014 1

Hash Left Join (cost=7,118.90..288,586.21 rows=102,177 width=86) (actual time=65.610..1,614.852 rows=96,014 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 = 1305)) OR (((t.transaction_type)::text = ANY ('{income,expense}'::text[])) AND (s_1.contract_id IS NOT NULL)))
  • Rows Removed by Filter: 7310
10. 121.974 1,518.150 ↓ 1.0 103,324 1

Hash Left Join (cost=7,090.96..288,056.36 rows=102,694 width=66) (actual time=64.037..1,518.150 rows=103,324 loops=1)

  • Hash Cond: ((SubPlan 3) = rg.id)
11. 51.744 775.261 ↓ 1.0 103,324 1

Hash Left Join (cost=6,948.17..244,662.08 rows=102,694 width=62) (actual time=62.987..775.261 rows=103,324 loops=1)

  • Hash Cond: (r_1.track_id = tr.id)
12. 66.258 720.868 ↓ 1.0 103,324 1

Hash Left Join (cost=6,671.96..244,116.25 rows=102,694 width=62) (actual time=60.324..720.868 rows=103,324 loops=1)

  • Hash Cond: (t.release_id = r_1.id)
13. 59.381 614.559 ↓ 1.0 103,324 1

Hash Left Join (cost=2,134.58..239,309.26 rows=102,694 width=54) (actual time=20.255..614.559 rows=103,324 loops=1)

  • Hash Cond: (t.customer_id = cu.id)
14. 539.712 555.165 ↓ 1.0 103,324 1

Bitmap Heap Scan on royalties_transaction t (cost=2,132.31..237,894.94 rows=102,694 width=54) (actual time=20.231..555.165 rows=103,324 loops=1)

  • Recheck Cond: (customer_id = 1)
  • Heap Blocks: exact=28365
15. 15.453 15.453 ↓ 1.0 103,656 1

Bitmap Index Scan on royalties_transaction_customer_id_2afc02dd (cost=0.00..2,106.63 rows=102,694 width=0) (actual time=15.453..15.453 rows=103,656 loops=1)

  • Index Cond: (customer_id = 1)
16. 0.003 0.013 ↑ 1.0 1 1

Hash (cost=2.26..2.26 rows=1 width=8) (actual time=0.012..0.013 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 0.010 0.010 ↑ 1.0 1 1

Seq Scan on royalties_customer cu (cost=0.00..2.26 rows=1 width=8) (actual time=0.007..0.010 rows=1 loops=1)

  • Filter: (id = 1)
  • Rows Removed by Filter: 20
18. 7.240 40.051 ↑ 1.0 28,239 1

Hash (cost=4,184.39..4,184.39 rows=28,239 width=12) (actual time=40.051..40.051 rows=28,239 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1291kB
19. 32.811 32.811 ↑ 1.0 28,239 1

Seq Scan on royalties_release r_1 (cost=0.00..4,184.39 rows=28,239 width=12) (actual time=0.424..32.811 rows=28,239 loops=1)

20. 0.944 2.649 ↑ 1.0 6,898 1

Hash (cost=189.98..189.98 rows=6,898 width=8) (actual time=2.649..2.649 rows=6,898 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 307kB
21. 1.705 1.705 ↑ 1.0 6,898 1

Seq Scan on royalties_track tr (cost=0.00..189.98 rows=6,898 width=8) (actual time=0.061..1.705 rows=6,898 loops=1)

22. 0.189 1.010 ↑ 1.0 1,413 1

Hash (cost=125.13..125.13 rows=1,413 width=8) (actual time=1.010..1.010 rows=1,413 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 69kB
23. 0.821 0.821 ↑ 1.0 1,413 1

Seq Scan on royalties_releasegroup rg (cost=0.00..125.13 rows=1,413 width=8) (actual time=0.008..0.821 rows=1,413 loops=1)

24.          

SubPlan (for Hash Left Join)

25. 206.635 619.905 ↑ 1.0 1 206,635

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

26. 413.270 413.270 ↑ 1.0 1 206,635

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=206,635)

  • Index Cond: (release_id = t.release_id)
27. 0.008 1.556 ↑ 1.0 13 1

Hash (cost=27.77..27.77 rows=13 width=40) (actual time=1.556..1.556 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
28. 0.005 1.548 ↑ 1.0 13 1

Subquery Scan on s_1 (cost=27.35..27.77 rows=13 width=40) (actual time=1.532..1.548 rows=13 loops=1)

29. 0.022 1.543 ↑ 1.0 13 1

GroupAggregate (cost=27.35..27.64 rows=13 width=40) (actual time=1.530..1.543 rows=13 loops=1)

  • Group Key: s_2.contract_id, s_2.balance_group_id
30. 0.016 1.521 ↑ 1.0 13 1

Sort (cost=27.35..27.38 rows=13 width=13) (actual time=1.520..1.521 rows=13 loops=1)

  • Sort Key: s_2.contract_id, s_2.balance_group_id
  • Sort Method: quicksort Memory: 25kB
31. 0.299 1.505 ↑ 1.0 13 1

Bitmap Heap Scan on royalties_split s_2 (cost=4.38..27.11 rows=13 width=13) (actual time=1.351..1.505 rows=13 loops=1)

  • Recheck Cond: (payee_id = 1305)
  • Filter: (split_amount > '0'::numeric)
  • Heap Blocks: exact=6
32. 1.206 1.206 ↓ 1.1 14 1

Bitmap Index Scan on royalties_split_payee_id_729ebb89 (cost=0.00..4.38 rows=13 width=0) (actual time=1.206..1.206 rows=14 loops=1)

  • Index Cond: (payee_id = 1305)
33. 0.100 1,830.670 ↑ 4,257.4 24 1

Hash Left Join (cost=211.26..6,127.80 rows=102,177 width=378) (actual time=1,706.459..1,830.670 rows=24 loops=1)

  • Hash Cond: (s.ledger_id = l.id)
34. 0.042 1,828.910 ↑ 4,257.4 24 1

Hash Left Join (cost=185.94..2,767.56 rows=102,177 width=242) (actual time=1,704.787..1,828.910 rows=24 loops=1)

  • Hash Cond: (s.release_id = r.id)
35. 0.037 1,828.014 ↑ 4,257.4 24 1

Hash Left Join (cost=43.15..2,355.75 rows=102,177 width=217) (actual time=1,703.927..1,828.014 rows=24 loops=1)

  • Hash Cond: (s.contract_id = c.id)
36. 1,827.197 1,827.197 ↑ 4,257.4 24 1

CTE Scan on summary s (cost=0.00..2,043.54 rows=102,177 width=204) (actual time=1,703.134..1,827.197 rows=24 loops=1)

37. 0.180 0.780 ↑ 1.0 1,340 1

Hash (cost=26.40..26.40 rows=1,340 width=17) (actual time=0.780..0.780 rows=1,340 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 83kB
38. 0.600 0.600 ↑ 1.0 1,340 1

Seq Scan on royalties_contract c (cost=0.00..26.40 rows=1,340 width=17) (actual time=0.222..0.600 rows=1,340 loops=1)

39. 0.218 0.854 ↑ 1.0 1,413 1

Hash (cost=125.13..125.13 rows=1,413 width=29) (actual time=0.854..0.854 rows=1,413 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 104kB
40. 0.636 0.636 ↑ 1.0 1,413 1

Seq Scan on royalties_releasegroup r (cost=0.00..125.13 rows=1,413 width=29) (actual time=0.016..0.636 rows=1,413 loops=1)

41. 0.104 1.660 ↑ 1.0 814 1

Hash (cost=15.14..15.14 rows=814 width=12) (actual time=1.660..1.660 rows=814 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 45kB
42. 1.556 1.556 ↑ 1.0 814 1

Seq Scan on royalties_splitbalancegroup l (cost=0.00..15.14 rows=814 width=12) (actual time=0.165..1.556 rows=814 loops=1)

Planning time : 12.364 ms
Execution time : 1,834.371 ms