explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mVfF

Settings
# exclusive inclusive rows x rows loops node
1. 0.029 855.945 ↑ 4,614.5 24 1

Sort (cost=364,321.58..364,598.44 rows=110,747 width=377) (actual time=855.944..855.945 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. 59.043 854.407 ↑ 4,614.5 24 1

GroupAggregate (cost=318,036.34..329,111.04 rows=110,747 width=204) (actual time=763.001..854.407 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. 93.258 795.361 ↑ 1.2 96,014 1

Sort (cost=318,036.32..318,313.19 rows=110,747 width=120) (actual time=759.245..795.361 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. 74.805 702.103 ↑ 1.2 96,014 1

Hash Left Join (cost=7,801.78..301,942.93 rows=110,747 width=120) (actual time=72.393..702.103 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. 103.901 627.091 ↑ 1.1 103,324 1

Hash Left Join (cost=7,778.06..301,414.16 rows=111,304 width=100) (actual time=72.177..627.091 rows=103,324 loops=1)

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

Gather (cost=7,633.47..254,407.30 rows=111,304 width=96) (actual time=71.670..109.444 rows=103,324 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
12. 11.835 180.130 ↑ 1.3 34,441 3

Hash Left Join (cost=6,633.47..242,276.90 rows=46,377 width=96) (actual time=63.698..180.130 rows=34,441 loops=3)

  • Hash Cond: (r_1.track_id = tr.id)
13. 18.959 165.336 ↑ 1.3 34,441 3

Parallel Hash Left Join (cost=6,361.14..241,882.79 rows=46,377 width=96) (actual time=60.710..165.336 rows=34,441 loops=3)

  • Hash Cond: (t.release_id = r_1.id)
14. 12.136 113.077 ↑ 1.3 34,441 3

Hash Left Join (cost=2,197.26..237,597.15 rows=46,377 width=88) (actual time=21.312..113.077 rows=34,441 loops=3)

  • Hash Cond: (t.customer_id = cu.id)
15. 86.711 100.846 ↑ 1.3 34,441 3

Parallel Bitmap Heap Scan on royalties_transaction t (cost=2,195.04..236,957.24 rows=46,377 width=54) (actual time=21.192..100.846 rows=34,441 loops=3)

  • Recheck Cond: (customer_id = 1)
  • Heap Blocks: exact=129
16. 14.135 14.135 ↑ 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=14.135..14.135 rows=103,656 loops=1)

  • Index Cond: (customer_id = 1)
17. 0.005 0.095 ↑ 1.0 1 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.090 0.090 ↑ 1.0 1 3

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

  • Filter: (id = 1)
  • Rows Removed by Filter: 20
19. 7.654 33.300 ↑ 1.2 9,413 3

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1408kB
20. 25.646 25.646 ↑ 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.028..25.646 rows=9,413 loops=3)

21. 1.200 2.959 ↓ 1.0 6,898 3

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

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

23. 0.156 0.476 ↑ 1.1 1,413 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 69kB
24. 0.320 0.320 ↑ 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.320 rows=1,413 loops=1)

25.          

SubPlan (for Hash Left Join)

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

27. 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)
28. 0.005 0.207 ↓ 1.4 13 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
29. 0.003 0.202 ↓ 1.4 13 1

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

30. 0.014 0.199 ↓ 1.4 13 1

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

  • Group Key: s_2.contract_id, s_2.balance_group_id
31. 0.010 0.185 ↓ 1.4 13 1

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

  • Sort Key: s_2.contract_id, s_2.balance_group_id
  • Sort Method: quicksort Memory: 25kB
32. 0.090 0.175 ↓ 1.4 13 1

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

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

  • Index Cond: (payee_id = 1305)
34. 0.066 855.916 ↑ 4,614.5 24 1

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

  • Hash Cond: (s.ledger_id = l.id)
35. 0.021 855.655 ↑ 4,614.5 24 1

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

  • Hash Cond: (s.release_id = r.id)
36. 0.033 854.856 ↑ 4,614.5 24 1

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

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

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

38. 0.176 0.381 ↓ 1.0 1,340 1

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

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

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

40. 0.221 0.778 ↑ 1.1 1,413 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 104kB
41. 0.557 0.557 ↑ 1.1 1,413 1

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

42. 0.094 0.195 ↓ 1.0 814 1

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

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

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

Planning time : 4.639 ms
Execution time : 861.422 ms