explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YmDA

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

Limit (cost=4,987.80..4,987.85 rows=17 width=915) (actual time=45.723..45.733 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.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. 0.936 45.726 ↓ 5.9 100 1

Sort (cost=4,945.26..4,945.31 rows=17 width=915) (actual time=45.721..45.726 rows=100 loops=1)

  • Sort Key: content_catalog.created DESC
  • Sort Method: top-N heapsort Memory: 230kB
10. 0.204 44.790 ↓ 90.0 1,530 1

Nested Loop (cost=285.76..4,944.92 rows=17 width=915) (actual time=1.279..44.790 rows=1,530 loops=1)

11. 0.005 0.005 ↑ 1.0 1 1

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

12. 0.344 44.581 ↓ 90.0 1,530 1

Nested Loop Left Join (cost=285.76..4,944.73 rows=17 width=907) (actual time=1.272..44.581 rows=1,530 loops=1)

  • Join Filter: ((content_catalog.category_id = poc.category_id) AND (content_catalog.external_id = poc.external_id))
13. 1.524 44.237 ↓ 90.0 1,530 1

Nested Loop (cost=285.76..4,926.19 rows=17 width=795) (actual time=1.269..44.237 rows=1,530 loops=1)

14. 0.617 0.617 ↓ 154.8 2,631 1

Index Scan using content_catalog_pkey on content_catalog (cost=0.28..72.58 rows=17 width=763) (actual time=0.024..0.617 rows=2,631 loops=1)

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

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

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

Nested Loop Semi Join (cost=0.69..285.47 rows=1 width=4) (actual time=0.013..0.016 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
17. 0.000 34.203 ↓ 4.0 8 2,631

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

18. 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))
19. 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)
20. 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)

21. 1.176 1.176 ↑ 1.0 1 1

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

  • Filter: (is_primary AND (user_id = '44e87762-bcab-425c-b400-7cf4d157f635'::text))
  • Rows Removed by Filter: 6044
22. 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 : 1.703 ms
Execution time : 45.976 ms