explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lLlT

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 74.329 ↑ 1.0 100 1

Limit (cost=752,005.49..752,005.74 rows=100 width=931) (actual time=74.320..74.329 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.001..0.001 rows=1 loops=1)

4.          

CTE poc

5. 0.002 0.007 ↓ 0.0 0 1

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

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

Seq Scan on poc_content (cost=0.00..17.50 rows=750 width=80) (actual time=0.005..0.005 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. 1.523 74.321 ↑ 26.3 100 1

Sort (cost=751,962.95..751,969.53 rows=2,631 width=931) (actual time=74.318..74.321 rows=100 loops=1)

  • Sort Key: content_catalog.created DESC
  • Sort Method: top-N heapsort Memory: 230kB
10. 0.270 72.798 ↑ 1.7 1,530 1

Nested Loop (cost=285.48..751,862.39 rows=2,631 width=931) (actual time=4.350..72.798 rows=1,530 loops=1)

11. 0.007 0.007 ↑ 1.0 1 1

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

12. 1.222 72.521 ↑ 1.7 1,530 1

Nested Loop (cost=285.48..751,836.06 rows=2,631 width=923) (actual time=4.340..72.521 rows=1,530 loops=1)

13. 1.289 2.893 ↑ 1.0 2,631 1

Nested Loop Left Join (cost=0.00..667.80 rows=2,631 width=891) (actual time=0.279..2.893 rows=2,631 loops=1)

  • Join Filter: (((content_catalog.category_id)::text = poc.category_id) AND ((content_catalog.external_id)::text = poc.external_id))
14. 1.604 1.604 ↑ 1.0 2,631 1

Seq Scan on content_catalog (cost=0.00..387.86 rows=2,631 width=779) (actual time=0.265..1.604 rows=2,631 loops=1)

  • Filter: ((category_id)::text = 'vm-template'::text)
  • Rows Removed by Filter: 718
15. 0.000 0.000 ↓ 0.0 0 2,631

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

  • Filter: (category_id = 'vm-template'::text)
16. 2.631 68.406 ↑ 1.0 1 2,631

Aggregate (cost=285.48..285.49 rows=1 width=32) (actual time=0.026..0.026 rows=1 loops=2,631)

  • Filter: ('list'::sharing_perm = ANY (array_agg(shared_with.permission)))
  • Rows Removed by Filter: 0
17. 10.524 65.775 ↓ 2.0 2 2,631

Nested Loop Semi Join (cost=0.69..285.47 rows=1 width=4) (actual time=0.022..0.025 rows=2 loops=2,631)

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

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

19. 23.679 23.679 ↓ 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.007..0.009 rows=8 loops=2,631)

  • Index Cond: ((category_id = (content_catalog.category_id)::text) AND (external_id = (content_catalog.external_id)::text))
20. 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)
21. 0.000 0.000 ↑ 1.0 1 21,962

Materialize (cost=0.00..257.57 rows=1 width=140) (actual time=0.000..0.000 rows=1 loops=21,962)

22. 3.697 3.697 ↑ 1.0 1 1

Seq Scan on mto_node (cost=0.00..257.56 rows=1 width=140) (actual time=0.087..3.697 rows=1 loops=1)

  • Filter: (is_primary AND (user_id = '44e87762-bcab-425c-b400-7cf4d157f635'::text))
  • Rows Removed by Filter: 6044