explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0lgr

Settings
# exclusive inclusive rows x rows loops node
1. 59.193 378.733 ↑ 1.0 1 1

Aggregate (cost=4,188.26..4,188.27 rows=1 width=32) (actual time=378.733..378.733 rows=1 loops=1)

2. 0.569 319.540 ↓ 7.9 252 1

Subquery Scan on q (cost=31.89..4,188.10 rows=32 width=123) (actual time=2.308..319.540 rows=252 loops=1)

3. 1.178 318.971 ↓ 7.9 252 1

Nested Loop (cost=31.89..4,187.78 rows=32 width=131) (actual time=2.301..318.971 rows=252 loops=1)

4.          

CTE au

5. 0.006 0.006 ↑ 1.0 7 1

Values Scan on "*VALUES*" (cost=0.00..0.09 rows=7 width=4) (actual time=0.004..0.006 rows=7 loops=1)

6. 0.292 0.515 ↓ 7.9 254 1

HashAggregate (cost=31.53..31.85 rows=32 width=8) (actual time=0.318..0.515 rows=254 loops=1)

  • Group Key: c.object_type_id
7. 0.031 0.223 ↓ 13.2 424 1

Nested Loop (cost=0.28..31.45 rows=32 width=8) (actual time=0.077..0.223 rows=424 loops=1)

8. 0.010 0.010 ↑ 1.0 7 1

CTE Scan on au (cost=0.00..0.14 rows=7 width=4) (actual time=0.006..0.010 rows=7 loops=1)

9. 0.182 0.182 ↓ 12.2 61 7

Index Only Scan using c_role_objtype_roleid_object_type_id_action_key on c_role_objtype c (cost=0.28..4.42 rows=5 width=10) (actual time=0.016..0.026 rows=61 loops=7)

  • Index Cond: ((roleid = au.authid) AND (action = 'show'::text))
  • Heap Fetches: 0
10. 1.270 1.270 ↑ 1.0 1 254

Index Scan using object_types_pkey on object_types ot (cost=0.28..0.47 rows=1 width=99) (actual time=0.005..0.005 rows=1 loops=254)

  • Index Cond: (id = c.object_type_id)
11.          

SubPlan (for Nested Loop)

12. 62.496 316.008 ↑ 1.0 1 252

Aggregate (cost=129.39..129.40 rows=1 width=32) (actual time=1.254..1.254 rows=1 loops=252)

13. 3.024 253.512 ↓ 32.0 32 252

Subquery Scan on tep (cost=129.37..129.39 rows=1 width=199) (actual time=0.993..1.006 rows=32 loops=252)

14. 14.112 250.488 ↓ 32.0 32 252

Sort (cost=129.37..129.38 rows=1 width=187) (actual time=0.992..0.994 rows=32 loops=252)

  • Sort Key: ct.order_n, t.category_id, t.order_n
  • Sort Method: quicksort Memory: 43kB
15. 2.376 236.376 ↓ 32.0 32 252

Nested Loop (cost=120.47..129.36 rows=1 width=187) (actual time=0.784..0.938 rows=32 loops=252)

16. 4.691 226.044 ↓ 32.0 32 252

Nested Loop (cost=120.19..129.05 rows=1 width=90) (actual time=0.781..0.897 rows=32 loops=252)

17. 0.120 212.436 ↓ 17.5 35 252

Nested Loop (cost=119.91..128.37 rows=2 width=106) (actual time=0.766..0.843 rows=35 loops=252)

18. 2.016 193.536 ↓ 18.5 37 252

Unique (cost=119.62..119.64 rows=2 width=16) (actual time=0.759..0.768 rows=37 loops=252)

19. 3.024 191.520 ↓ 25.5 51 252

Sort (cost=119.62..119.63 rows=2 width=16) (actual time=0.758..0.760 rows=51 loops=252)

  • Sort Key: crt.tep_id, crt.object_type_id
  • Sort Method: quicksort Memory: 30kB
20. 1.260 188.496 ↓ 25.5 51 252

Nested Loop (cost=0.42..119.61 rows=2 width=16) (actual time=0.209..0.748 rows=51 loops=252)

21. 0.252 0.252 ↑ 1.0 7 252

CTE Scan on au au_1 (cost=0.00..0.14 rows=7 width=4) (actual time=0.000..0.001 rows=7 loops=252)

22. 186.984 186.984 ↓ 7.0 7 1,764

Index Only Scan using c_role_teps_roleid_tep_id_action_object_type_id_key on c_role_teps crt (cost=0.42..17.06 rows=1 width=18) (actual time=0.048..0.106 rows=7 loops=1,764)

  • Index Cond: ((roleid = au_1.authid) AND (action = 'show'::text) AND (object_type_id = ot.id))
  • Heap Fetches: 19
23. 18.780 18.780 ↑ 1.0 1 9,390

Index Scan using obj_teps_pkey on obj_teps t (cost=0.29..4.37 rows=1 width=90) (actual time=0.002..0.002 rows=1 loops=9,390)

  • Index Cond: (id = crt.tep_id)
24. 8.917 8.917 ↑ 1.0 1 8,917

Index Only Scan using c_objtype_tep_object_type_id_tep_id_key on c_objtype_tep c_1 (cost=0.29..0.34 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=8,917)

  • Index Cond: ((object_type_id = ot.id) AND (tep_id = t.id))
  • Heap Fetches: 0
25. 7.956 7.956 ↑ 1.0 1 7,956

Index Scan using obj_tep_category_pkey on obj_tep_category ct (cost=0.28..0.31 rows=1 width=97) (actual time=0.001..0.001 rows=1 loops=7,956)

  • Index Cond: (id = t.category_id)
Planning time : 3.382 ms
Execution time : 379.689 ms