explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vNe8 : with recursive + subselect ANALYZE

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

CTE Scan on parents (cost=11,138.47..11,141.50 rows=101 width=46) (actual time=10.575..71.658 rows=79 loops=1)

2.          

CTE parents

3. 0.174 71.537 ↑ 1.3 79 1

Recursive Union (cost=7,402.74..11,138.47 rows=101 width=48) (actual time=10.568..71.537 rows=79 loops=1)

4. 22.810 61.937 ↓ 33.0 33 1

Nested Loop (cost=7,402.74..7,757.14 rows=1 width=44) (actual time=10.565..61.937 rows=33 loops=1)

  • Join Filter: (c.id = uses_items.value)
  • Rows Removed by Join Filter: 109377
5. 3.637 3.637 ↓ 35.0 35 1

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

  • Filter: (lower(title) ~~ '%сад%'::text)
  • Rows Removed by Filter: 3200
6. 29.174 35.490 ↓ 7.9 3,126 35

HashAggregate (cost=7,402.74..7,406.70 rows=396 width=4) (actual time=0.265..1.014 rows=3,126 loops=35)

  • Group Key: uses_items.value
7. 6.316 6.316 ↑ 1.5 8,150 1

Index Only Scan using uses_items_site_id_value_type_key on uses_items (cost=0.55..7,371.19 rows=12,619 width=4) (actual time=0.195..6.316 rows=8,150 loops=1)

  • Index Cond: ((site_id = ANY ('{1571,127,1123}'::integer[])) AND (type = 'general_category'::text))
  • Heap Fetches: 1891
8. 3.496 9.426 ↓ 1.2 12 6

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

  • Hash Cond: (op.id = p.parent_id)
9. 5.870 5.870 ↑ 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.174 rows=3,235 loops=5)

10. 0.036 0.060 ↓ 1.2 12 6

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
11. 0.024 0.024 ↓ 1.3 13 6

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

Planning time : 0.299 ms
Execution time : 71.769 ms