explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7o5q

Settings
# exclusive inclusive rows x rows loops node
1. 0.027 1,245.113 ↑ 4,257.4 24 1

Sort (cost=343,223.90..343,479.34 rows=102,177 width=378) (actual time=1,245.112..1,245.113 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 cu

5. 0.023 0.023 ↑ 1.0 1 1

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

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

CTE summary

7. 56.473 1,243.524 ↑ 4,257.4 24 1

GroupAggregate (cost=300,563.14..310,780.84 rows=102,177 width=204) (actual time=1,155.366..1,243.524 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
8.          

Initplan (for GroupAggregate)

9. 0.000 0.000 ↓ 0.0 0

CTE Scan on cu cu_1 (cost=0.00..0.02 rows=1 width=38) (never executed)

10. 0.000 0.000 ↓ 0.0 0

CTE Scan on cu cu_2 (cost=0.00..0.02 rows=1 width=38) (never executed)

11. 0.000 0.000 ↓ 0.0 0

CTE Scan on cu cu_3 (cost=0.00..0.02 rows=1 width=38) (never executed)

12. 0.000 0.000 ↓ 0.0 0

CTE Scan on cu cu_4 (cost=0.00..0.02 rows=1 width=38) (never executed)

13. 0.002 0.002 ↑ 1.0 1 1

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

14. 0.026 0.026 ↑ 1.0 1 1

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

15. 0.000 0.000 ↓ 0.0 0

CTE Scan on cu cu_6 (cost=0.00..0.02 rows=1 width=38) (never executed)

16. 0.001 0.001 ↑ 1.0 1 1

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

17. 0.000 0.000 ↓ 0.0 0

CTE Scan on cu cu_8 (cost=0.00..0.02 rows=1 width=38) (never executed)

18. 0.001 0.001 ↑ 1.0 1 1

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

19. 0.000 0.000 ↓ 0.0 0

CTE Scan on cu cu_10 (cost=0.00..0.02 rows=1 width=38) (never executed)

20. 0.001 0.001 ↑ 1.0 1 1

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

21. 0.000 0.000 ↓ 0.0 0

CTE Scan on cu cu_12 (cost=0.00..0.02 rows=1 width=38) (never executed)

22. 102.776 1,187.020 ↑ 1.1 96,014 1

Sort (cost=300,562.88..300,818.32 rows=102,177 width=82) (actual time=1,151.788..1,187.020 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: 6312kB
23. 83.808 1,084.244 ↑ 1.1 96,014 1

Hash Left Join (cost=7,116.62..287,171.89 rows=102,177 width=82) (actual time=82.764..1,084.244 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
24. 151.978 998.156 ↓ 1.0 103,324 1

Hash Left Join (cost=7,088.68..286,642.04 rows=102,694 width=62) (actual time=80.470..998.156 rows=103,324 loops=1)

  • Hash Cond: ((SubPlan 16) = rg.id)
25. 33.792 431.477 ↓ 1.0 103,324 1

Hash Left Join (cost=6,945.89..243,247.77 rows=102,694 width=58) (actual time=78.787..431.477 rows=103,324 loops=1)

  • Hash Cond: (r_1.track_id = tr.id)
26. 46.705 390.065 ↓ 1.0 103,324 1

Hash Left Join (cost=6,669.69..242,701.93 rows=102,694 width=58) (actual time=71.134..390.065 rows=103,324 loops=1)

  • Hash Cond: (t.release_id = r_1.id)
27. 283.515 305.525 ↓ 1.0 103,324 1

Bitmap Heap Scan on royalties_transaction t (cost=2,132.31..237,894.94 rows=102,694 width=50) (actual time=33.272..305.525 rows=103,324 loops=1)

  • Recheck Cond: (customer_id = 1)
  • Heap Blocks: exact=28365
28. 22.010 22.010 ↓ 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=22.010..22.010 rows=103,656 loops=1)

  • Index Cond: (customer_id = 1)
29. 6.694 37.835 ↑ 1.0 28,239 1

Hash (cost=4,184.39..4,184.39 rows=28,239 width=12) (actual time=37.835..37.835 rows=28,239 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1291kB
30. 31.141 31.141 ↑ 1.0 28,239 1

Seq Scan on royalties_release r_1 (cost=0.00..4,184.39 rows=28,239 width=12) (actual time=0.089..31.141 rows=28,239 loops=1)

31. 1.931 7.620 ↑ 1.0 6,898 1

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

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

Seq Scan on royalties_track tr (cost=0.00..189.98 rows=6,898 width=8) (actual time=0.271..5.689 rows=6,898 loops=1)

33. 0.636 1.431 ↑ 1.0 1,413 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 69kB
34. 0.795 0.795 ↑ 1.0 1,413 1

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

35.          

SubPlan (for Hash Left Join)

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

37. 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)
38. 0.007 2.280 ↑ 1.0 13 1

Hash (cost=27.77..27.77 rows=13 width=40) (actual time=2.280..2.280 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
39. 0.005 2.273 ↑ 1.0 13 1

Subquery Scan on s_1 (cost=27.35..27.77 rows=13 width=40) (actual time=2.258..2.273 rows=13 loops=1)

40. 0.020 2.268 ↑ 1.0 13 1

GroupAggregate (cost=27.35..27.64 rows=13 width=40) (actual time=2.256..2.268 rows=13 loops=1)

  • Group Key: s_2.contract_id, s_2.balance_group_id
41. 0.017 2.248 ↑ 1.0 13 1

Sort (cost=27.35..27.38 rows=13 width=13) (actual time=2.247..2.248 rows=13 loops=1)

  • Sort Key: s_2.contract_id, s_2.balance_group_id
  • Sort Method: quicksort Memory: 25kB
42. 2.207 2.231 ↑ 1.0 13 1

Bitmap Heap Scan on royalties_split s_2 (cost=4.38..27.11 rows=13 width=13) (actual time=0.393..2.231 rows=13 loops=1)

  • Recheck Cond: (payee_id = 1305)
  • Filter: (split_amount > '0'::numeric)
  • Heap Blocks: exact=6
43. 0.024 0.024 ↓ 1.1 14 1

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

  • Index Cond: (payee_id = 1305)
44. 0.059 1,245.086 ↑ 4,257.4 24 1

Hash Left Join (cost=211.26..6,127.80 rows=102,177 width=378) (actual time=1,156.824..1,245.086 rows=24 loops=1)

  • Hash Cond: (s.ledger_id = l.id)
45. 0.020 1,244.698 ↑ 4,257.4 24 1

Hash Left Join (cost=185.94..2,767.56 rows=102,177 width=242) (actual time=1,156.485..1,244.698 rows=24 loops=1)

  • Hash Cond: (s.release_id = r.id)
46. 0.030 1,243.920 ↑ 4,257.4 24 1

Hash Left Join (cost=43.15..2,355.75 rows=102,177 width=217) (actual time=1,155.723..1,243.920 rows=24 loops=1)

  • Hash Cond: (s.contract_id = c.id)
47. 1,243.546 1,243.546 ↑ 4,257.4 24 1

CTE Scan on summary s (cost=0.00..2,043.54 rows=102,177 width=204) (actual time=1,155.368..1,243.546 rows=24 loops=1)

48. 0.157 0.344 ↑ 1.0 1,340 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 83kB
49. 0.187 0.187 ↑ 1.0 1,340 1

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

50. 0.205 0.758 ↑ 1.0 1,413 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 104kB
51. 0.553 0.553 ↑ 1.0 1,413 1

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

52. 0.134 0.329 ↑ 1.0 814 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 45kB
53. 0.195 0.195 ↑ 1.0 814 1

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

Planning time : 7.501 ms
Execution time : 1,248.036 ms