explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7nbY

Settings
# exclusive inclusive rows x rows loops node
1. 8,464.019 8,464.019 ↑ 2,856,577.2 4 1

CTE Scan on base_and_ancestors namespaces (cost=8,535,668.59..8,764,194.77 rows=11,426,309 width=12) (actual time=0.120..8,464.019 rows=4 loops=1)

2.          

CTE base_and_ancestors

3. 4.476 8,464.002 ↑ 2,856,577.2 4 1

Recursive Union (cost=647.25..8,535,668.59 rows=11,426,309 width=322) (actual time=0.117..8,464.002 rows=4 loops=1)

4. 0.003 0.106 ↑ 568,379.0 1 1

Nested Loop (cost=647.25..1,341.31 rows=568,379 width=322) (actual time=0.101..0.106 rows=1 loops=1)

5. 0.007 0.069 ↑ 200.0 1 1

HashAggregate (cost=646.81..648.81 rows=200 width=4) (actual time=0.068..0.069 rows=1 loops=1)

  • Group Key: epics_2.group_id
6. 0.062 0.062 ↑ 271.0 1 1

CTE Scan on base_and_descendants epics_2 (cost=638.01..643.43 rows=271 width=4) (actual time=0.040..0.062 rows=1 loops=1)

7.          

CTE base_and_descendants

8. 0.013 0.059 ↑ 271.0 1 1

Recursive Union (cost=0.29..638.01 rows=271 width=778) (actual time=0.038..0.059 rows=1 loops=1)

9. 0.026 0.026 ↑ 1.0 1 1

Index Scan using epics_pkey on epics (cost=0.29..3.30 rows=1 width=778) (actual time=0.026..0.026 rows=1 loops=1)

  • Index Cond: (id = 15413)
10. 0.003 0.020 ↓ 0.0 0 1

Nested Loop (cost=0.29..62.93 rows=27 width=778) (actual time=0.020..0.020 rows=0 loops=1)

11. 0.005 0.005 ↑ 10.0 1 1

WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.005..0.005 rows=1 loops=1)

12. 0.012 0.012 ↓ 0.0 0 1

Index Scan using index_epics_on_parent_id on epics epics_1 (cost=0.29..6.24 rows=3 width=778) (actual time=0.012..0.012 rows=0 loops=1)

  • Index Cond: (parent_id = base_and_descendants.id)
13. 0.034 0.034 ↑ 1.0 1 1

Index Scan using namespaces_pkey on namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=322) (actual time=0.030..0.034 rows=1 loops=1)

  • Index Cond: (id = epics_2.group_id)
  • Filter: ((type)::text = 'Group'::text)
14. 291.816 8,459.420 ↑ 1,085,793.0 1 4

Hash Join (cost=311,724.17..830,580.11 rows=1,085,793 width=322) (actual time=2,099.586..2,114.855 rows=1 loops=4)

  • Hash Cond: (base_and_ancestors.parent_id = namespaces_2.id)
15. 0.008 0.008 ↑ 5,683,790.0 1 4

WorkTable Scan on base_and_ancestors (cost=0.00..113,675.80 rows=5,683,790 width=4) (actual time=0.001..0.002 rows=1 loops=4)

16. 8,167.596 8,167.596 ↓ 1.0 1,141,428 4

Hash (cost=102,130.69..102,130.69 rows=1,136,758 width=322) (actual time=2,041.899..2,041.899 rows=1,141,428 loops=4)

  • Buckets: 524288 Batches: 8 Memory Usage: 29315kB
  • -> Index Scan using index_namespaces_on_type_partial on namespaces namespaces_2 (cost=0.43..102130.69 rows=1136758 width=322) (actual time=0.038..1374.138 rows=114142
  • Index Cond: ((type)::text = 'Group'::text)
Planning time : 2.302 ms
Execution time : 8,557.414 ms