explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2lLc

Settings
# exclusive inclusive rows x rows loops node
1. 0.028 935.958 ↑ 461.5 24 1

Sort (cost=395,411.45..395,439.14 rows=11,075 width=377) (actual time=935.957..935.958 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. 85.930 725.951 ↑ 1.2 96,014 1

Hash Left Join (cost=7,801.78..304,464.77 rows=110,747 width=92) (actual time=93.725..725.951 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. 94.068 639.937 ↑ 1.1 103,324 1

Hash Left Join (cost=7,778.06..303,382.27 rows=111,304 width=100) (actual time=93.630..639.937 rows=103,324 loops=1)

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

Gather (cost=7,633.47..256,375.40 rows=111,304 width=96) (actual time=92.965..131.967 rows=103,324 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 13.142 219.723 ↑ 1.3 34,441 3

Hash Left Join (cost=6,633.47..244,245.00 rows=46,377 width=96) (actual time=83.852..219.723 rows=34,441 loops=3)

  • Hash Cond: (r_1.track_id = tr.id)
9. 16.512 200.499 ↑ 1.3 34,441 3

Parallel Hash Left Join (cost=6,361.14..243,850.89 rows=46,377 width=96) (actual time=77.725..200.499 rows=34,441 loops=3)

  • Hash Cond: (t.release_id = r_1.id)
10. 14.006 151.598 ↑ 1.3 34,441 3

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

  • Hash Cond: (t.customer_id = cu.id)
11. 105.178 137.564 ↑ 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=44.376..137.564 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=90
12. 32.386 32.386 ↑ 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=32.386..32.386 rows=103,656 loops=1)

  • Index Cond: (customer_id = 1)
13. 0.004 0.028 ↑ 1.0 1 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.024 0.024 ↑ 1.0 1 3

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

  • Filter: (id = 1)
  • Rows Removed by Filter: 20
15. 5.717 32.389 ↑ 1.2 9,413 3

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1408kB
16. 26.672 26.672 ↑ 1.2 9,413 3

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

17. 1.775 6.082 ↓ 1.0 6,898 3

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

  • Buckets: 8192 Batches: 1 Memory Usage: 307kB
18. 4.307 4.307 ↓ 1.0 6,898 3

Seq Scan on royalties_track tr (cost=0.00..188.26 rows=6,726 width=8) (actual time=0.012..4.307 rows=6,898 loops=3)

19. 0.239 0.632 ↑ 1.1 1,413 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 69kB
20. 0.393 0.393 ↑ 1.1 1,413 1

Seq Scan on royalties_releasegroup rg (cost=0.00..125.93 rows=1,493 width=8) (actual time=0.006..0.393 rows=1,413 loops=1)

21.          

SubPlan (for Hash Left Join)

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

23. 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)
24. 0.003 0.084 ↓ 1.4 13 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
25. 0.003 0.081 ↓ 1.4 13 1

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

26. 0.013 0.078 ↓ 1.4 13 1

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

  • Group Key: s_2.contract_id, s_2.balance_group_id
27. 0.008 0.065 ↓ 1.4 13 1

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

  • Sort Key: s_2.contract_id, s_2.balance_group_id
  • Sort Method: quicksort Memory: 25kB
28. 0.037 0.057 ↓ 1.4 13 1

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

  • Recheck Cond: (payee_id = 1305)
  • Filter: (split_amount > '0'::numeric)
  • Heap Blocks: exact=6
29. 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)
30.          

CTE summary

31. 51.036 934.453 ↑ 461.5 24 1

GroupAggregate (cost=81,906.85..87,416.52 rows=11,075 width=204) (actual time=850.436..934.453 rows=24 loops=1)

  • Group Key: t_1.contract_id, t_1.split_amount, t_1.ledger_id, t_1.release_id
32.          

Initplan (for GroupAggregate)

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

34. 86.166 883.414 ↑ 1.2 96,014 1

Sort (cost=81,906.83..82,183.69 rows=110,747 width=718) (actual time=847.342..883.414 rows=96,014 loops=1)

  • Sort Key: t_1.contract_id, t_1.split_amount, t_1.ledger_id, t_1.release_id
  • Sort Method: external merge Disk: 4832kB
35. 797.248 797.248 ↑ 1.2 96,014 1

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

36. 0.061 935.930 ↑ 461.5 24 1

Hash Left Join (cost=212.90..854.19 rows=11,075 width=377) (actual time=851.785..935.930 rows=24 loops=1)

  • Hash Cond: (s.ledger_id = l.id)
37. 0.020 935.677 ↑ 461.5 24 1

Hash Left Join (cost=187.63..467.44 rows=11,075 width=241) (actual time=851.585..935.677 rows=24 loops=1)

  • Hash Cond: (s.release_id = r.id)
38. 0.029 934.990 ↑ 461.5 24 1

Hash Left Join (cost=43.04..293.70 rows=11,075 width=217) (actual time=850.913..934.990 rows=24 loops=1)

  • Hash Cond: (s.contract_id = c.id)
39. 934.496 934.496 ↑ 461.5 24 1

CTE Scan on summary s (cost=0.00..221.50 rows=11,075 width=204) (actual time=850.438..934.496 rows=24 loops=1)

40. 0.184 0.465 ↓ 1.0 1,340 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 83kB
41. 0.281 0.281 ↓ 1.0 1,340 1

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

42. 0.204 0.667 ↑ 1.1 1,413 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 104kB
43. 0.463 0.463 ↑ 1.1 1,413 1

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

44. 0.095 0.192 ↓ 1.0 814 1

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

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

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

Planning time : 2.390 ms
Execution time : 942.176 ms