explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mM48

Settings
# exclusive inclusive rows x rows loops node
1. 144.131 1,730.274 ↑ 1.3 1,732 1

Unique (cost=969,306.95..969,507.56 rows=2,326 width=22) (actual time=1,074.004..1,730.274 rows=1,732 loops=1)

2. 910.701 1,586.143 ↓ 48.8 1,957,160 1

Sort (cost=969,306.95..969,407.25 rows=40,123 width=22) (actual time=1,074.003..1,586.143 rows=1,957,160 loops=1)

  • Sort Key: b.ledger_name
  • Sort Method: external merge Disk: 59968kB
3. 229.555 675.442 ↓ 48.8 1,957,160 1

Merge Join (cost=965,610.47..966,239.11 rows=40,123 width=22) (actual time=361.137..675.442 rows=1,957,160 loops=1)

  • Merge Cond: ((b.group_name)::text = a.group_name)
4. 0.744 1.621 ↑ 1.1 2,202 1

Sort (cost=6,588.69..6,594.53 rows=2,334 width=38) (actual time=1.407..1.621 rows=2,202 loops=1)

  • Sort Key: b.group_name
  • Sort Method: quicksort Memory: 276kB
5. 0.877 0.877 ↑ 1.1 2,202 1

Index Scan using idx_org_id_ledger_name_is_deleted on ledgers b (cost=0.42..6,458.12 rows=2,334 width=38) (actual time=0.015..0.877 rows=2,202 loops=1)

  • Index Cond: (organization_id = '2198'::bigint)
6. 107.992 444.266 ↓ 45.6 1,966,272 1

Sort (cost=959,021.77..959,129.65 rows=43,149 width=32) (actual time=357.889..444.266 rows=1,966,272 loops=1)

  • Sort Key: a.group_name
  • Sort Method: quicksort Memory: 988kB
7. 0.954 336.274 ↑ 4.1 10,646 1

Subquery Scan on a (cost=955,160.58..955,699.94 rows=43,149 width=32) (actual time=334.713..336.274 rows=10,646 loops=1)

8. 18.284 335.320 ↑ 4.1 10,646 1

Sort (cost=955,160.58..955,268.45 rows=43,149 width=104) (actual time=334.712..335.320 rows=10,646 loops=1)

  • Sort Key: a_1.path_info
  • Sort Method: quicksort Memory: 1879kB
9.          

CTE rel_tree

10. 16.524 284.635 ↑ 168.1 51,324 1

Recursive Union (cost=0.00..757,669.59 rows=8,629,740 width=112) (actual time=0.009..284.635 rows=51,324 loops=1)

11. 11.022 11.022 ↓ 1.0 18,524 1

Seq Scan on ledger_groups (cost=0.00..5,764.21 rows=18,470 width=112) (actual time=0.008..11.022 rows=18,524 loops=1)

  • Filter: (parent_id IS NULL)
  • Rows Removed by Filter: 32800
12. 50.001 257.089 ↑ 183.8 4,686 7

Merge Join (cost=37,837.35..57,931.06 rows=861,127 width=112) (actual time=25.960..36.727 rows=4,686 loops=7)

  • Merge Cond: (c.parent_id = p.id)
13. 100.562 170.744 ↑ 1.6 32,085 7

Sort (cost=9,779.38..9,907.68 rows=51,321 width=27) (actual time=20.834..24.392 rows=32,085 loops=7)

  • Sort Key: c.parent_id
  • Sort Method: quicksort Memory: 5508kB
14. 70.182 70.182 ↓ 1.0 51,324 7

Seq Scan on ledger_groups c (cost=0.00..5,764.21 rows=51,321 width=27) (actual time=0.004..10.026 rows=51,324 loops=7)

15. 8.722 36.344 ↑ 17.0 10,876 7

Materialize (cost=28,057.97..28,981.47 rows=184,700 width=76) (actual time=3.225..5.192 rows=10,876 loops=7)

16. 20.062 27.622 ↑ 25.2 7,331 7

Sort (cost=28,057.97..28,519.72 rows=184,700 width=76) (actual time=3.220..3.946 rows=7,331 loops=7)

  • Sort Key: p.id
  • Sort Method: quicksort Memory: 43kB
17. 7.560 7.560 ↑ 25.2 7,332 7

WorkTable Scan on rel_tree p (cost=0.00..3,694.00 rows=184,700 width=76) (actual time=0.001..1.080 rows=7,332 loops=7)

18. 317.036 317.036 ↑ 4.1 10,646 1

CTE Scan on rel_tree a_1 (cost=0.00..194,169.15 rows=43,149 width=104) (actual time=48.487..317.036 rows=10,646 loops=1)

  • Filter: (top_level_group_name[2] = 'Sundry Debtors'::text)
  • Rows Removed by Filter: 40678
Planning time : 0.293 ms
Execution time : 1,742.890 ms