explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XMlw

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 48.440 ↓ 5.9 100 1

Limit (cost=855.93..855.97 rows=17 width=915) (actual time=48.430..48.440 rows=100 loops=1)

2.          

CTE fuzzyquery

3. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=1) (actual time=0.000..0.001 rows=1 loops=1)

4.          

CTE poc

5. 0.001 0.002 ↓ 0.0 0 1

Hash Join (cost=23.05..42.53 rows=750 width=192) (actual time=0.002..0.002 rows=0 loops=1)

  • Hash Cond: (poc_content.poc_id = point_of_contact.id)
6. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on poc_content (cost=0.00..17.50 rows=750 width=80) (actual time=0.001..0.001 rows=0 loops=1)

7. 0.000 0.000 ↓ 0.0 0

Hash (cost=15.80..15.80 rows=580 width=112) (never executed)

8. 0.000 0.000 ↓ 0.0 0

Seq Scan on point_of_contact (cost=0.00..15.80 rows=580 width=112) (never executed)

9.          

CTE mto_nodes

10. 0.574 0.574 ↑ 1.0 1 1

Seq Scan on mto_node (cost=0.00..257.56 rows=1 width=230) (actual time=0.019..0.574 rows=1 loops=1)

  • Filter: (is_primary AND (user_id = '44e87762-bcab-425c-b400-7cf4d157f635'::text))
  • Rows Removed by Filter: 6044
11. 0.978 48.433 ↓ 5.9 100 1

Sort (cost=555.83..555.87 rows=17 width=915) (actual time=48.429..48.433 rows=100 loops=1)

  • Sort Key: content_catalog.created DESC
  • Sort Method: top-N heapsort Memory: 230kB
12. 0.229 47.455 ↓ 90.0 1,530 1

Nested Loop (cost=32.35..555.48 rows=17 width=915) (actual time=0.821..47.455 rows=1,530 loops=1)

13. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on fuzzyquery (cost=0.00..0.02 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)

14. 0.365 47.224 ↓ 90.0 1,530 1

Nested Loop Left Join (cost=32.35..555.29 rows=17 width=907) (actual time=0.818..47.224 rows=1,530 loops=1)

  • Join Filter: ((content_catalog.category_id = poc.category_id) AND (content_catalog.external_id = poc.external_id))
15. 1.476 46.859 ↓ 90.0 1,530 1

Nested Loop (cost=32.35..536.75 rows=17 width=795) (actual time=0.815..46.859 rows=1,530 loops=1)

16. 0.481 0.656 ↓ 154.8 2,631 1

Bitmap Heap Scan on content_catalog (cost=4.41..61.32 rows=17 width=763) (actual time=0.197..0.656 rows=2,631 loops=1)

  • Recheck Cond: (category_id = 'vm-template'::text)
  • Heap Blocks: exact=269
17. 0.175 0.175 ↓ 154.8 2,631 1

Bitmap Index Scan on content_catalog_category_id (cost=0.00..4.41 rows=17 width=0) (actual time=0.175..0.175 rows=2,631 loops=1)

  • Index Cond: (category_id = 'vm-template'::text)
18. 0.000 44.727 ↑ 1.0 1 2,631

Aggregate (cost=27.94..27.95 rows=1 width=32) (actual time=0.017..0.017 rows=1 loops=2,631)

  • Filter: ('list'::sharing_perm = ANY (array_agg(shared_with.permission)))
  • Rows Removed by Filter: 0
19. 7.893 44.727 ↓ 2.0 2 2,631

Nested Loop Semi Join (cost=0.69..27.93 rows=1 width=4) (actual time=0.014..0.017 rows=2 loops=2,631)

  • Join Filter: CASE WHEN shared_with.recursive_down THEN (mto_nodes.path <@ shared_to.path) WHEN ((shared_to.user_id IS NULL) AND (shared_to.subtree_id IS NULL)) THEN ((mto_nodes.path <@ shared_to.path) AND ((nlevel(mto_nodes.path) - nlevel(shared_to.path)) <= 1)) ELSE (mto_nodes.id = shared_with.node_id) END
  • Rows Removed by Join Filter: 6
20. 0.000 36.834 ↓ 4.0 8 2,631

Nested Loop (cost=0.69..27.84 rows=2 width=197) (actual time=0.006..0.014 rows=8 loops=2,631)

21. 15.786 15.786 ↓ 4.0 8 2,631

Index Scan using shared_with_test_index on shared_with (cost=0.41..11.24 rows=2 width=21) (actual time=0.005..0.006 rows=8 loops=2,631)

  • Index Cond: ((category_id = content_catalog.category_id) AND (external_id = content_catalog.external_id))
22. 21.962 21.962 ↑ 1.0 1 21,962

Index Scan using mto_node_pkey on mto_node shared_to (cost=0.28..8.30 rows=1 width=192) (actual time=0.001..0.001 rows=1 loops=21,962)

  • Index Cond: (id = shared_with.node_id)
23. 0.000 0.000 ↑ 1.0 1 21,962

CTE Scan on mto_nodes (cost=0.00..0.02 rows=1 width=48) (actual time=0.000..0.000 rows=1 loops=21,962)

24. 0.000 0.000 ↓ 0.0 0 1,530

CTE Scan on poc (cost=0.00..16.88 rows=4 width=176) (actual time=0.000..0.000 rows=0 loops=1,530)

  • Filter: (category_id = 'vm-template'::text)
Planning time : 2.395 ms
Execution time : 48.540 ms