explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OV7e

Settings
# exclusive inclusive rows x rows loops node
1. 1.567 21,249.051 ↑ 1.0 100 1

Limit (cost=3,634,109.60..3,634,109.85 rows=100 width=640) (actual time=21,246.778..21,249.051 rows=100 loops=1)

2.          

CTE fuzzyquery

3. 0.015 0.015 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.008..0.015 rows=1 loops=1)

4.          

CTE searchresults

5. 43.151 71.596 ↑ 1.0 2,631 1

Nested Loop (cost=0.00..415.19 rows=2,631 width=779) (actual time=0.340..71.596 rows=2,631 loops=1)

6. 0.043 0.043 ↑ 1.0 1 1

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

7. 28.402 28.402 ↑ 1.0 2,631 1

Seq Scan on content_catalog (cost=0.00..388.86 rows=2,631 width=779) (actual time=0.062..28.402 rows=2,631 loops=1)

  • Filter: ((category_id)::text = 'vm-template'::text)
  • Rows Removed by Filter: 718
8.          

CTE poc

9. 0.014 0.021 ↓ 0.0 0 1

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

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

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

11. 0.000 0.000 ↓ 0.0 0

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

12. 0.000 0.000 ↓ 0.0 0

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

13. 20.974 21,247.484 ↑ 26.3 100 1

Sort (cost=3,633,643.53..3,633,650.11 rows=2,631 width=640) (actual time=21,246.763..21,247.484 rows=100 loops=1)

  • Sort Key: searchresults.rank DESC
  • Sort Method: top-N heapsort Memory: 126kB
14. 27.145 21,226.510 ↑ 1.7 1,530 1

Hash Left Join (cost=1,407.04..3,633,542.98 rows=2,631 width=640) (actual time=10.119..21,226.510 rows=1,530 loops=1)

  • Hash Cond: (((searchresults.category_id)::text = poc.category_id) AND ((searchresults.external_id)::text = poc.external_id))
15. 70.049 21,199.314 ↑ 1.7 1,530 1

Nested Loop (cost=1,380.79..3,633,009.77 rows=2,631 width=528) (actual time=10.031..21,199.314 rows=1,530 loops=1)

16. 125.992 125.992 ↑ 1.0 2,631 1

CTE Scan on searchresults (cost=0.00..52.62 rows=2,631 width=496) (actual time=0.354..125.992 rows=2,631 loops=1)

17. 113.133 21,003.273 ↑ 1.0 1 2,631

Aggregate (cost=1,380.79..1,380.81 rows=1 width=4) (actual time=7.979..7.983 rows=1 loops=2,631)

  • Filter: ('list'::sharing_perm = ANY (array_agg(shared_with.permission)))
  • Rows Removed by Filter: 0
18. 585.355 20,890.140 ↓ 2.0 2 2,631

Nested Loop Semi Join (cost=0.28..1,380.78 rows=1 width=4) (actual time=6.138..7.940 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
19. 2,373.299 20,019.279 ↓ 4.0 8 2,631

Nested Loop (cost=0.28..1,123.15 rows=2 width=197) (actual time=4.153..7.609 rows=8 loops=2,631)

20. 17,206.740 17,206.740 ↓ 4.0 8 2,631

Seq Scan on shared_with (cost=0.00..1,106.53 rows=2 width=21) (actual time=3.485..6.540 rows=8 loops=2,631)

  • Filter: ((category_id = (searchresults.category_id)::text) AND (external_id = (searchresults.external_id)::text))
  • Rows Removed by Filter: 27494
21. 439.240 439.240 ↑ 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.012..0.020 rows=1 loops=21,962)

  • Index Cond: (id = shared_with.node_id)
22. 283.891 285.506 ↑ 1.0 1 21,962

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

23. 1.615 1.615 ↑ 1.0 1 1

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

  • Filter: (is_primary AND (user_id = '44e87762-bcab-425c-b400-7cf4d157f635'::text))
  • Rows Removed by Filter: 6044
24. 0.015 0.051 ↓ 0.0 0 1

Hash (cost=15.00..15.00 rows=750 width=176) (actual time=0.051..0.051 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
25. 0.036 0.036 ↓ 0.0 0 1

CTE Scan on poc (cost=0.00..15.00 rows=750 width=176) (actual time=0.036..0.036 rows=0 loops=1)