explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JMad

Settings
# exclusive inclusive rows x rows loops node
1. 0.027 1,149.429 ↑ 4,614.5 24 1

Sort (cost=367,769.50..368,046.37 rows=110,747 width=377) (actual time=1,149.428..1,149.429 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. 77.269 1,145.595 ↑ 4,614.5 24 1

GroupAggregate (cost=321,484.26..332,558.96 rows=110,747 width=204) (actual time=1,024.694..1,145.595 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.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. 130.315 1,068.323 ↑ 1.2 96,014 1

Sort (cost=321,484.24..321,761.11 rows=110,747 width=120) (actual time=1,016.112..1,068.323 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. 88.146 938.008 ↑ 1.2 96,014 1

Hash Left Join (cost=8,159.94..305,390.86 rows=110,747 width=120) (actual time=68.061..938.008 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. 183.972 849.783 ↑ 1.1 103,324 1

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

  • Hash Cond: ((SubPlan 3) = rg.id)
11. 25.600 252.046 ↑ 1.1 103,324 1

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

  • Hash Cond: (tr.contract_id = trc.id)
12. 39.856 226.173 ↑ 1.1 103,324 1

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

  • Hash Cond: (r_1.track_id = tr.id)
13. 38.024 183.199 ↑ 1.1 103,324 1

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

  • Hash Cond: (r_1.contract_id = rc.id)
14. 0.000 144.848 ↑ 1.1 103,324 1

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

  • Workers Planned: 2
  • Workers Launched: 2
15. 10.455 214.917 ↑ 1.3 34,441 3

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

  • Hash Cond: (ra.alias_of_id = ra_alias.id)
16. 15.676 204.150 ↑ 1.3 34,441 3

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

  • Hash Cond: (r_1.artist_id = ra.id)
17. 15.896 187.958 ↑ 1.3 34,441 3

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

  • Hash Cond: (t.release_id = r_1.id)
18. 10.938 149.849 ↑ 1.3 34,441 3

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

  • Hash Cond: (t.contract_id = c_1.id)
19. 12.893 138.461 ↑ 1.3 34,441 3

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

  • Hash Cond: (t.customer_id = cu.id)
20. 102.952 125.539 ↑ 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=31.061..125.539 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=1
21. 22.587 22.587 ↑ 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=22.587..22.587 rows=103,656 loops=1)

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

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

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

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

  • Filter: (id = 1)
  • Rows Removed by Filter: 20
24. 0.189 0.450 ↓ 1.0 1,340 3

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

  • Buckets: 2048 Batches: 1 Memory Usage: 64kB
25. 0.261 0.261 ↓ 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.013..0.261 rows=1,340 loops=3)

26. 5.108 22.213 ↑ 1.2 9,413 3

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1472kB
27. 17.105 17.105 ↑ 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.016..17.105 rows=9,413 loops=3)

28. 0.225 0.516 ↓ 1.1 1,393 3

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

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

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

30. 0.160 0.312 ↓ 1.1 1,393 3

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

  • Buckets: 2048 Batches: 1 Memory Usage: 65kB
31. 0.152 0.152 ↓ 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.003..0.152 rows=1,393 loops=3)

32. 0.204 0.327 ↓ 1.0 1,340 1

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

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

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

34. 0.925 3.118 ↓ 1.0 6,898 1

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

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

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

36. 0.146 0.273 ↓ 1.0 1,340 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 64kB
37. 0.127 0.127 ↓ 1.0 1,340 1

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

38. 0.164 0.495 ↑ 1.1 1,413 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 74kB
39. 0.331 0.331 ↑ 1.1 1,413 1

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

40.          

SubPlan (for Hash Left Join)

41. 0.000 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)

42. 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.001..0.002 rows=1 loops=206,635)

  • Index Cond: (release_id = t.release_id)
43. 0.003 0.079 ↓ 1.4 13 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
44. 0.003 0.076 ↓ 1.4 13 1

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

45. 0.012 0.073 ↓ 1.4 13 1

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

  • Group Key: s_2.contract_id, s_2.balance_group_id
46. 0.009 0.061 ↓ 1.4 13 1

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

  • Sort Key: s_2.contract_id, s_2.balance_group_id
  • Sort Method: quicksort Memory: 25kB
47. 0.034 0.052 ↓ 1.4 13 1

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

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

  • Index Cond: (payee_id = 1305)
49. 0.062 1,149.402 ↑ 4,614.5 24 1

Hash Left Join (cost=212.90..6,625.64 rows=110,747 width=377) (actual time=1,028.398..1,149.402 rows=24 loops=1)

  • Hash Cond: (s.ledger_id = l.id)
50. 0.020 1,148.721 ↑ 4,614.5 24 1

Hash Left Join (cost=187.63..2,985.74 rows=110,747 width=241) (actual time=1,027.766..1,148.721 rows=24 loops=1)

  • Hash Cond: (s.release_id = r.id)
51. 0.096 1,146.286 ↑ 4,614.5 24 1

Hash Left Join (cost=43.04..2,549.61 rows=110,747 width=217) (actual time=1,025.344..1,146.286 rows=24 loops=1)

  • Hash Cond: (s.contract_id = c.id)
52. 1,145.619 1,145.619 ↑ 4,614.5 24 1

CTE Scan on summary s (cost=0.00..2,214.94 rows=110,747 width=204) (actual time=1,024.697..1,145.619 rows=24 loops=1)

53. 0.264 0.571 ↓ 1.0 1,340 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 83kB
54. 0.307 0.307 ↓ 1.0 1,340 1

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

55. 0.849 2.415 ↑ 1.1 1,413 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 104kB
56. 1.566 1.566 ↑ 1.1 1,413 1

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

57. 0.314 0.619 ↓ 1.0 814 1

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

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

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

Planning time : 5.363 ms
Execution time : 1,153.443 ms