explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NFeR

Settings
# exclusive inclusive rows x rows loops node
1. 0.051 6,317.110 ↑ 4,975.0 8 1

Sort (cost=803,801.60..803,901.10 rows=39,800 width=712) (actual time=6,317.110..6,317.110 rows=8 loops=1)

  • Sort Key: s.total_payable DESC, s.payee_name
  • Sort Method: quicksort Memory: 26kB
2.          

CTE period

3. 0.018 0.040 ↑ 1.0 1 1

Hash Right Join (cost=1.19..3.47 rows=1 width=24) (actual time=0.034..0.040 rows=1 loops=1)

  • Hash Cond: (cu.id = p.customer_id)
4. 0.007 0.007 ↑ 1.0 21 1

Seq Scan on royalties_customer cu (cost=0.00..2.21 rows=21 width=8) (actual time=0.005..0.007 rows=21 loops=1)

5. 0.005 0.015 ↑ 1.0 1 1

Hash (cost=1.18..1.18 rows=1 width=12) (actual time=0.015..0.015 rows=1 loops=1)

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

Seq Scan on royalties_period p (cost=0.00..1.18 rows=1 width=12) (actual time=0.009..0.010 rows=1 loops=1)

  • Filter: (id = 14)
  • Rows Removed by Filter: 13
7.          

CTE transactions

8. 711.734 4,998.496 ↓ 1.4 567,200 1

Hash Left Join (cost=9,917.07..463,070.65 rows=414,879 width=56) (actual time=80.109..4,998.496 rows=567,200 loops=1)

  • Hash Cond: (COALESCE(t.contract_id, r.contract_id, tr.contract_id, rg.contract_id) = s_1.contract_id)
  • Filter: ((((t.transaction_type)::text = 'transfer'::text) AND ((COALESCE(t.contract_id, r.contract_id, tr.contract_id, rg.contract_id) IS NULL) OR (s_1.payee_id = t.payee_id))) OR ((t.transaction_type)::text = ANY ('{income,expense}'::text[])))
  • Rows Removed by Filter: 258
9.          

Initplan (for Hash Left Join)

10. 0.041 0.041 ↑ 1.0 1 1

CTE Scan on period (cost=0.00..0.02 rows=1 width=4) (actual time=0.036..0.041 rows=1 loops=1)

11. 214.521 4,285.196 ↑ 2.1 103,324 1

Hash Left Join (cost=9,816.28..437,616.17 rows=216,654 width=62) (actual time=78.557..4,285.196 rows=103,324 loops=1)

  • Hash Cond: ((SubPlan 2) = rg.id)
12. 37.717 3,036.392 ↑ 2.1 103,324 1

Hash Left Join (cost=9,673.49..346,229.32 rows=216,654 width=62) (actual time=77.391..3,036.392 rows=103,324 loops=1)

  • Hash Cond: (tr.contract_id = trc.id)
13. 68.053 2,998.371 ↑ 2.1 103,324 1

Hash Left Join (cost=9,630.34..345,617.45 rows=216,654 width=62) (actual time=77.062..2,998.371 rows=103,324 loops=1)

  • Hash Cond: (r.track_id = tr.id)
14. 39.247 2,926.215 ↑ 2.1 103,324 1

Hash Left Join (cost=9,354.14..344,772.41 rows=216,654 width=62) (actual time=72.927..2,926.215 rows=103,324 loops=1)

  • Hash Cond: (r.contract_id = rc.id)
15. 46.095 2,886.673 ↑ 2.1 103,324 1

Hash Left Join (cost=9,310.99..344,160.52 rows=216,654 width=62) (actual time=72.614..2,886.673 rows=103,324 loops=1)

  • Hash Cond: (ra.alias_of_id = ra_alias.id)
16. 57.796 2,840.057 ↑ 2.1 103,324 1

Hash Left Join (cost=9,194.64..343,475.32 rows=216,654 width=66) (actual time=72.079..2,840.057 rows=103,324 loops=1)

  • Hash Cond: (r.artist_id = ra.id)
17. 74.909 2,781.370 ↑ 2.1 103,324 1

Hash Left Join (cost=9,078.30..342,788.60 rows=216,654 width=66) (actual time=71.160..2,781.370 rows=103,324 loops=1)

  • Hash Cond: (t.release_id = r.id)
18. 52.232 2,661.338 ↑ 2.1 103,324 1

Hash Left Join (cost=4,540.92..337,682.41 rows=216,654 width=54) (actual time=25.927..2,661.338 rows=103,324 loops=1)

  • Hash Cond: (t.contract_id = c.id)
19. 92.116 2,608.580 ↑ 2.1 103,324 1

Hash Left Join (cost=4,497.77..337,069.15 rows=216,654 width=54) (actual time=25.374..2,608.580 rows=103,324 loops=1)

  • Hash Cond: (t.customer_id = cu_1.id)
20. 2,497.658 2,516.447 ↑ 2.1 103,324 1

Bitmap Heap Scan on royalties_transaction t (cost=4,495.50..334,087.88 rows=216,654 width=54) (actual time=25.332..2,516.447 rows=103,324 loops=1)

  • Recheck Cond: (customer_id = $3)
  • Heap Blocks: exact=28365
21. 18.789 18.789 ↑ 2.1 103,656 1

Bitmap Index Scan on royalties_transaction_customer_id_2afc02dd (cost=0.00..4,441.34 rows=216,654 width=0) (actual time=18.789..18.789 rows=103,656 loops=1)

  • Index Cond: (customer_id = $3)
22. 0.004 0.017 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
23. 0.013 0.013 ↑ 1.0 1 1

Seq Scan on royalties_customer cu_1 (cost=0.00..2.26 rows=1 width=8) (actual time=0.011..0.013 rows=1 loops=1)

  • Filter: (id = $3)
  • Rows Removed by Filter: 20
24. 0.248 0.526 ↑ 1.0 1,340 1

Hash (cost=26.40..26.40 rows=1,340 width=4) (actual time=0.526..0.526 rows=1,340 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 64kB
25. 0.278 0.278 ↑ 1.0 1,340 1

Seq Scan on royalties_contract c (cost=0.00..26.40 rows=1,340 width=4) (actual time=0.007..0.278 rows=1,340 loops=1)

26. 7.151 45.123 ↑ 1.0 28,239 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1397kB
27. 37.972 37.972 ↑ 1.0 28,239 1

Seq Scan on royalties_release r (cost=0.00..4,184.39 rows=28,239 width=16) (actual time=0.005..37.972 rows=28,239 loops=1)

28. 0.196 0.891 ↑ 1.0 1,393 1

Hash (cost=98.93..98.93 rows=1,393 width=8) (actual time=0.891..0.891 rows=1,393 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 66kB
29. 0.695 0.695 ↑ 1.0 1,393 1

Seq Scan on royalties_artist ra (cost=0.00..98.93 rows=1,393 width=8) (actual time=0.092..0.695 rows=1,393 loops=1)

30. 0.222 0.521 ↑ 1.0 1,393 1

Hash (cost=98.93..98.93 rows=1,393 width=4) (actual time=0.521..0.521 rows=1,393 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 65kB
31. 0.299 0.299 ↑ 1.0 1,393 1

Seq Scan on royalties_artist ra_alias (cost=0.00..98.93 rows=1,393 width=4) (actual time=0.003..0.299 rows=1,393 loops=1)

32. 0.165 0.295 ↑ 1.0 1,340 1

Hash (cost=26.40..26.40 rows=1,340 width=4) (actual time=0.295..0.295 rows=1,340 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 64kB
33. 0.130 0.130 ↑ 1.0 1,340 1

Seq Scan on royalties_contract rc (cost=0.00..26.40 rows=1,340 width=4) (actual time=0.006..0.130 rows=1,340 loops=1)

34. 1.064 4.103 ↑ 1.0 6,898 1

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

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

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

36. 0.173 0.304 ↑ 1.0 1,340 1

Hash (cost=26.40..26.40 rows=1,340 width=4) (actual time=0.304..0.304 rows=1,340 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 64kB
37. 0.131 0.131 ↑ 1.0 1,340 1

Seq Scan on royalties_contract trc (cost=0.00..26.40 rows=1,340 width=4) (actual time=0.007..0.131 rows=1,340 loops=1)

38. 0.205 1.108 ↑ 1.0 1,413 1

Hash (cost=125.13..125.13 rows=1,413 width=12) (actual time=1.107..1.108 rows=1,413 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 74kB
39. 0.903 0.903 ↑ 1.0 1,413 1

Seq Scan on royalties_releasegroup rg (cost=0.00..125.13 rows=1,413 width=12) (actual time=0.042..0.903 rows=1,413 loops=1)

40.          

SubPlan (for Hash Left Join)

41. 413.270 1,033.175 ↑ 1.0 1 206,635

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

42. 619.905 619.905 ↑ 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.003..0.003 rows=1 loops=206,635)

  • Index Cond: (release_id = t.release_id)
43. 0.016 1.525 ↑ 5.6 69 1

Hash (cost=95.98..95.98 rows=383 width=44) (actual time=1.525..1.525 rows=69 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
44. 0.009 1.509 ↑ 5.6 69 1

Subquery Scan on s_1 (cost=87.37..95.98 rows=383 width=44) (actual time=1.476..1.509 rows=69 loops=1)

45. 0.255 1.500 ↑ 5.6 69 1

HashAggregate (cost=87.37..92.15 rows=383 width=84) (actual time=1.475..1.500 rows=69 loops=1)

  • Group Key: s_2.contract_id, s_2.balance_group_id, s_2.payee_id
46.          

Initplan (for HashAggregate)

47. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on period period_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1)

48. 0.240 1.243 ↑ 5.6 69 1

Hash Join (cost=23.53..83.52 rows=383 width=17) (actual time=0.328..1.243 rows=69 loops=1)

  • Hash Cond: (s_2.contract_id = c_1.id)
49. 0.694 0.694 ↑ 1.0 2,690 1

Seq Scan on royalties_split s_2 (cost=0.00..52.90 rows=2,690 width=17) (actual time=0.005..0.694 rows=2,690 loops=1)

50. 0.009 0.309 ↑ 8.0 24 1

Hash (cost=21.15..21.15 rows=191 width=4) (actual time=0.309..0.309 rows=24 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
51. 0.011 0.300 ↑ 8.0 24 1

Bitmap Heap Scan on royalties_contract c_1 (cost=5.76..21.15 rows=191 width=4) (actual time=0.294..0.300 rows=24 loops=1)

  • Recheck Cond: (customer_id = $4)
  • Heap Blocks: exact=3
52. 0.289 0.289 ↑ 7.1 27 1

Bitmap Index Scan on royalties_contract_customer_id_99fcc438 (cost=0.00..5.71 rows=191 width=0) (actual time=0.289..0.289 rows=27 loops=1)

  • Index Cond: (customer_id = $4)
53.          

CTE ledger_summary

54. 415.423 6,316.058 ↑ 4,000.0 10 1

GroupAggregate (cost=297,955.29..315,267.49 rows=40,000 width=136) (actual time=5,798.954..6,316.058 rows=10 loops=1)

  • Group Key: t_1.ledger_id, t_1.payee_id
55.          

Initplan (for GroupAggregate)

56. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on period period_2 (cost=0.00..0.02 rows=1 width=8) (actual time=0.000..0.001 rows=1 loops=1)

57. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on period period_3 (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1)

58. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on period period_4 (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1)

59. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on period period_5 (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1)

60. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on period period_6 (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1)

61. 0.003 0.003 ↑ 1.0 1 1

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

62. 397.550 5,900.627 ↓ 1.4 567,189 1

Sort (cost=297,955.17..298,987.19 rows=412,805 width=682) (actual time=5,790.793..5,900.627 rows=567,189 loops=1)

  • Sort Key: t_1.ledger_id, t_1.payee_id
  • Sort Method: external merge Disk: 23456kB
63. 5,503.077 5,503.077 ↓ 1.4 567,189 1

CTE Scan on transactions t_1 (cost=0.00..8,297.58 rows=412,805 width=682) (actual time=80.112..5,503.077 rows=567,189 loops=1)

  • Filter: (payee_id IS NOT NULL)
  • Rows Removed by Filter: 11
64.          

CTE combined_summary

65. 0.049 6,316.684 ↑ 4,975.0 8 1

GroupAggregate (cost=6,964.87..8,954.87 rows=39,800 width=177) (actual time=6,316.665..6,316.684 rows=8 loops=1)

  • Group Key: s_3.payee_id, p_1.name
66. 0.038 6,316.635 ↑ 3,980.0 10 1

Sort (cost=6,964.87..7,064.37 rows=39,800 width=145) (actual time=6,316.634..6,316.635 rows=10 loops=1)

  • Sort Key: s_3.payee_id, p_1.name
  • Sort Method: quicksort Memory: 26kB
67. 0.044 6,316.597 ↑ 3,980.0 10 1

Hash Left Join (cost=22.95..928.05 rows=39,800 width=145) (actual time=5,799.433..6,316.597 rows=10 loops=1)

  • Hash Cond: (s_3.payee_id = p_1.id)
68. 6,316.091 6,316.091 ↑ 3,980.0 10 1

CTE Scan on ledger_summary s_3 (cost=0.00..800.00 rows=39,800 width=132) (actual time=5,798.958..6,316.091 rows=10 loops=1)

  • Filter: (payee_id IS NOT NULL)
69. 0.300 0.462 ↑ 1.0 709 1

Hash (cost=14.09..14.09 rows=709 width=17) (actual time=0.462..0.462 rows=709 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
70. 0.162 0.162 ↑ 1.0 709 1

Seq Scan on royalties_payee p_1 (cost=0.00..14.09 rows=709 width=17) (actual time=0.025..0.162 rows=709 loops=1)

71.          

CTE payouts

72. 0.001 0.354 ↓ 0.0 0 1

HashAggregate (cost=1.02..1.03 rows=1 width=36) (actual time=0.354..0.354 rows=0 loops=1)

  • Group Key: p_2.payee_id
73. 0.353 0.353 ↓ 0.0 0 1

Seq Scan on royalties_payout p_2 (cost=0.00..1.01 rows=1 width=9) (actual time=0.353..0.353 rows=0 loops=1)

  • Filter: (period_id = 14)
  • Rows Removed by Filter: 1
74. 0.014 6,317.059 ↑ 4,975.0 8 1

Hash Left Join (cost=0.03..947.27 rows=39,800 width=712) (actual time=6,317.034..6,317.059 rows=8 loops=1)

  • Hash Cond: (s.payee_id = payouts.payee_id)
75. 6,316.690 6,316.690 ↑ 4,975.0 8 1

CTE Scan on combined_summary s (cost=0.00..796.00 rows=39,800 width=680) (actual time=6,316.667..6,316.690 rows=8 loops=1)

76. 0.001 0.355 ↓ 0.0 0 1

Hash (cost=0.02..0.02 rows=1 width=36) (actual time=0.355..0.355 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
77. 0.354 0.354 ↓ 0.0 0 1

CTE Scan on payouts (cost=0.00..0.02 rows=1 width=36) (actual time=0.354..0.354 rows=0 loops=1)

Planning time : 32.048 ms
Execution time : 6,331.824 ms