explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8HSp

Settings
# exclusive inclusive rows x rows loops node
1. 0.038 933.032 ↑ 461.5 24 1

Sort (cost=393,443.35..393,471.03 rows=11,075 width=377) (actual time=933.031..933.032 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.000..0.001 rows=1 loops=1)

4.          

CTE transactions

5. 82.567 691.899 ↑ 1.2 96,014 1

Hash Left Join (cost=7,801.78..302,496.67 rows=110,747 width=92) (actual time=60.398..691.899 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
6. 97.599 609.241 ↑ 1.1 103,324 1

Hash Left Join (cost=7,778.06..301,414.16 rows=111,304 width=100) (actual time=60.298..609.241 rows=103,324 loops=1)

  • Hash Cond: ((SubPlan 2) = rg.id)
7. 0.000 97.803 ↑ 1.1 103,324 1

Gather (cost=7,633.47..254,407.30 rows=111,304 width=96) (actual time=59.699..97.803 rows=103,324 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 11.725 163.586 ↑ 1.3 34,441 3

Hash Left Join (cost=6,633.47..242,276.90 rows=46,377 width=96) (actual time=55.146..163.586 rows=34,441 loops=3)

  • Hash Cond: (r_1.track_id = tr.id)
9. 14.868 148.152 ↑ 1.3 34,441 3

Parallel Hash Left Join (cost=6,361.14..241,882.79 rows=46,377 width=96) (actual time=51.392..148.152 rows=34,441 loops=3)

  • Hash Cond: (t.release_id = r_1.id)
10. 11.800 108.635 ↑ 1.3 34,441 3

Hash Left Join (cost=2,197.26..237,597.15 rows=46,377 width=88) (actual time=24.627..108.635 rows=34,441 loops=3)

  • Hash Cond: (t.customer_id = cu.id)
11. 78.139 93.581 ↑ 1.3 34,441 3

Parallel Bitmap Heap Scan on royalties_transaction t (cost=2,195.04..236,957.24 rows=46,377 width=54) (actual time=21.073..93.581 rows=34,441 loops=3)

  • Recheck Cond: (customer_id = 1)
  • Heap Blocks: exact=1
12. 15.442 15.442 ↑ 1.1 103,656 1

Bitmap Index Scan on royalties_transaction_customer_id_2afc02dd (cost=0.00..2,167.21 rows=111,304 width=0) (actual time=15.442..15.442 rows=103,656 loops=1)

  • Index Cond: (customer_id = 1)
13. 3.215 3.254 ↑ 1.0 1 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.039 0.039 ↑ 1.0 1 3

Seq Scan on royalties_customer cu (cost=0.00..2.21 rows=1 width=42) (actual time=0.035..0.039 rows=1 loops=3)

  • Filter: (id = 1)
  • Rows Removed by Filter: 20
15. 7.264 24.649 ↑ 1.2 9,413 3

Parallel Hash (cost=4,018.39..4,018.39 rows=11,639 width=12) (actual time=24.649..24.649 rows=9,413 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 1376kB
16. 17.385 17.385 ↑ 1.2 9,413 3

Parallel Seq Scan on royalties_release r_1 (cost=0.00..4,018.39 rows=11,639 width=12) (actual time=0.024..17.385 rows=9,413 loops=3)

17. 1.209 3.709 ↓ 1.0 6,898 3

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

  • Buckets: 8192 Batches: 1 Memory Usage: 307kB
18. 2.500 2.500 ↓ 1.0 6,898 3

Seq Scan on royalties_track tr (cost=0.00..188.26 rows=6,726 width=8) (actual time=0.022..2.500 rows=6,898 loops=3)

19. 0.194 0.569 ↑ 1.1 1,413 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 69kB
20. 0.375 0.375 ↑ 1.1 1,413 1

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

21.          

SubPlan (for Hash Left Join)

22. 206.635 413.270 ↑ 1.0 1 206,635

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

23. 206.635 206.635 ↑ 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.001..0.001 rows=1 loops=206,635)

  • Index Cond: (release_id = t.release_id)
24. 0.003 0.091 ↓ 1.4 13 1

Hash (cost=23.61..23.61 rows=9 width=40) (actual time=0.091..0.091 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
25. 0.003 0.088 ↓ 1.4 13 1

Subquery Scan on s_1 (cost=23.31..23.61 rows=9 width=40) (actual time=0.078..0.088 rows=13 loops=1)

26. 0.013 0.085 ↓ 1.4 13 1

GroupAggregate (cost=23.31..23.52 rows=9 width=40) (actual time=0.077..0.085 rows=13 loops=1)

  • Group Key: s_2.contract_id, s_2.balance_group_id
27. 0.009 0.072 ↓ 1.4 13 1

Sort (cost=23.31..23.34 rows=9 width=13) (actual time=0.071..0.072 rows=13 loops=1)

  • Sort Key: s_2.contract_id, s_2.balance_group_id
  • Sort Method: quicksort Memory: 25kB
28. 0.041 0.063 ↓ 1.4 13 1

Bitmap Heap Scan on royalties_split s_2 (cost=4.35..23.17 rows=9 width=13) (actual time=0.034..0.063 rows=13 loops=1)

  • Recheck Cond: (payee_id = 1305)
  • Filter: (split_amount > '0'::numeric)
  • Heap Blocks: exact=6
29. 0.022 0.022 ↓ 1.6 14 1

Bitmap Index Scan on royalties_split_payee_id_729ebb89 (cost=0.00..4.35 rows=9 width=0) (actual time=0.022..0.022 rows=14 loops=1)

  • Index Cond: (payee_id = 1305)
30.          

CTE summary

31. 65.980 931.379 ↑ 461.5 24 1

GroupAggregate (cost=81,906.85..87,416.52 rows=11,075 width=204) (actual time=820.425..931.379 rows=24 loops=1)

  • Group Key: t_1.contract_id, t_1.split_amount, t_1.ledger_id, t_1.release_id
32.          

Initplan (for GroupAggregate)

33. 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)

34. 102.144 865.396 ↑ 1.2 96,014 1

Sort (cost=81,906.83..82,183.69 rows=110,747 width=718) (actual time=816.764..865.396 rows=96,014 loops=1)

  • Sort Key: t_1.contract_id, t_1.split_amount, t_1.ledger_id, t_1.release_id
  • Sort Method: external merge Disk: 4832kB
35. 763.252 763.252 ↑ 1.2 96,014 1

CTE Scan on transactions t_1 (cost=0.00..2,214.94 rows=110,747 width=718) (actual time=60.400..763.252 rows=96,014 loops=1)

36. 0.078 932.994 ↑ 461.5 24 1

Hash Left Join (cost=212.90..854.19 rows=11,075 width=377) (actual time=821.894..932.994 rows=24 loops=1)

  • Hash Cond: (s.ledger_id = l.id)
37. 0.026 932.712 ↑ 461.5 24 1

Hash Left Join (cost=187.63..467.44 rows=11,075 width=241) (actual time=821.679..932.712 rows=24 loops=1)

  • Hash Cond: (s.release_id = r.id)
38. 0.053 931.829 ↑ 461.5 24 1

Hash Left Join (cost=43.04..293.70 rows=11,075 width=217) (actual time=820.816..931.829 rows=24 loops=1)

  • Hash Cond: (s.contract_id = c.id)
39. 931.414 931.414 ↑ 461.5 24 1

CTE Scan on summary s (cost=0.00..221.50 rows=11,075 width=204) (actual time=820.428..931.414 rows=24 loops=1)

40. 0.167 0.362 ↓ 1.0 1,340 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 83kB
41. 0.195 0.195 ↓ 1.0 1,340 1

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

42. 0.261 0.857 ↑ 1.1 1,413 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 104kB
43. 0.596 0.596 ↑ 1.1 1,413 1

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

44. 0.101 0.204 ↓ 1.0 814 1

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

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

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