explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vSGn

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 43,663.124 ↓ 10.0 40 1

Unique (cost=3,563,624.58..3,563,624.60 rows=4 width=14) (actual time=43,663.118..43,663.124 rows=40 loops=1)

2.          

CTE hostnames

3. 0.109 0.810 ↑ 721.5 39 1

HashAggregate (cost=375.82..657.19 rows=28,137 width=8) (actual time=0.731..0.810 rows=39 loops=1)

  • Group Key: h.id
4. 0.004 0.701 ↑ 721.5 39 1

Append (cost=1.27..305.48 rows=28,137 width=8) (actual time=0.108..0.701 rows=39 loops=1)

5. 0.002 0.110 ↑ 28,135.0 1 1

Nested Loop (cost=1.27..10.77 rows=28,135 width=8) (actual time=0.107..0.110 rows=1 loops=1)

6. 0.008 0.073 ↑ 1.0 1 1

Nested Loop (cost=0.83..4.88 rows=1 width=8) (actual time=0.071..0.073 rows=1 loops=1)

7. 0.013 0.013 ↑ 1.0 1 1

Index Scan using organisations_pkey on organisations o (cost=0.15..2.17 rows=1 width=11) (actual time=0.012..0.013 rows=1 loops=1)

  • Index Cond: (id = 33)
8. 0.052 0.052 ↑ 1.0 1 1

Index Only Scan using cloudscans_hostname_by_hostname_vendorid on cloudscans_hostnames oh (cost=0.69..2.70 rows=1 width=70) (actual time=0.051..0.052 rows=1 loops=1)

  • Index Cond: (hostname = o.primary_hostname)
  • Heap Fetches: 1
9. 0.035 0.035 ↑ 181.0 1 1

Index Scan using cloudscans_hostnames_vendorid_enabled_without_id on cloudscans_hostnames h (cost=0.43..4.08 rows=181 width=16) (actual time=0.034..0.035 rows=1 loops=1)

  • Index Cond: ((enabled = true) AND (datastore_vendor_id = oh.datastore_vendor_id))
  • Filter: enabled
10. 0.008 0.587 ↓ 19.0 38 1

Nested Loop (cost=0.86..13.34 rows=2 width=8) (actual time=0.041..0.587 rows=38 loops=1)

11. 0.006 0.006 ↑ 1.0 1 1

Index Only Scan using organisations_pkey on organisations o_1 (cost=0.15..2.17 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=1)

  • Index Cond: (id = 33)
  • Heap Fetches: 1
12. 0.046 0.573 ↓ 19.0 38 1

Nested Loop (cost=0.71..11.16 rows=2 width=16) (actual time=0.033..0.573 rows=38 loops=1)

13. 0.047 0.047 ↓ 16.0 48 1

Index Scan using cloudscans_orgmap_orgid on cloudscans_orgmap co (cost=0.28..3.77 rows=3 width=16) (actual time=0.015..0.047 rows=48 loops=1)

  • Index Cond: (org_id = 33)
14. 0.480 0.480 ↑ 1.0 1 48

Index Scan using cloudscans_hostnames_pkey on cloudscans_hostnames h_1 (cost=0.43..2.45 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=48)

  • Index Cond: (id = co.hostname_id)
  • Filter: enabled
  • Rows Removed by Filter: 0
15.          

CTE host_vulns

16. 23,129.886 43,660.104 ↑ 4,410.2 483 1

Hash Join (cost=1,365,260.48..3,512,267.10 rows=2,130,126 width=16) (actual time=20,745.457..43,660.104 rows=483 loops=1)

  • Hash Cond: (vhc.vuln_hostname_id = vh.id)
17. 20,527.471 20,527.471 ↑ 1.0 64,326,886 1

Seq Scan on vulns_hostnames_cpes vhc (cost=0.00..1,149,243.91 rows=65,283,891 width=12) (actual time=0.058..20,527.471 rows=64,326,886 loops=1)

18. 1.171 2.747 ↑ 4,418.0 483 1

Hash (cost=1,326,082.99..1,326,082.99 rows=2,133,879 width=20) (actual time=2.747..2.747 rows=483 loops=1)

  • Buckets: 65,536 Batches: 64 Memory Usage: 513kB
19. 0.125 1.576 ↑ 4,418.0 483 1

Nested Loop (cost=0.56..1,326,082.99 rows=2,133,879 width=20) (actual time=0.808..1.576 rows=483 loops=1)

20. 0.827 0.827 ↑ 721.5 39 1

CTE Scan on hostnames h_2 (cost=0.00..562.74 rows=28,137 width=8) (actual time=0.733..0.827 rows=39 loops=1)

21. 0.624 0.624 ↑ 6.3 12 39

Index Scan using temp on vulns_hostnames vh (cost=0.56..46.35 rows=76 width=20) (actual time=0.011..0.016 rows=12 loops=39)

  • Index Cond: (hostname_id = h_2.id)
22. 0.228 43,663.117 ↓ 10.0 40 1

Sort (cost=50,700.28..50,700.29 rows=4 width=14) (actual time=43,663.116..43,663.117 rows=40 loops=1)

  • Sort Key: v.cve_name
  • Sort Method: quicksort Memory: 26kB
23. 0.165 43,662.889 ↓ 10.0 40 1

Nested Loop (cost=2.74..50,700.24 rows=4 width=14) (actual time=36,126.169..43,662.889 rows=40 loops=1)

24. 0.132 43,662.164 ↓ 10.0 40 1

Nested Loop Left Join (cost=2.46..50,699.01 rows=4 width=4) (actual time=36,126.150..43,662.164 rows=40 loops=1)

  • Join Filter: (((voi.hostname_id IS NULL) OR (voi.hostname_id = hv.hostname_id)) AND (voi.vuln_id = hv.vuln_id))
  • Filter: (voi.id IS NULL)
25. 0.847 43,661.992 ↑ 21.7 40 1

Hash Join (cost=2.31..50,601.49 rows=869 width=12) (actual time=36,126.132..43,661.992 rows=40 loops=1)

  • Hash Cond: (hv.cpe_id = c.id)
26. 43,661.102 43,661.102 ↑ 4,410.2 483 1

CTE Scan on host_vulns hv (cost=0.00..42,602.52 rows=2,130,126 width=16) (actual time=20,745.461..43,661.102 rows=483 loops=1)

27. 0.002 0.043 ↑ 1.0 1 1

Hash (cost=2.30..2.30 rows=1 width=4) (actual time=0.043..0.043 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
28. 0.041 0.041 ↑ 1.0 1 1

Index Scan using vulns_cpes_name_uindex on vulns_cpes c (cost=0.28..2.30 rows=1 width=4) (actual time=0.039..0.041 rows=1 loops=1)

  • Index Cond: ((name)::text = 'cpe:/a:apache:http_server:2.2.16'::text)
29. 0.032 0.040 ↓ 0.0 0 40

Materialize (cost=0.15..6.29 rows=6 width=20) (actual time=0.001..0.001 rows=0 loops=40)

30. 0.008 0.008 ↓ 0.0 0 1

Index Scan using vulns_org_ignored_organisation_id_vuln_id_hostname_id_key on vulns_org_ignored voi (cost=0.15..6.26 rows=6 width=20) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: (organisation_id = 33)
31. 0.560 0.560 ↑ 1.0 1 40

Index Scan using vulns_pk on vulns v (cost=0.28..0.30 rows=1 width=18) (actual time=0.013..0.014 rows=1 loops=40)

  • Index Cond: (id = hv.vuln_id)
Planning time : 3.170 ms
Execution time : 43,663.486 ms