explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hSuV

Settings
# exclusive inclusive rows x rows loops node
1. 0.027 1,359.993 ↑ 461.5 24 1

Sort (cost=602,912.92..602,940.61 rows=11,075 width=2,396) (actual time=1,359.992..1,359.993 rows=24 loops=1)

  • Sort Key: ((((COALESCE(((s.income + s.expenses) * s.split_amount), '0'::numeric) + 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. 60.198 993.247 ↑ 1.2 96,014 1

Hash Left Join (cost=8,221.34..306,297.21 rows=110,747 width=640) (actual time=65.144..993.247 rows=96,014 loops=1)

  • Hash Cond: (rg.contract_id = rgc.id)
6. 90.607 932.659 ↑ 1.2 96,014 1

Hash Left Join (cost=8,178.30..305,409.22 rows=110,747 width=203) (actual time=64.745..932.659 rows=96,014 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 (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
7. 213.937 841.583 ↑ 1.1 103,324 1

Hash Left Join (cost=8,136.22..304,862.09 rows=111,304 width=163) (actual time=64.264..841.583 rows=103,324 loops=1)

  • Hash Cond: ((SubPlan 2) = rg.id)
8. 26.688 212.903 ↑ 1.1 103,324 1

Hash Left Join (cost=7,991.62..257,855.22 rows=111,304 width=135) (actual time=62.759..212.903 rows=103,324 loops=1)

  • Hash Cond: (tr.contract_id = trc.id)
9. 41.985 185.843 ↑ 1.1 103,324 1

Hash Left Join (cost=7,948.59..257,519.09 rows=111,304 width=122) (actual time=62.379..185.843 rows=103,324 loops=1)

  • Hash Cond: (r.track_id = tr.id)
10. 36.688 140.492 ↑ 1.1 103,324 1

Hash Left Join (cost=7,676.25..256,954.51 rows=111,304 width=122) (actual time=59.006..140.492 rows=103,324 loops=1)

  • Hash Cond: (r.contract_id = rc.id)
11. 0.000 103.513 ↑ 1.1 103,324 1

Gather (cost=7,633.21..256,619.29 rows=111,304 width=109) (actual time=58.712..103.513 rows=103,324 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
12. 12.944 235.011 ↑ 1.3 34,441 3

Hash Left Join (cost=6,633.21..244,488.89 rows=46,377 width=109) (actual time=43.313..235.011 rows=34,441 loops=3)

  • Hash Cond: (ra.alias_of_id = ra_alias.id)
13. 13.199 221.737 ↑ 1.3 34,441 3

Hash Left Join (cost=6,518.69..244,252.60 rows=46,377 width=113) (actual time=42.970..221.737 rows=34,441 loops=3)

  • Hash Cond: (r.artist_id = ra.id)
14. 20.560 207.612 ↑ 1.3 34,441 3

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

  • Hash Cond: (t.release_id = r.id)
15. 14.268 166.078 ↑ 1.3 34,441 3

Hash Left Join (cost=2,240.30..239,730.32 rows=46,377 width=101) (actual time=20.829..166.078 rows=34,441 loops=3)

  • Hash Cond: (t.contract_id = c.id)
16. 16.561 151.227 ↑ 1.3 34,441 3

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

  • Hash Cond: (t.customer_id = cu.id)
17. 121.507 134.635 ↑ 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.141..134.635 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
18. 13.128 13.128 ↑ 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.128..13.128 rows=103,656 loops=1)

  • Index Cond: (customer_id = 1)
19. 0.004 0.031 ↑ 1.0 1 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.027 0.027 ↑ 1.0 1 3

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

  • Filter: (id = 1)
  • Rows Removed by Filter: 20
21. 0.319 0.583 ↓ 1.0 1,340 3

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

  • Buckets: 2048 Batches: 1 Memory Usage: 83kB
22. 0.264 0.264 ↓ 1.0 1,340 3

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

23. 3.809 20.974 ↑ 1.2 9,413 3

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

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

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

25. 0.379 0.926 ↓ 1.1 1,393 3

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

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

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

27. 0.177 0.330 ↓ 1.1 1,393 3

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

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

29. 0.153 0.291 ↓ 1.0 1,340 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 83kB
30. 0.138 0.138 ↓ 1.0 1,340 1

Seq Scan on royalties_contract rc (cost=0.00..26.35 rows=1,335 width=17) (actual time=0.004..0.138 rows=1,340 loops=1)

31. 0.959 3.366 ↓ 1.0 6,898 1

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

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

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

33. 0.210 0.372 ↓ 1.0 1,340 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 83kB
34. 0.162 0.162 ↓ 1.0 1,340 1

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

35. 0.291 1.473 ↑ 1.1 1,413 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 112kB
36. 1.182 1.182 ↑ 1.1 1,413 1

Seq Scan on royalties_releasegroup rg (cost=0.00..125.93 rows=1,493 width=36) (actual time=0.019..1.182 rows=1,413 loops=1)

37.          

SubPlan (for Hash Left Join)

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

39. 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)
40. 0.098 0.469 ↓ 1.4 13 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
41. 0.125 0.371 ↓ 1.4 13 1

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

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

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

43. 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
44. 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.077..0.088 rows=13 loops=1)

45. 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
46. 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
47. 0.043 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.032..0.063 rows=13 loops=1)

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

  • Index Cond: (payee_id = 1305)
49. 0.189 0.390 ↓ 1.0 1,340 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 83kB
50. 0.201 0.201 ↓ 1.0 1,340 1

Seq Scan on royalties_contract rgc (cost=0.00..26.35 rows=1,335 width=17) (actual time=0.009..0.201 rows=1,340 loops=1)

51.          

CTE summary

52. 66.174 1,359.891 ↑ 461.5 24 1

GroupAggregate (cost=277,227.85..283,844.99 rows=11,075 width=2,268) (actual time=1,220.897..1,359.891 rows=24 loops=1)

  • Group Key: t_1.contract_id, t_1.contract_name, t_1.split_amount, t_1.ledger_id, t_1.ledger_name, t_1.release_id, t_1.release_name, t_1.release_catalog_number
53.          

Initplan (for GroupAggregate)

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

55. 185.042 1,293.714 ↑ 1.2 96,014 1

Sort (cost=277,227.83..277,504.69 rows=110,747 width=2,782) (actual time=1,215.494..1,293.714 rows=96,014 loops=1)

  • Sort Key: t_1.contract_id, t_1.contract_name, t_1.split_amount, t_1.ledger_id, t_1.ledger_name, t_1.release_id, t_1.release_name, t_1.release_catalog_number
  • Sort Method: external merge Disk: 8448kB
56. 1,108.672 1,108.672 ↑ 1.2 96,014 1

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

57. 1,359.966 1,359.966 ↑ 461.5 24 1

CTE Scan on summary s (cost=0.00..553.75 rows=11,075 width=2,396) (actual time=1,220.904..1,359.966 rows=24 loops=1)

Planning time : 5.203 ms
Execution time : 1,366.910 ms