explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LVVJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.168 8,225.500 ↑ 161.7 101 1

Sort (cost=1,515,888.22..1,515,929.04 rows=16,329 width=380) (actual time=8,225.491..8,225.500 rows=101 loops=1)

  • Sort Key: ((((((s.income + s.expenses) * COALESCE(s.split_amount, '0'::numeric)) + s.transfers) + s.payouts) + s.previous_balance)) DESC
  • Sort Method: quicksort Memory: 42kB
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.017 0.017 ↑ 1.0 1 1

Seq Scan on royalties_customer cu (cost=0.00..2.31 rows=1 width=38) (actual time=0.011..0.017 rows=1 loops=1)

  • Filter: (id = 3)
  • Rows Removed by Filter: 31
6.          

CTE splits

7. 0.077 1.565 ↑ 1.0 29 1

HashAggregate (cost=60.89..61.26 rows=29 width=40) (actual time=1.556..1.565 rows=29 loops=1)

  • Group Key: s_1.contract_id, s_1.ledger_id
8. 0.961 1.488 ↑ 1.0 29 1

Bitmap Heap Scan on royalties_split s_1 (cost=4.51..60.68 rows=29 width=13) (actual time=0.955..1.488 rows=29 loops=1)

  • Recheck Cond: (payee_id = 216)
  • Heap Blocks: exact=4
9. 0.527 0.527 ↓ 1.1 31 1

Bitmap Index Scan on royalties_split_payee_id_729ebb89 (cost=0.00..4.50 rows=29 width=0) (actual time=0.527..0.527 rows=31 loops=1)

  • Index Cond: (payee_id = 216)
10.          

CTE items

11. 9.440 17.887 ↓ 1.8 913 1

Hash Join (cost=0.94..1,184.22 rows=506 width=48) (actual time=1.747..17.887 rows=913 loops=1)

  • Hash Cond: (i.contract_id = s_2.contract_id)
12. 6.849 6.849 ↑ 1.0 58,046 1

Seq Scan on royalties_calculated_item_contract i (cost=0.00..957.25 rows=58,925 width=12) (actual time=0.014..6.849 rows=58,046 loops=1)

13. 0.019 1.598 ↑ 1.0 29 1

Hash (cost=0.58..0.58 rows=29 width=40) (actual time=1.598..1.598 rows=29 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
14. 1.579 1.579 ↑ 1.0 29 1

CTE Scan on splits s_2 (cost=0.00..0.58 rows=29 width=40) (actual time=1.559..1.579 rows=29 loops=1)

15.          

CTE summary

16. 196.500 8,220.083 ↑ 161.7 101 1

HashAggregate (cost=1,511,603.42..1,511,970.83 rows=16,329 width=204) (actual time=8,219.725..8,220.083 rows=101 loops=1)

  • Group Key: "*SELECT* 1".contract_id, "*SELECT* 1".split_amount, "*SELECT* 1".ledger_id, "*SELECT* 1".release_id
17.          

Initplan (for HashAggregate)

18. 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.000..0.001 rows=1 loops=1)

19. 0.001 0.001 ↑ 1.0 1 1

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

20. 0.003 0.003 ↑ 1.0 1 1

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

21. 0.001 0.001 ↑ 1.0 1 1

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

22. 0.000 0.000 ↓ 0.0 0

CTE Scan on datetime_range datetime_range_4 (cost=0.00..0.02 rows=1 width=8) (never executed)

23. 23.576 8,023.577 ↑ 1.5 106,929 1

Result (cost=0.49..1,502,622.32 rows=163,291 width=92) (actual time=1.819..8,023.577 rows=106,929 loops=1)

24. 15.571 8,000.001 ↑ 1.5 106,929 1

Append (cost=0.49..1,500,989.41 rows=163,291 width=92) (actual time=1.818..8,000.001 rows=106,929 loops=1)

25. 23.882 7,922.309 ↑ 1.7 94,119 1

Subquery Scan on *SELECT* 1 (cost=0.49..1,469,823.90 rows=159,639 width=92) (actual time=1.818..7,922.309 rows=94,119 loops=1)

26. 46.064 7,898.427 ↑ 1.7 94,119 1

Nested Loop (cost=0.49..1,468,227.51 rows=159,639 width=92) (actual time=1.817..7,898.427 rows=94,119 loops=1)

27.          

Initplan (for Nested Loop)

28. 0.018 0.018 ↑ 1.0 1 1

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

29. 0.000 0.000 ↓ 0.0 0

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

30. 0.003 0.003 ↑ 1.0 1 1

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

31. 19.715 19.715 ↓ 1.8 913 1

CTE Scan on items i_1 (cost=0.00..10.12 rows=506 width=48) (actual time=1.749..19.715 rows=913 loops=1)

32. 7,832.627 7,832.627 ↑ 3.1 103 913

Index Scan using royalties_transaction_release_id_10b77fbc on royalties_transaction t (cost=0.43..2,896.89 rows=315 width=43) (actual time=0.212..8.579 rows=103 loops=913)

  • Index Cond: (item_id = i_1.item_id)
  • Filter: ((contract_id IS NULL) AND (paid_timestamp < $11) AND ((transaction_type)::text = ANY ('{income,expense}'::text[])))
  • Rows Removed by Filter: 0
33. 1.501 61.771 ↓ 3.5 12,809 1

Subquery Scan on *SELECT* 2 (cost=8,483.59..31,144.22 rows=3,651 width=92) (actual time=41.103..61.771 rows=12,809 loops=1)

34. 4.263 60.270 ↓ 3.5 12,809 1

Hash Left Join (cost=8,483.59..31,107.71 rows=3,651 width=92) (actual time=41.102..60.270 rows=12,809 loops=1)

  • Hash Cond: (t_1.item_id = i_2.id)
35.          

Initplan (for Hash Left Join)

36. 0.002 0.002 ↑ 1.0 1 1

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

37. 0.000 0.000 ↓ 0.0 0

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

38. 0.003 0.003 ↑ 1.0 1 1

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

39. 3.234 16.854 ↓ 3.5 12,809 1

Hash Left Join (cost=1.38..22,597.66 rows=3,651 width=83) (actual time=1.058..16.854 rows=12,809 loops=1)

  • Hash Cond: (t_1.contract_id = s_3.contract_id)
40. 13.591 13.591 ↓ 3.5 12,809 1

Index Scan using royalties_transaction_contract_id_99579882 on royalties_transaction t_1 (cost=0.43..22,582.98 rows=3,651 width=47) (actual time=0.985..13.591 rows=12,809 loops=1)

  • Index Cond: (contract_id IS NOT NULL)
  • Filter: ((paid_timestamp < $14) AND ((transaction_type)::text = ANY ('{income,expense}'::text[])))
  • Rows Removed by Filter: 1218
41. 0.018 0.029 ↑ 1.0 29 1

Hash (cost=0.58..0.58 rows=29 width=40) (actual time=0.029..0.029 rows=29 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
42. 0.011 0.011 ↑ 1.0 29 1

CTE Scan on splits s_3 (cost=0.00..0.58 rows=29 width=40) (actual time=0.004..0.011 rows=29 loops=1)

43. 14.839 39.148 ↑ 1.2 58,046 1

Hash (cost=7,593.18..7,593.18 rows=71,118 width=8) (actual time=39.148..39.148 rows=58,046 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 3275kB
44. 24.309 24.309 ↑ 1.2 58,046 1

Seq Scan on royalties_item i_2 (cost=0.00..7,593.18 rows=71,118 width=8) (actual time=0.031..24.309 rows=58,046 loops=1)

45. 0.001 0.350 ↑ 1.0 1 1

Subquery Scan on *SELECT* 3 (cost=20.57..21.29 rows=1 width=92) (actual time=0.344..0.350 rows=1 loops=1)

46. 0.037 0.349 ↑ 1.0 1 1

Hash Right Join (cost=20.57..21.28 rows=1 width=92) (actual time=0.343..0.349 rows=1 loops=1)

  • Hash Cond: (s_4.contract_id = COALESCE(t_2.contract_id, i_3.contract_id))
47.          

Initplan (for Hash Right Join)

48. 0.001 0.001 ↑ 1.0 1 1

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

49. 0.000 0.000 ↓ 0.0 0

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

50. 0.002 0.002 ↑ 1.0 1 1

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

51. 0.004 0.004 ↑ 1.0 29 1

CTE Scan on splits s_4 (cost=0.00..0.58 rows=29 width=40) (actual time=0.001..0.004 rows=29 loops=1)

52. 0.005 0.305 ↑ 1.0 1 1

Hash (cost=20.50..20.50 rows=1 width=51) (actual time=0.305..0.305 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
53. 0.110 0.300 ↑ 1.0 1 1

Hash Right Join (cost=8.47..20.50 rows=1 width=51) (actual time=0.229..0.300 rows=1 loops=1)

  • Hash Cond: (i_3.item_id = t_2.item_id)
54. 0.074 0.074 ↓ 1.8 913 1

CTE Scan on items i_3 (cost=0.00..10.12 rows=506 width=12) (actual time=0.001..0.074 rows=913 loops=1)

55. 0.005 0.116 ↑ 1.0 1 1

Hash (cost=8.46..8.46 rows=1 width=47) (actual time=0.116..0.116 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
56. 0.111 0.111 ↑ 1.0 1 1

Index Scan using royalties_transaction_payee_id_78082bd6 on royalties_transaction t_2 (cost=0.43..8.46 rows=1 width=47) (actual time=0.110..0.111 rows=1 loops=1)

  • Index Cond: (payee_id = 216)
  • Filter: ((paid_timestamp < $17) AND (customer_id = 3) AND ((transaction_type)::text = 'transfer'::text))
57. 0.286 8,225.332 ↑ 161.7 101 1

Hash Left Join (cost=580.92..1,526.96 rows=16,329 width=380) (actual time=8,224.511..8,225.332 rows=101 loops=1)

  • Hash Cond: (s.ledger_id = l.id)
58. 0.102 8,224.918 ↑ 161.7 101 1

Hash Left Join (cost=569.66..982.06 rows=16,329 width=245) (actual time=8,224.345..8,224.918 rows=101 loops=1)

  • Hash Cond: (s.release_id = r.id)
59. 0.137 8,222.345 ↑ 161.7 101 1

Hash Left Join (cost=141.31..510.80 rows=16,329 width=223) (actual time=8,221.828..8,222.345 rows=101 loops=1)

  • Hash Cond: (s.contract_id = c.id)
60. 8,220.163 8,220.163 ↑ 161.7 101 1

CTE Scan on summary s (cost=0.00..326.58 rows=16,329 width=204) (actual time=8,219.728..8,220.163 rows=101 loops=1)

61. 1.120 2.045 ↑ 1.0 4,125 1

Hash (cost=89.47..89.47 rows=4,147 width=23) (actual time=2.045..2.045 rows=4,125 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 293kB
62. 0.925 0.925 ↑ 1.0 4,125 1

Seq Scan on royalties_contract c (cost=0.00..89.47 rows=4,147 width=23) (actual time=0.010..0.925 rows=4,125 loops=1)

63. 1.003 2.471 ↓ 1.0 3,951 1

Hash (cost=379.27..379.27 rows=3,927 width=26) (actual time=2.471..2.471 rows=3,951 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 266kB
64. 1.468 1.468 ↓ 1.0 3,951 1

Seq Scan on royalties_release r (cost=0.00..379.27 rows=3,927 width=26) (actual time=0.012..1.468 rows=3,951 loops=1)

65. 0.058 0.128 ↓ 1.1 213 1

Hash (cost=8.89..8.89 rows=189 width=11) (actual time=0.128..0.128 rows=213 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
66. 0.070 0.070 ↓ 1.1 213 1

Seq Scan on royalties_ledger l (cost=0.00..8.89 rows=189 width=11) (actual time=0.015..0.070 rows=213 loops=1)

Planning time : 2.853 ms
Execution time : 8,226.708 ms