explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 51Yf

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 2,975.501 ↑ 1.0 1 1

Nested Loop (cost=647,068.49..647,068.55 rows=1 width=16) (actual time=2,975.500..2,975.501 rows=1 loops=1)

2. 0.133 18.961 ↑ 1.0 1 1

Aggregate (cost=29,864.11..29,864.12 rows=1 width=8) (actual time=18.961..18.961 rows=1 loops=1)

3. 1.282 18.828 ↓ 1,732.0 1,732 1

Nested Loop (cost=29,856.06..29,864.10 rows=1 width=8) (actual time=6.717..18.828 rows=1,732 loops=1)

4. 0.290 7.154 ↓ 1,732.0 1,732 1

Unique (cost=29,855.64..29,855.64 rows=1 width=22) (actual time=6.704..7.154 rows=1,732 loops=1)

5. 3.339 6.864 ↓ 1,732.0 1,732 1

Sort (cost=29,855.64..29,855.64 rows=1 width=22) (actual time=6.703..6.864 rows=1,732 loops=1)

  • Sort Key: b.ledger_name
  • Sort Method: quicksort Memory: 171kB
6. 0.200 3.525 ↓ 1,732.0 1,732 1

Nested Loop (cost=29,658.43..29,855.63 rows=1 width=22) (actual time=2.927..3.525 rows=1,732 loops=1)

7. 0.005 2.889 ↑ 1.0 1 1

Sort (cost=29,658.01..29,658.01 rows=1 width=104) (actual time=2.888..2.889 rows=1 loops=1)

  • Sort Key: a.path_info
  • Sort Method: quicksort Memory: 25kB
8.          

CTE rel_tree

9. 2.789 2.855 ↓ 1.4 30 1

Recursive Union (cost=0.41..29,657.50 rows=22 width=112) (actual time=0.369..2.855 rows=30 loops=1)

  • -> Index Scan using ledger_groups_group_name_organization_id_index on ledger_groups (cost=0.41..5157.87 rows=12 width=112) (actual time=0.367..2.772 rows=1
  • Index Cond: (organization_id = 2198)
  • Filter: (parent_id IS NULL)
  • Rows Removed by Filter: 14
10. 0.032 0.066 ↓ 7.0 7 2

Nested Loop (cost=0.41..2,449.92 rows=1 width=112) (actual time=0.017..0.033 rows=7 loops=2)

11. 0.004 0.004 ↑ 8.0 15 2

WorkTable Scan on rel_tree p (cost=0.00..2.40 rows=120 width=76) (actual time=0.001..0.002 rows=15 loops=2)

12. 0.030 0.030 ↓ 0.0 0 30

Index Scan using idx_parent_id_is_deleted on ledger_groups c (cost=0.41..20.39 rows=1 width=27) (actual time=0.001..0.001 rows=0 loops=30)

  • Index Cond: (parent_id = p.id)
  • Filter: (organization_id = 2198)
13. 2.884 2.884 ↑ 1.0 1 1

CTE Scan on rel_tree a (cost=0.00..0.49 rows=1 width=104) (actual time=2.834..2.884 rows=1 loops=1)

  • Filter: (top_level_group_name[2] = 'Sundry Debtors'::text)
  • Rows Removed by Filter: 29
14. 0.436 0.436 ↓ 1,732.0 1,732 1

Index Scan using idx_is_deleted_organization_id_due_date_group_name on ledgers b (cost=0.42..197.59 rows=1 width=38) (actual time=0.037..0.436 rows=1,732 loops=1)

  • Index Cond: ((organization_id = 2198) AND ((group_name)::text = a.group_name))
15. 10.392 10.392 ↑ 1.0 1 1,732

Index Scan using ledgers_ledger_name_organization_id_index on ledgers (cost=0.42..8.45 rows=1 width=30) (actual time=0.006..0.006 rows=1 loops=1,732)

  • Index Cond: (((ledger_name)::text = (b.ledger_name)::text) AND (organization_id = 2198))
16. 0.962 2,956.535 ↑ 1.0 1 1

Aggregate (cost=617,204.38..617,204.40 rows=1 width=8) (actual time=2,956.535..2,956.535 rows=1 loops=1)

17. 2.007 2,955.573 ↓ 807.2 9,686 1

Nested Loop (cost=29,856.09..617,204.35 rows=12 width=8) (actual time=88.851..2,955.573 rows=9,686 loops=1)

18. 499.857 2,895.450 ↓ 43.2 9,686 1

Hash Join (cost=29,855.67..617,099.58 rows=224 width=41) (actual time=88.828..2,895.450 rows=9,686 loops=1)

  • Hash Cond: ((v.ledger_name)::text = (b_1.ledger_name)::text)
19. 2,388.898 2,388.898 ↑ 1.0 4,986,671 1

Seq Scan on vouchers v (cost=0.00..573,988.11 rows=5,048,879 width=19) (actual time=0.009..2,388.898 rows=4,986,671 loops=1)

  • Filter: (entry_date < (now() - '1 day'::interval))
  • Rows Removed by Filter: 58
20. 0.224 6.695 ↓ 1,732.0 1,732 1

Hash (cost=29,855.65..29,855.65 rows=1 width=22) (actual time=6.695..6.695 rows=1,732 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 107kB
21. 0.248 6.471 ↓ 1,732.0 1,732 1

Unique (cost=29,855.64..29,855.64 rows=1 width=22) (actual time=6.140..6.471 rows=1,732 loops=1)

22. 3.325 6.223 ↓ 1,732.0 1,732 1

Sort (cost=29,855.64..29,855.64 rows=1 width=22) (actual time=6.139..6.223 rows=1,732 loops=1)

  • Sort Key: b_1.ledger_name
  • Sort Method: quicksort Memory: 171kB
23. 0.193 2.898 ↓ 1,732.0 1,732 1

Nested Loop (cost=29,658.43..29,855.63 rows=1 width=22) (actual time=2.377..2.898 rows=1,732 loops=1)

24. 0.003 2.340 ↑ 1.0 1 1

Sort (cost=29,658.01..29,658.01 rows=1 width=104) (actual time=2.340..2.340 rows=1 loops=1)

  • Sort Key: a_1.path_info
  • Sort Method: quicksort Memory: 25kB
25.          

CTE rel_tree

26. 0.013 2.315 ↓ 1.4 30 1

Recursive Union (cost=0.41..29,657.50 rows=22 width=112) (actual time=0.320..2.315 rows=30 loops=1)

27. 2.240 2.240 ↓ 1.3 16 1

Index Scan using ledger_groups_group_name_organization_id_index on ledger_groups ledger_groups_1 (cost=0.41..5,157.87 rows=12 width=112) (actual time=0.319..2.240 rows=16 loops=1)

  • Index Cond: (organization_id = 2198)
  • Filter: (parent_id IS NULL)
  • Rows Removed by Filter: 14
28. 0.028 0.062 ↓ 7.0 7 2

Nested Loop (cost=0.41..2,449.92 rows=1 width=112) (actual time=0.016..0.031 rows=7 loops=2)

29. 0.004 0.004 ↑ 8.0 15 2

WorkTable Scan on rel_tree p_1 (cost=0.00..2.40 rows=120 width=76) (actual time=0.001..0.002 rows=15 loops=2)

30. 0.030 0.030 ↓ 0.0 0 30

Index Scan using idx_parent_id_is_deleted on ledger_groups c_1 (cost=0.41..20.39 rows=1 width=27) (actual time=0.001..0.001 rows=0 loops=30)

  • Index Cond: (parent_id = p_1.id)
  • Filter: (organization_id = 2198)
31. 2.337 2.337 ↑ 1.0 1 1

CTE Scan on rel_tree a_1 (cost=0.00..0.49 rows=1 width=104) (actual time=2.289..2.337 rows=1 loops=1)

  • Filter: (top_level_group_name[2] = 'Sundry Debtors'::text)
  • Rows Removed by Filter: 29
32. 0.365 0.365 ↓ 1,732.0 1,732 1

Index Scan using idx_is_deleted_organization_id_due_date_group_name on ledgers b_1 (cost=0.42..197.59 rows=1 width=38) (actual time=0.035..0.365 rows=1,732 loops=1)

  • Index Cond: ((organization_id = 2198) AND ((group_name)::text = a_1.group_name))
33. 58.116 58.116 ↑ 1.0 1 9,686

Index Scan using ledgers_ledger_name_organization_id_index on ledgers l (cost=0.42..0.47 rows=1 width=30) (actual time=0.006..0.006 rows=1 loops=9,686)

  • Index Cond: (((ledger_name)::text = (v.ledger_name)::text) AND (organization_id = 2198))
Planning time : 1.665 ms
Execution time : 2,975.645 ms