explain.depesz.com

PostgreSQL's explain analyze made readable

Result: w1J7 : Optimization for: Optimization for: Optimization for: plan #jJl1; plan #ev9W; plan #zcrb

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.012 2.459 ↑ 245.0 2 1

Sort (cost=2,567.12..2,568.35 rows=490 width=157) (actual time=2.459..2.459 rows=2 loops=1)

  • Sort Key: menus.level, menus.rank
  • Sort Method: quicksort Memory: 25kB
2.          

CTE tree

3. 0.071 0.422 ↑ 9.6 186 1

Recursive Union (cost=0.00..166.44 rows=1,788 width=36) (actual time=0.019..0.422 rows=186 loops=1)

4. 0.039 0.039 ↑ 1.0 8 1

Seq Scan on menus menus_1 (cost=0.00..6.86 rows=8 width=36) (actual time=0.013..0.039 rows=8 loops=1)

  • Filter: (parent_menu_id IS NULL)
  • Rows Removed by Filter: 178
5. 0.192 0.312 ↑ 4.0 45 4

Hash Join (cost=2.60..12.38 rows=178 width=36) (actual time=0.036..0.078 rows=45 loops=4)

  • Hash Cond: (menus_2.parent_menu_id = tree_1.id)
6. 0.060 0.060 ↑ 1.0 186 4

Seq Scan on menus menus_2 (cost=0.00..6.86 rows=186 width=12) (actual time=0.002..0.015 rows=186 loops=4)

7. 0.036 0.060 ↑ 1.7 47 4

Hash (cost=1.60..1.60 rows=80 width=36) (actual time=0.015..0.015 rows=47 loops=4)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
8. 0.024 0.024 ↑ 1.7 47 4

WorkTable Scan on tree tree_1 (cost=0.00..1.60 rows=80 width=36) (actual time=0.001..0.006 rows=47 loops=4)

9. 0.013 2.447 ↑ 245.0 2 1

Hash Join (cost=95.28..2,378.78 rows=490 width=157) (actual time=1.977..2.447 rows=2 loops=1)

  • Hash Cond: (tree.id = menus.id)
10. 0.624 0.810 ↑ 447.0 2 1

CTE Scan on tree (cost=0.00..2,275.23 rows=894 width=4) (actual time=0.342..0.810 rows=2 loops=1)

  • Filter: (SubPlan 2)
  • Rows Removed by Filter: 184
11.          

SubPlan (for CTE Scan)

12. 0.186 0.186 ↓ 0.0 0 186

Function Scan on unnest id (cost=0.00..1.25 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=186)

  • Filter: (id = 34)
  • Rows Removed by Filter: 2
13. 0.066 1.624 ↓ 1.0 115 1

Hash (cost=93.90..93.90 rows=110 width=161) (actual time=1.624..1.624 rows=115 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
14. 0.051 1.558 ↓ 1.0 115 1

Hash Join (cost=5.65..93.90 rows=110 width=161) (actual time=0.091..1.558 rows=115 loops=1)

  • Hash Cond: (menus.id = ltm.menu_id)
15. 0.930 1.446 ↓ 1.0 115 1

Nested Loop Semi Join (cost=0.00..87.95 rows=110 width=157) (actual time=0.021..1.446 rows=115 loops=1)

  • Join Filter: (((m.id = menus.parent_menu_id) AND m.enabled) OR (menus.parent_menu_id IS NULL))
  • Rows Removed by Join Filter: 9605
16. 0.056 0.056 ↑ 1.0 115 1

Seq Scan on menus (cost=0.00..7.33 rows=115 width=157) (actual time=0.007..0.056 rows=115 loops=1)

  • Filter: (enabled AND (level = ANY ('{3,4}'::integer[])))
  • Rows Removed by Filter: 71
17. 0.411 0.460 ↑ 2.2 85 115

Materialize (cost=0.00..7.79 rows=186 width=5) (actual time=0.000..0.004 rows=85 loops=115)

18. 0.049 0.049 ↑ 1.0 184 1

Seq Scan on menus m (cost=0.00..6.86 rows=186 width=5) (actual time=0.006..0.049 rows=184 loops=1)

19. 0.028 0.061 ↑ 1.0 186 1

Hash (cost=3.33..3.33 rows=186 width=4) (actual time=0.061..0.061 rows=186 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
20. 0.033 0.033 ↑ 1.0 186 1

Seq Scan on tenants_menus ltm (cost=0.00..3.33 rows=186 width=4) (actual time=0.007..0.033 rows=186 loops=1)

  • Filter: (tenant_id = 1)
Planning time : 0.912 ms
Execution time : 2.638 ms