explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TYzD : with recursive + join ANALYZE

Settings
# exclusive inclusive rows x rows loops node
1. 17.761 17.761 ↑ 1.3 79 1

CTE Scan on parents (cost=3,740.57..3,743.60 rows=101 width=46) (actual time=1.716..17.761 rows=79 loops=1)

2.          

CTE parents

3. 0.210 17.644 ↑ 1.3 79 1

Recursive Union (cost=0.55..3,740.57 rows=101 width=48) (actual time=1.710..17.644 rows=79 loops=1)

4. 0.120 7.966 ↓ 94.0 94 1

Nested Loop (cost=0.55..359.23 rows=1 width=44) (actual time=1.707..7.966 rows=94 loops=1)

5. 3.436 3.436 ↓ 35.0 35 1

Seq Scan on general_categories c (cost=0.00..341.52 rows=1 width=44) (actual time=0.163..3.436 rows=35 loops=1)

  • Filter: (lower(title) ~~ '%сад%'::text)
  • Rows Removed by Filter: 3200
6. 4.410 4.410 ↓ 3.0 3 35

Index Only Scan using uses_items_site_id_value_type_key on uses_items u (cost=0.55..17.70 rows=1 width=4) (actual time=0.048..0.126 rows=3 loops=35)

  • Index Cond: ((site_id = ANY ('{1571,127,1123}'::integer[])) AND (value = c.id) AND (type = 'general_category'::text))
  • Heap Fetches: 24
7. 3.509 9.468 ↓ 1.2 12 6

Hash Join (cost=0.33..337.93 rows=10 width=48) (actual time=1.006..1.578 rows=12 loops=6)

  • Hash Cond: (op.id = p.parent_id)
8. 5.905 5.905 ↑ 1.0 3,235 5

Seq Scan on general_categories op (cost=0.00..325.35 rows=3,235 width=44) (actual time=0.001..1.181 rows=3,235 loops=5)

9. 0.024 0.054 ↓ 1.2 12 6

Hash (cost=0.20..0.20 rows=10 width=8) (actual time=0.009..0.009 rows=12 loops=6)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
10. 0.030 0.030 ↓ 1.3 13 6

WorkTable Scan on parents p (cost=0.00..0.20 rows=10 width=8) (actual time=0.001..0.005 rows=13 loops=6)

Planning time : 0.292 ms
Execution time : 17.845 ms