explain.depesz.com

PostgreSQL's explain analyze made readable

Result: POki

Settings
# exclusive inclusive rows x rows loops node
1. 0.025 1,257.415 ↑ 461.5 24 1

Sort (cost=448,718.51..448,746.19 rows=11,075 width=885) (actual time=1,257.413..1,257.415 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 transactions

5. 100.340 1,020.961 ↑ 1.2 96,014 1

Hash Left Join (cost=8,178.30..305,962.96 rows=110,747 width=100) (actual time=69.449..1,020.961 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. 186.179 920.325 ↑ 1.1 103,324 1

Hash Left Join (cost=8,136.22..304,862.09 rows=111,304 width=100) (actual time=69.134..920.325 rows=103,324 loops=1)

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

Hash Left Join (cost=7,991.62..257,855.22 rows=111,304 width=96) (actual time=68.530..320.296 rows=103,324 loops=1)

  • Hash Cond: (tr.contract_id = trc.id)
8. 40.899 295.951 ↑ 1.1 103,324 1

Hash Left Join (cost=7,948.59..257,519.09 rows=111,304 width=96) (actual time=68.241..295.951 rows=103,324 loops=1)

  • Hash Cond: (r_1.track_id = tr.id)
9. 37.142 252.461 ↑ 1.1 103,324 1

Hash Left Join (cost=7,676.25..256,954.51 rows=111,304 width=96) (actual time=65.643..252.461 rows=103,324 loops=1)

  • Hash Cond: (r_1.contract_id = rc.id)
10. 4.977 215.044 ↑ 1.1 103,324 1

Gather (cost=7,633.21..256,619.29 rows=111,304 width=96) (actual time=65.362..215.044 rows=103,324 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
11. 9.788 210.067 ↑ 1.3 34,441 3

Hash Left Join (cost=6,633.21..244,488.89 rows=46,377 width=96) (actual time=53.930..210.067 rows=34,441 loops=3)

  • Hash Cond: (ra.alias_of_id = ra_alias.id)
12. 10.497 199.697 ↑ 1.3 34,441 3

Hash Left Join (cost=6,518.69..244,252.60 rows=46,377 width=100) (actual time=53.318..199.697 rows=34,441 loops=3)

  • Hash Cond: (r_1.artist_id = ra.id)
13. 15.795 188.523 ↑ 1.3 34,441 3

Parallel Hash Left Join (cost=6,404.17..244,015.96 rows=46,377 width=100) (actual time=52.629..188.523 rows=34,441 loops=3)

  • Hash Cond: (t.release_id = r_1.id)
14. 10.881 142.115 ↑ 1.3 34,441 3

Hash Left Join (cost=2,240.30..239,730.32 rows=46,377 width=88) (actual time=21.583..142.115 rows=34,441 loops=3)

  • Hash Cond: (t.contract_id = c_1.id)
15. 12.297 130.623 ↑ 1.3 34,441 3

Hash Left Join (cost=2,197.26..239,565.26 rows=46,377 width=88) (actual time=20.946..130.623 rows=34,441 loops=3)

  • Hash Cond: (t.customer_id = cu.id)
16. 104.918 118.251 ↑ 1.3 34,441 3

Parallel Bitmap Heap Scan on royalties_transaction t (cost=2,195.04..238,925.35 rows=46,377 width=54) (actual time=20.845..118.251 rows=34,441 loops=3)

  • Recheck Cond: (customer_id = 1)
  • Filter: ((tags IS NULL) OR (NOT (tags @> '{mechanicals_payout}'::character varying(255)[])))
  • Heap Blocks: exact=68
17. 13.333 13.333 ↑ 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=13.333..13.333 rows=103,656 loops=1)

  • Index Cond: (customer_id = 1)
18. 0.005 0.075 ↑ 1.0 1 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.070 0.070 ↑ 1.0 1 3

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

  • Filter: (id = 1)
  • Rows Removed by Filter: 20
20. 0.371 0.611 ↓ 1.0 1,340 3

Hash (cost=26.35..26.35 rows=1,335 width=4) (actual time=0.611..0.611 rows=1,340 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 64kB
21. 0.240 0.240 ↓ 1.0 1,340 3

Seq Scan on royalties_contract c_1 (cost=0.00..26.35 rows=1,335 width=4) (actual time=0.022..0.240 rows=1,340 loops=3)

22. 6.032 30.613 ↑ 1.2 9,413 3

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1472kB
23. 24.581 24.581 ↑ 1.2 9,413 3

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

24. 0.261 0.677 ↓ 1.1 1,393 3

Hash (cost=98.12..98.12 rows=1,312 width=8) (actual time=0.677..0.677 rows=1,393 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 66kB
25. 0.416 0.416 ↓ 1.1 1,393 3

Seq Scan on royalties_artist ra (cost=0.00..98.12 rows=1,312 width=8) (actual time=0.011..0.416 rows=1,393 loops=3)

26. 0.270 0.582 ↓ 1.1 1,393 3

Hash (cost=98.12..98.12 rows=1,312 width=4) (actual time=0.582..0.582 rows=1,393 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 65kB
27. 0.312 0.312 ↓ 1.1 1,393 3

Seq Scan on royalties_artist ra_alias (cost=0.00..98.12 rows=1,312 width=4) (actual time=0.029..0.312 rows=1,393 loops=3)

28. 0.142 0.275 ↓ 1.0 1,340 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 64kB
29. 0.133 0.133 ↓ 1.0 1,340 1

Seq Scan on royalties_contract rc (cost=0.00..26.35 rows=1,335 width=4) (actual time=0.012..0.133 rows=1,340 loops=1)

30. 0.872 2.591 ↓ 1.0 6,898 1

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

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

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

32. 0.157 0.283 ↓ 1.0 1,340 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 64kB
33. 0.126 0.126 ↓ 1.0 1,340 1

Seq Scan on royalties_contract trc (cost=0.00..26.35 rows=1,335 width=4) (actual time=0.006..0.126 rows=1,340 loops=1)

34. 0.163 0.580 ↑ 1.1 1,413 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 74kB
35. 0.417 0.417 ↑ 1.1 1,413 1

Seq Scan on royalties_releasegroup rg (cost=0.00..125.93 rows=1,493 width=12) (actual time=0.004..0.417 rows=1,413 loops=1)

36.          

SubPlan (for Hash Left Join)

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

38. 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)
39. 0.004 0.296 ↓ 1.4 13 1

Hash (cost=41.97..41.97 rows=9 width=48) (actual time=0.296..0.296 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
40. 0.077 0.292 ↓ 1.4 13 1

Hash Right Join (cost=23.72..41.97 rows=9 width=48) (actual time=0.289..0.292 rows=13 loops=1)

  • Hash Cond: (l.id = s_1.ledger_id)
41. 0.090 0.090 ↓ 1.0 814 1

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

42. 0.004 0.125 ↓ 1.4 13 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
43. 0.003 0.121 ↓ 1.4 13 1

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

44. 0.013 0.118 ↓ 1.4 13 1

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

  • Group Key: s_2.contract_id, s_2.balance_group_id
45. 0.026 0.105 ↓ 1.4 13 1

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

  • Sort Key: s_2.contract_id, s_2.balance_group_id
  • Sort Method: quicksort Memory: 25kB
46. 0.042 0.079 ↓ 1.4 13 1

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

  • Recheck Cond: (payee_id = 1305)
  • Filter: (split_amount > '0'::numeric)
  • Heap Blocks: exact=6
47. 0.037 0.037 ↓ 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.037..0.037 rows=14 loops=1)

  • Index Cond: (payee_id = 1305)
48.          

CTE summary

49. 47.919 1,256.139 ↑ 461.5 24 1

GroupAggregate (cost=131,109.85..136,896.38 rows=11,075 width=720) (actual time=1,176.828..1,256.139 rows=24 loops=1)

  • Group Key: t_1.contract_id, t_1.split_amount, t_1.ledger_id, t_1.ledger_name, t_1.release_id
50.          

Initplan (for GroupAggregate)

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

52. 98.216 1,208.217 ↑ 1.2 96,014 1

Sort (cost=131,109.83..131,386.69 rows=110,747 width=1,234) (actual time=1,173.457..1,208.217 rows=96,014 loops=1)

  • Sort Key: t_1.contract_id, t_1.split_amount, t_1.ledger_id, t_1.ledger_name, t_1.release_id
  • Sort Method: external merge Disk: 5592kB
53. 1,110.001 1,110.001 ↑ 1.2 96,014 1

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

54. 0.062 1,257.390 ↑ 461.5 24 1

Hash Left Join (cost=187.63..799.69 rows=11,075 width=885) (actual time=1,177.988..1,257.390 rows=24 loops=1)

  • Hash Cond: (s.release_id = r.id)
55. 0.029 1,256.545 ↑ 461.5 24 1

Hash Left Join (cost=43.04..293.70 rows=11,075 width=733) (actual time=1,177.196..1,256.545 rows=24 loops=1)

  • Hash Cond: (s.contract_id = c.id)
56. 1,256.161 1,256.161 ↑ 461.5 24 1

CTE Scan on summary s (cost=0.00..221.50 rows=11,075 width=720) (actual time=1,176.830..1,256.161 rows=24 loops=1)

57. 0.168 0.355 ↓ 1.0 1,340 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 83kB
58. 0.187 0.187 ↓ 1.0 1,340 1

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

59. 0.221 0.783 ↑ 1.1 1,413 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 104kB
60. 0.562 0.562 ↑ 1.1 1,413 1

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