explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XDB9

Settings
# exclusive inclusive rows x rows loops node
1. 0.488 50.536 ↓ 19.0 19 1

Sort (cost=218.58..218.59 rows=1 width=4,977) (actual time=50.396..50.536 rows=19 loops=1)

  • Sort Key: vulnerabilities.resolved_at DESC
  • Sort Method: quicksort Memory: 101kB
2. 0.550 50.048 ↓ 19.0 19 1

Nested Loop Left Join (cost=148.32..218.57 rows=1 width=4,977) (actual time=25.808..50.048 rows=19 loops=1)

3. 0.636 49.213 ↓ 19.0 19 1

Nested Loop Left Join (cost=148.17..218.10 rows=1 width=4,691) (actual time=25.777..49.213 rows=19 loops=1)

4. 0.519 48.216 ↓ 19.0 19 1

Nested Loop Left Join (cost=148.03..214.32 rows=1 width=4,209) (actual time=25.746..48.216 rows=19 loops=1)

5. 0.616 47.374 ↓ 19.0 19 1

Nested Loop Left Join (cost=147.89..212.86 rows=1 width=3,136) (actual time=25.712..47.374 rows=19 loops=1)

6. 0.503 46.473 ↓ 19.0 19 1

Nested Loop Left Join (cost=147.75..211.09 rows=1 width=2,468) (actual time=25.680..46.473 rows=19 loops=1)

7. 0.577 45.685 ↓ 19.0 19 1

Nested Loop Left Join (cost=147.60..210.62 rows=1 width=2,186) (actual time=25.648..45.685 rows=19 loops=1)

8. 0.653 44.823 ↓ 19.0 19 1

Nested Loop Left Join (cost=147.45..206.84 rows=1 width=2,182) (actual time=25.616..44.823 rows=19 loops=1)

  • Filter: (((vulnerabilities.title)::text ~~* '%wilma%'::text) OR (vulnerabilities.description ~~* '%wilma%'::text) OR (vulnerabilities.impact ~~* '%wilma%'::text) OR ((tags.name)::text ~~* '%wilma%'::text) OR ((vulnerabilities.id)::text ~~* '%wilma%'::text))
9. 0.581 43.752 ↑ 1.1 19 1

Nested Loop Left Join (cost=147.31..202.43 rows=20 width=2,139) (actual time=25.563..43.752 rows=19 loops=1)

  • Join Filter: (organization_profiles.id = vulnerabilities.org_profile_id)
10. 9.810 42.886 ↑ 1.1 19 1

Hash Right Join (cost=147.16..193.97 rows=20 width=1,609) (actual time=25.447..42.886 rows=19 loops=1)

  • Hash Cond: ((validation_steps.vulnerability_id)::text = (vulnerabilities.id)::text)
11. 9.684 9.684 ↓ 1.0 1,376 1

Seq Scan on validation_steps (cost=0.00..41.53 rows=1,353 width=146) (actual time=0.010..9.684 rows=1,376 loops=1)

12. 0.106 23.392 ↑ 1.0 10 1

Hash (cost=147.04..147.04 rows=10 width=1,463) (actual time=23.392..23.392 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
13. 0.169 23.286 ↑ 1.0 10 1

Hash Left Join (cost=122.73..147.04 rows=10 width=1,463) (actual time=22.945..23.286 rows=10 loops=1)

  • Hash Cond: (vulnerabilities.vulnerability_status_id = vulnerability_statuses.id)
14. 0.182 22.670 ↑ 1.0 10 1

Hash Left Join (cost=92.93..117.10 rows=10 width=1,399) (actual time=22.464..22.670 rows=10 loops=1)

  • Hash Cond: ((vulnerabilities.vulnerability_category)::text = (vulnerability_categories.id)::text)
15. 0.097 21.018 ↑ 1.0 10 1

Hash Right Join (cost=87.70..111.73 rows=10 width=1,247) (actual time=20.946..21.018 rows=10 loops=1)

  • Hash Cond: (disputes.id = vulnerabilities.dispute_id)
16. 0.007 0.007 ↓ 0.0 0 1

Seq Scan on disputes (cost=0.00..20.20 rows=1,020 width=52) (actual time=0.007..0.007 rows=0 loops=1)

17. 0.117 20.914 ↑ 1.0 10 1

Hash (cost=87.57..87.57 rows=10 width=1,195) (actual time=20.914..20.914 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
18. 5.127 20.797 ↑ 1.0 10 1

Hash Right Join (cost=72.51..87.57 rows=10 width=1,195) (actual time=11.084..20.797 rows=10 loops=1)

  • Hash Cond: ((vuln_taggings_vulnerabilities_join.vulnerability_id)::text = (vulnerabilities.id)::text)
19. 5.057 5.057 ↑ 1.1 647 1

Seq Scan on vuln_taggings vuln_taggings_vulnerabilities_join (cost=0.00..12.25 rows=725 width=17) (actual time=0.010..5.057 rows=647 loops=1)

20. 0.120 10.613 ↑ 1.0 10 1

Hash (cost=72.38..72.38 rows=10 width=1,191) (actual time=10.613..10.613 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
21. 5.210 10.493 ↑ 1.0 10 1

Hash Right Join (cost=57.31..72.38 rows=10 width=1,191) (actual time=0.854..10.493 rows=10 loops=1)

  • Hash Cond: ((vuln_taggings.vulnerability_id)::text = (vulnerabilities.id)::text)
22. 4.954 4.954 ↑ 1.1 647 1

Seq Scan on vuln_taggings (cost=0.00..12.25 rows=725 width=21) (actual time=0.014..4.954 rows=647 loops=1)

23. 0.105 0.329 ↑ 1.0 10 1

Hash (cost=57.19..57.19 rows=10 width=1,170) (actual time=0.329..0.329 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
24. 0.163 0.224 ↑ 1.0 10 1

Bitmap Heap Scan on vulnerabilities (cost=26.83..57.19 rows=10 width=1,170) (actual time=0.083..0.224 rows=10 loops=1)

  • Recheck Cond: ((id)::text = ANY ('{epicshears-29,epicsword-32,seedacorn-45,epicscissors-66,epicpowder-91,seedacorn-56,epicscissors-29,epicshears-15,epicshears-91,epicshears-43}'::text[]))
  • Filter: (accepted AND (org_profile_id = 1))
  • Heap Blocks: exact=10
25. 0.061 0.061 ↑ 1.0 10 1

Bitmap Index Scan on index_vulnerabilities_on_id (cost=0.00..26.82 rows=10 width=0) (actual time=0.061..0.061 rows=10 loops=1)

  • Index Cond: ((id)::text = ANY ('{epicshears-29,epicsword-32,seedacorn-45,epicscissors-66,epicpowder-91,seedacorn-56,epicscissors-29,epicshears-15,epicshears-91,epicshears-43}'::text[]))
26. 0.778 1.470 ↑ 1.0 99 1

Hash (cost=3.99..3.99 rows=99 width=152) (actual time=1.470..1.470 rows=99 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 27kB
27. 0.692 0.692 ↑ 1.0 99 1

Seq Scan on vulnerability_categories (cost=0.00..3.99 rows=99 width=152) (actual time=0.024..0.692 rows=99 loops=1)

28. 0.267 0.447 ↑ 32.6 27 1

Hash (cost=18.80..18.80 rows=880 width=64) (actual time=0.447..0.447 rows=27 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
29. 0.180 0.180 ↑ 32.6 27 1

Seq Scan on vulnerability_statuses (cost=0.00..18.80 rows=880 width=64) (actual time=0.010..0.180 rows=27 loops=1)

30. 0.263 0.285 ↑ 1.0 1 19

Materialize (cost=0.14..8.17 rows=1 width=530) (actual time=0.008..0.015 rows=1 loops=19)

31. 0.022 0.022 ↑ 1.0 1 1

Index Scan using organization_profiles_pkey on organization_profiles (cost=0.14..8.16 rows=1 width=530) (actual time=0.015..0.022 rows=1 loops=1)

  • Index Cond: (id = 1)
32. 0.418 0.418 ↑ 1.0 1 19

Index Scan using tags_pkey on tags (cost=0.15..0.20 rows=1 width=47) (actual time=0.013..0.022 rows=1 loops=19)

  • Index Cond: (id = vuln_taggings_vulnerabilities_join.tag_id)
33. 0.285 0.285 ↑ 1.0 1 19

Index Scan using listings_pkey on listings (cost=0.14..3.76 rows=1 width=8) (actual time=0.009..0.015 rows=1 loops=19)

  • Index Cond: (id = vulnerabilities.listing_id)
34. 0.285 0.285 ↑ 1.0 1 19

Index Scan using categories_pkey on categories (cost=0.15..0.46 rows=1 width=286) (actual time=0.008..0.015 rows=1 loops=19)

  • Index Cond: (id = listings.category_id)
35. 0.285 0.285 ↑ 1.0 1 19

Index Scan using users_pkey on users (cost=0.14..1.76 rows=1 width=668) (actual time=0.009..0.015 rows=1 loops=19)

  • Index Cond: (id = vulnerabilities.user_id)
36. 0.323 0.323 ↑ 1.0 1 19

Index Scan using index_researcher_profiles_on_user_id on researcher_profiles (cost=0.14..1.46 rows=1 width=1,073) (actual time=0.010..0.017 rows=1 loops=19)

  • Index Cond: (user_id = users.id)
37. 0.361 0.361 ↑ 1.0 1 19

Index Scan using listings_pkey on listings listings_vulnerabilities (cost=0.14..3.76 rows=1 width=482) (actual time=0.009..0.019 rows=1 loops=19)

  • Index Cond: (id = vulnerabilities.listing_id)
38. 0.285 0.285 ↑ 1.0 1 19

Index Scan using categories_pkey on categories categories_listings (cost=0.15..0.46 rows=1 width=286) (actual time=0.009..0.015 rows=1 loops=19)

  • Index Cond: (id = listings_vulnerabilities.category_id)