explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QOtL

Settings
# exclusive inclusive rows x rows loops node
1. 12.968 1,340.874 ↓ 994.5 153,154 1

Nested Loop (cost=1,685.26..2,911.29 rows=154 width=76) (actual time=825.668..1,340.874 rows=153,154 loops=1)

2. 115.012 868.444 ↓ 994.5 153,154 1

HashAggregate (cost=1,684.84..1,686.38 rows=154 width=8) (actual time=825.659..868.444 rows=153,154 loops=1)

  • Group Key: _descendants.id, _descendants.account_id
3.          

CTE _descendants

4. 72.546 609.880 ↓ 212.4 153,155 1

Recursive Union (cost=0.42..1,665.61 rows=721 width=44) (actual time=0.022..609.880 rows=153,155 loops=1)

5. 0.022 0.022 ↑ 1.0 1 1

Index Scan using linear_hierarchy_relations_pkey on linear_hierarchy_relations (cost=0.42..8.44 rows=1 width=44) (actual time=0.020..0.022 rows=1 loops=1)

  • Index Cond: (id = 3,796)
6. 56.184 537.312 ↓ 24.4 1,760 87

Nested Loop (cost=0.42..164.28 rows=72 width=44) (actual time=0.007..6.176 rows=1,760 loops=87)

7. 21.663 21.663 ↓ 176.0 1,760 87

WorkTable Scan on _descendants _descendants_1 (cost=0.00..0.20 rows=10 width=40) (actual time=0.000..0.249 rows=1,760 loops=87)

8. 459.465 459.465 ↑ 7.0 1 153,155

Index Scan using linear_hierarchy_relations_parent_id_index on linear_hierarchy_relations linear_hierarchy_relations_1 (cost=0.42..16.30 rows=7 width=12) (actual time=0.002..0.003 rows=1 loops=153,155)

  • Index Cond: (parent_id = _descendants_1.id)
9. 753.432 753.432 ↓ 638.1 153,154 1

CTE Scan on _descendants (cost=0.00..18.03 rows=240 width=8) (actual time=0.043..753.432 rows=153,154 loops=1)

  • Filter: (array_length(_materialized_path, 1) > 1)
  • Rows Removed by Filter: 1
10. 459.462 459.462 ↑ 1.0 1 153,154

Index Scan using accounts_pkey on accounts a (cost=0.42..7.94 rows=1 width=68) (actual time=0.003..0.003 rows=1 loops=153,154)

  • Index Cond: (id = _descendants.account_id)
Planning time : 0.343 ms
Execution time : 1,352.143 ms