explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pexy

Settings
# exclusive inclusive rows x rows loops node
1. 0.037 1,485.439 ↑ 461.5 24 1

Sort (cost=702,437.96..702,465.64 rows=11,075 width=2,396) (actual time=1,485.438..1,485.439 rows=24 loops=1)

  • Sort Key: ((((COALESCE(((s.income + s.expenses) * s.split_amount), '0'::numeric) + COALESCE(s.transfers, '0'::numeric)) + COALESCE(s.payouts, '0'::numeric)) + COALESCE(pb.previous_balance, '0'::numeric))) 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 splits

5. 0.013 0.096 ↓ 1.4 13 1

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

  • Group Key: s_1.contract_id, s_1.balance_group_id
6. 0.015 0.083 ↓ 1.4 13 1

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

  • Sort Key: s_1.contract_id, s_1.balance_group_id
  • Sort Method: quicksort Memory: 25kB
7. 0.045 0.068 ↓ 1.4 13 1

Bitmap Heap Scan on royalties_split s_1 (cost=4.35..23.17 rows=9 width=13) (actual time=0.035..0.068 rows=13 loops=1)

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

  • Index Cond: (payee_id = 1305)
9.          

CTE transactions

10. 61.758 1,098.662 ↑ 1.2 96,014 1

Hash Left Join (cost=8,197.91..306,273.78 rows=110,747 width=640) (actual time=52.918..1,098.662 rows=96,014 loops=1)

  • Hash Cond: (rg.contract_id = rgc.id)
11. 90.872 1,036.339 ↑ 1.2 96,014 1

Hash Left Join (cost=8,154.88..305,385.80 rows=110,747 width=203) (actual time=52.340..1,036.339 rows=96,014 loops=1)

  • Hash Cond: (COALESCE(t.contract_id, r.contract_id, tr.contract_id, rg.contract_id) = s_2.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_2.contract_id IS NOT NULL)))
  • Rows Removed by Filter: 7310
12. 226.916 945.191 ↑ 1.1 103,324 1

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

  • Hash Cond: ((SubPlan 3) = rg.id)
13. 29.191 303.782 ↑ 1.1 103,324 1

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

  • Hash Cond: (tr.contract_id = trc.id)
14. 43.138 274.237 ↑ 1.1 103,324 1

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

  • Hash Cond: (r.track_id = tr.id)
15. 37.275 227.722 ↑ 1.1 103,324 1

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

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

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

  • Workers Planned: 2
  • Workers Launched: 2
17. 10.579 198.464 ↑ 1.3 34,441 3

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

  • Hash Cond: (ra.alias_of_id = ra_alias.id)
18. 11.809 187.354 ↑ 1.3 34,441 3

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

  • Hash Cond: (r.artist_id = ra.id)
19. 16.262 174.905 ↑ 1.3 34,441 3

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

  • Hash Cond: (t.release_id = r.id)
20. 12.002 138.964 ↑ 1.3 34,441 3

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

  • Hash Cond: (t.contract_id = c.id)
21. 13.633 126.618 ↑ 1.3 34,441 3

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

  • Hash Cond: (t.customer_id = cu.id)
22. 102.154 112.946 ↑ 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=18.321..112.946 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
23. 10.792 10.792 ↑ 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=10.792..10.792 rows=103,656 loops=1)

  • Index Cond: (customer_id = 1)
24. 0.004 0.039 ↑ 1.0 1 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
25. 0.035 0.035 ↑ 1.0 1 3

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

  • Filter: (id = 1)
  • Rows Removed by Filter: 20
26. 0.183 0.344 ↓ 1.0 1,340 3

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

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

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

28. 2.835 19.679 ↑ 1.2 9,413 3

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1504kB
29. 16.844 16.844 ↑ 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.048..16.844 rows=9,413 loops=3)

30. 0.223 0.640 ↓ 1.1 1,393 3

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

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

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

32. 0.250 0.531 ↓ 1.1 1,393 3

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

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

34. 0.184 0.356 ↓ 1.0 1,340 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 83kB
35. 0.172 0.172 ↓ 1.0 1,340 1

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

36. 1.143 3.377 ↓ 1.0 6,898 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 307kB
37. 2.234 2.234 ↓ 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.234 rows=6,898 loops=1)

38. 0.191 0.354 ↓ 1.0 1,340 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 83kB
39. 0.163 0.163 ↓ 1.0 1,340 1

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

40. 0.265 1.223 ↑ 1.1 1,413 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 112kB
41. 0.958 0.958 ↑ 1.1 1,413 1

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

42.          

SubPlan (for Hash Left Join)

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

44. 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)
45. 0.004 0.276 ↓ 1.4 13 1

Hash (cost=18.55..18.55 rows=9 width=48) (actual time=0.276..0.276 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
46. 0.076 0.272 ↓ 1.4 13 1

Hash Right Join (cost=0.29..18.55 rows=9 width=48) (actual time=0.269..0.272 rows=13 loops=1)

  • Hash Cond: (l.id = s_2.ledger_id)
47. 0.088 0.088 ↓ 1.0 814 1

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

48. 0.004 0.108 ↓ 1.4 13 1

Hash (cost=0.18..0.18 rows=9 width=40) (actual time=0.108..0.108 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
49. 0.104 0.104 ↓ 1.4 13 1

CTE Scan on splits s_2 (cost=0.00..0.18 rows=9 width=40) (actual time=0.089..0.104 rows=13 loops=1)

50. 0.191 0.565 ↓ 1.0 1,340 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 83kB
51. 0.374 0.374 ↓ 1.0 1,340 1

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

52.          

CTE previous_balances

53. 0.000 11.792 ↓ 0.0 0 1

GroupAggregate (cost=77,469.27..78,712.79 rows=10,883 width=2,140) (actual time=11.792..11.792 rows=0 loops=1)

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

Initplan (for GroupAggregate)

55. 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.002..0.003 rows=1 loops=1)

56. 0.005 11.791 ↓ 0.0 0 1

Sort (cost=77,469.25..77,561.54 rows=36,916 width=2,258) (actual time=11.791..11.791 rows=0 loops=1)

  • Sort Key: t_1.contract_id, t_1.contract_name, t_1.ledger_id, t_1.ledger_name, t_1.release_id, t_1.release_name, t_1.release_catalog_number, t_1.split_amount
  • Sort Method: quicksort Memory: 25kB
57. 11.786 11.786 ↓ 0.0 0 1

CTE Scan on transactions t_1 (cost=0.00..2,491.81 rows=36,916 width=2,258) (actual time=11.786..11.786 rows=0 loops=1)

  • Filter: (paid_timestamp < $6)
  • Rows Removed by Filter: 96014
58.          

CTE summary

59. 62.355 1,473.444 ↑ 461.5 24 1

GroupAggregate (cost=276,464.83..281,946.81 rows=11,075 width=2,236) (actual time=1,341.320..1,473.444 rows=24 loops=1)

  • Group Key: t_2.contract_id, t_2.contract_name, t_2.split_amount, t_2.ledger_id, t_2.ledger_name, t_2.release_id, t_2.release_name, t_2.release_catalog_number
60. 188.230 1,411.089 ↑ 1.2 96,014 1

Sort (cost=276,464.83..276,741.69 rows=110,747 width=2,774) (actual time=1,335.781..1,411.089 rows=96,014 loops=1)

  • Sort Key: t_2.contract_id, t_2.contract_name, t_2.split_amount, t_2.ledger_id, t_2.ledger_name, t_2.release_id, t_2.release_name, t_2.release_catalog_number
  • Sort Method: external merge Disk: 7256kB
61. 1,222.859 1,222.859 ↑ 1.2 96,014 1

CTE Scan on transactions t_2 (cost=0.00..2,214.94 rows=110,747 width=2,774) (actual time=52.921..1,222.859 rows=96,014 loops=1)

62. 0.057 1,485.402 ↑ 461.5 24 1

Merge Full Join (cost=22,709.82..23,264.09 rows=11,075 width=2,396) (actual time=1,485.355..1,485.402 rows=24 loops=1)

  • Merge Cond: ((s.contract_id = pb.contract_id) AND (s.release_id = pb.release_id))
63. 0.066 1,473.544 ↑ 461.5 24 1

Sort (cost=11,678.96..11,706.65 rows=11,075 width=2,236) (actual time=1,473.542..1,473.544 rows=24 loops=1)

  • Sort Key: s.contract_id, s.release_id
  • Sort Method: quicksort Memory: 28kB
64. 1,473.478 1,473.478 ↑ 461.5 24 1

CTE Scan on summary s (cost=0.00..221.50 rows=11,075 width=2,236) (actual time=1,341.325..1,473.478 rows=24 loops=1)

65. 0.003 11.801 ↓ 0.0 0 1

Materialize (cost=11,030.85..11,085.27 rows=10,883 width=2,140) (actual time=11.801..11.801 rows=0 loops=1)

66. 0.005 11.798 ↓ 0.0 0 1

Sort (cost=11,030.85..11,058.06 rows=10,883 width=2,140) (actual time=11.798..11.798 rows=0 loops=1)

  • Sort Key: pb.contract_id, pb.release_id
  • Sort Method: quicksort Memory: 25kB
67. 11.793 11.793 ↓ 0.0 0 1

CTE Scan on previous_balances pb (cost=0.00..217.66 rows=10,883 width=2,140) (actual time=11.793..11.793 rows=0 loops=1)

Planning time : 21.902 ms
Execution time : 1,494.483 ms