explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xnYu

Settings
# exclusive inclusive rows x rows loops node
1. 0.034 399.868 ↑ 4,087.1 25 1

Sort (cost=289,949.40..290,204.84 rows=102,177 width=378) (actual time=399.866..399.868 rows=25 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.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=16) (actual time=0.000..0.000 rows=1 loops=1)

4.          

CTE cu

5. 0.026 0.026 ↑ 1.0 1 1

Seq Scan on royalties_customer cu (cost=0.00..2.26 rows=1 width=4) (actual time=0.021..0.026 rows=1 loops=1)

  • Filter: (id = 1)
  • Rows Removed by Filter: 20
6.          

CTE summary

7. 0.000 398.018 ↑ 4,087.1 25 1

Finalize GroupAggregate (cost=238,141.58..257,506.34 rows=102,177 width=204) (actual time=371.023..398.018 rows=25 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
8.          

Initplan (for Finalize GroupAggregate)

9. 0.028 0.028 ↑ 1.0 1 1

CTE Scan on cu cu_1 (cost=0.00..0.02 rows=1 width=38) (actual time=0.023..0.028 rows=1 loops=1)

10. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on cu cu_2 (cost=0.00..0.02 rows=1 width=38) (actual time=0.000..0.000 rows=1 loops=1)

11. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on cu cu_3 (cost=0.00..0.02 rows=1 width=38) (actual time=0.000..0.000 rows=1 loops=1)

12. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on cu cu_4 (cost=0.00..0.02 rows=1 width=38) (actual time=0.000..0.000 rows=1 loops=1)

13. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on datetime_range (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1)

14. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on cu cu_5 (cost=0.00..0.02 rows=1 width=38) (actual time=0.000..0.000 rows=1 loops=1)

15. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on cu cu_6 (cost=0.00..0.02 rows=1 width=38) (actual time=0.000..0.000 rows=1 loops=1)

16. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on cu cu_7 (cost=0.00..0.02 rows=1 width=38) (actual time=0.000..0.000 rows=1 loops=1)

17. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on cu cu_8 (cost=0.00..0.02 rows=1 width=38) (actual time=0.000..0.000 rows=1 loops=1)

18. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on cu cu_9 (cost=0.00..0.02 rows=1 width=38) (actual time=0.000..0.000 rows=1 loops=1)

19. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on cu cu_10 (cost=0.00..0.02 rows=1 width=38) (actual time=0.000..0.000 rows=1 loops=1)

20. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on cu cu_11 (cost=0.00..0.02 rows=1 width=38) (actual time=0.000..0.000 rows=1 loops=1)

21. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on cu cu_12 (cost=0.00..0.02 rows=1 width=38) (actual time=0.000..0.000 rows=1 loops=1)

22. 0.000 404.786 ↑ 1,637.5 52 1

Gather Merge (cost=238,141.32..252,226.92 rows=85,148 width=204) (actual time=347.107..404.786 rows=52 loops=1)

  • Workers Planned: 2
  • Params Evaluated: $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14
  • Workers Launched: 2
23. 84.801 1,097.409 ↑ 2,504.4 17 3

Partial GroupAggregate (cost=237,141.30..241,398.70 rows=42,574 width=204) (actual time=330.394..365.803 rows=17 loops=3)

  • 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
24. 151.494 1,012.608 ↑ 1.3 32,005 3

Sort (cost=237,141.30..237,247.73 rows=42,574 width=82) (actual time=329.254..337.536 rows=32,005 loops=3)

  • 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: 1952kB
  • Worker 0: Sort Method: external merge Disk: 2360kB
  • Worker 1: Sort Method: external merge Disk: 2000kB
25. 93.723 861.114 ↑ 1.3 32,005 3

Hash Left Join (cost=7,737.98..231,827.35 rows=42,574 width=82) (actual time=53.931..287.038 rows=32,005 loops=3)

  • 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: 2437
26. 60.945 766.857 ↑ 1.2 34,441 3

Hash Left Join (cost=7,710.04..231,590.29 rows=42,789 width=62) (actual time=53.706..255.619 rows=34,441 loops=3)

  • Hash Cond: (r_1.release_id = rg.id)
27. 63.018 703.017 ↑ 1.2 34,441 3

Hash Left Join (cost=7,567.25..231,334.97 rows=42,789 width=58) (actual time=52.724..234.339 rows=34,441 loops=3)

  • Hash Cond: (r_1.track_id = tr.id)
28. 58.449 609.963 ↑ 1.2 34,441 3

Parallel Hash Left Join (cost=7,291.05..230,946.43 rows=42,789 width=58) (actual time=42.588..203.321 rows=34,441 loops=3)

  • Hash Cond: (t.release_id = r_1.id)
29. 476.971 492.345 ↑ 1.2 34,441 3

Parallel Bitmap Heap Scan on royalties_transaction t (cost=2,132.31..225,675.35 rows=42,789 width=50) (actual time=22.722..164.115 rows=34,441 loops=3)

  • Recheck Cond: (customer_id = 1)
  • Heap Blocks: exact=9659
30. 15.374 15.374 ↓ 1.0 103,656 1

Bitmap Index Scan on royalties_transaction_customer_id_2afc02dd (cost=0.00..2,106.63 rows=102,694 width=0) (actual time=15.374..15.374 rows=103,656 loops=1)

  • Index Cond: (customer_id = 1)
31. 22.566 59.169 ↑ 1.2 9,413 3

Parallel Hash (cost=5,011.66..5,011.66 rows=11,766 width=16) (actual time=19.722..19.723 rows=9,413 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 1504kB
32. 36.603 36.603 ↑ 1.2 9,413 3

Parallel Seq Scan on royalties_release r_1 (cost=0.00..5,011.66 rows=11,766 width=16) (actual time=0.018..12.201 rows=9,413 loops=3)

33. 19.548 30.036 ↑ 1.0 6,898 3

Hash (cost=189.98..189.98 rows=6,898 width=8) (actual time=10.012..10.012 rows=6,898 loops=3)

  • Buckets: 8192 Batches: 1 Memory Usage: 307kB
34. 10.488 10.488 ↑ 1.0 6,898 3

Seq Scan on royalties_track tr (cost=0.00..189.98 rows=6,898 width=8) (actual time=0.020..3.496 rows=6,898 loops=3)

35. 1.062 2.895 ↑ 1.0 1,413 3

Hash (cost=125.13..125.13 rows=1,413 width=8) (actual time=0.965..0.965 rows=1,413 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 69kB
36. 1.833 1.833 ↑ 1.0 1,413 3

Seq Scan on royalties_releasegroup rg (cost=0.00..125.13 rows=1,413 width=8) (actual time=0.020..0.611 rows=1,413 loops=3)

37. 0.021 0.534 ↑ 1.0 13 3

Hash (cost=27.77..27.77 rows=13 width=40) (actual time=0.178..0.178 rows=13 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
38. 0.009 0.513 ↑ 1.0 13 3

Subquery Scan on s_1 (cost=27.35..27.77 rows=13 width=40) (actual time=0.159..0.171 rows=13 loops=3)

39. 0.051 0.504 ↑ 1.0 13 3

GroupAggregate (cost=27.35..27.64 rows=13 width=40) (actual time=0.158..0.168 rows=13 loops=3)

  • Group Key: s_2.contract_id, s_2.balance_group_id
40. 0.036 0.453 ↑ 1.0 13 3

Sort (cost=27.35..27.38 rows=13 width=13) (actual time=0.150..0.151 rows=13 loops=3)

  • Sort Key: s_2.contract_id, s_2.balance_group_id
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
41. 0.321 0.417 ↑ 1.0 13 3

Bitmap Heap Scan on royalties_split s_2 (cost=4.38..27.11 rows=13 width=13) (actual time=0.112..0.139 rows=13 loops=3)

  • Recheck Cond: (payee_id = 1305)
  • Filter: (split_amount > '0'::numeric)
  • Heap Blocks: exact=6
42. 0.096 0.096 ↓ 1.1 14 3

Bitmap Index Scan on royalties_split_payee_id_729ebb89 (cost=0.00..4.38 rows=13 width=0) (actual time=0.032..0.032 rows=14 loops=3)

  • Index Cond: (payee_id = 1305)
43. 0.094 399.834 ↑ 4,087.1 25 1

Hash Left Join (cost=211.26..6,127.80 rows=102,177 width=378) (actual time=372.687..399.834 rows=25 loops=1)

  • Hash Cond: (s.ledger_id = l.id)
44. 0.049 399.533 ↑ 4,087.1 25 1

Hash Left Join (cost=185.94..2,767.56 rows=102,177 width=242) (actual time=372.469..399.533 rows=25 loops=1)

  • Hash Cond: (s.release_id = r.id)
45. 0.042 398.440 ↑ 4,087.1 25 1

Hash Left Join (cost=43.15..2,355.75 rows=102,177 width=217) (actual time=371.392..398.440 rows=25 loops=1)

  • Hash Cond: (s.contract_id = c.id)
46. 398.044 398.044 ↑ 4,087.1 25 1

CTE Scan on summary s (cost=0.00..2,043.54 rows=102,177 width=204) (actual time=371.026..398.044 rows=25 loops=1)

47. 0.166 0.354 ↑ 1.0 1,340 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 83kB
48. 0.188 0.188 ↑ 1.0 1,340 1

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

49. 0.366 1.044 ↑ 1.0 1,413 1

Hash (cost=125.13..125.13 rows=1,413 width=29) (actual time=1.044..1.044 rows=1,413 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 104kB
50. 0.678 0.678 ↑ 1.0 1,413 1

Seq Scan on royalties_releasegroup r (cost=0.00..125.13 rows=1,413 width=29) (actual time=0.004..0.678 rows=1,413 loops=1)

51. 0.099 0.207 ↑ 1.0 814 1

Hash (cost=15.14..15.14 rows=814 width=12) (actual time=0.207..0.207 rows=814 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 45kB
52. 0.108 0.108 ↑ 1.0 814 1

Seq Scan on royalties_splitbalancegroup l (cost=0.00..15.14 rows=814 width=12) (actual time=0.013..0.108 rows=814 loops=1)

Planning time : 2.351 ms
Execution time : 407.914 ms