explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZvNi

Settings
# exclusive inclusive rows x rows loops node
1. 1.206 1,785.803 ↑ 1.3 161 1

Nested Loop Left Join (cost=404.46..16,339.66 rows=208 width=109) (actual time=165.127..1,785.803 rows=161 loops=1)

2. 34.028 806.844 ↑ 1.3 161 1

Subquery Scan on watchdata (cost=403.89..15,373.49 rows=208 width=100) (actual time=156.353..806.844 rows=161 loops=1)

  • Filter: (((NOT watchdata.scored) OR watchdata.scored) AND (((watchdata.overridden_name IS NOT NULL) AND (watchdata.overridden_name ~~* '%'::text)) OR ((watchdata.overridden_name IS NULL) AND (watchdata.name ~~* '%'::text)) OR (watchdata.primary_hostname ~~* '%'::text)))
3. 0.746 772.816 ↑ 1.7 161 1

Unique (cost=403.89..15,368.62 rows=278 width=100) (actual time=125.621..772.816 rows=161 loops=1)

4. 2.782 772.070 ↑ 1.7 161 1

Nested Loop Left Join (cost=403.89..15,367.92 rows=279 width=100) (actual time=125.601..772.070 rows=161 loops=1)

  • Join Filter: ((vor.org_id = owl.organisation_id) AND (owl.datastore_vendor_id = vor.datastore_vendor_id))
  • Rows Removed by Join Filter: 481
5. 0.976 639.200 ↑ 1.7 161 1

Nested Loop (cost=403.89..2,761.21 rows=279 width=63) (actual time=124.012..639.200 rows=161 loops=1)

6. 0.374 122.863 ↑ 1.7 161 1

Merge Left Join (cost=403.46..404.87 rows=279 width=29) (actual time=122.127..122.863 rows=161 loops=1)

  • Merge Cond: (owl.datastore_vendor_id = vv.datastore_vendor_id)
7. 0.520 122.464 ↑ 1.7 161 1

Sort (cost=402.44..403.14 rows=279 width=21) (actual time=122.098..122.464 rows=161 loops=1)

  • Sort Key: owl.datastore_vendor_id
  • Sort Method: quicksort Memory: 37kB
8. 121.841 121.944 ↑ 1.7 161 1

Bitmap Heap Scan on organisation_vendor_watchlist owl (cost=10.64..391.10 rows=279 width=21) (actual time=0.147..121.944 rows=161 loops=1)

  • Recheck Cond: (organisation_id = 166)
  • Filter: watched
  • Rows Removed by Filter: 149
  • Heap Blocks: exact=220
9. 0.103 0.103 ↓ 1.3 403 1

Bitmap Index Scan on unique_org_vendor (cost=0.00..10.57 rows=305 width=0) (actual time=0.102..0.103 rows=403 loops=1)

  • Index Cond: (organisation_id = 166)
10. 0.015 0.025 ↑ 1.0 1 1

Sort (cost=1.02..1.02 rows=1 width=8) (actual time=0.024..0.025 rows=1 loops=1)

  • Sort Key: vv.datastore_vendor_id
  • Sort Method: quicksort Memory: 25kB
11. 0.010 0.010 ↑ 1.0 1 1

Seq Scan on verified_vendors vv (cost=0.00..1.01 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=1)

  • Filter: (deleted_by IS NULL)
12. 515.361 515.361 ↑ 1.0 1 161

Index Scan using vendors_datastore_vendor_id_idx on vendors v (cost=0.43..8.44 rows=1 width=42) (actual time=3.148..3.201 rows=1 loops=161)

  • Index Cond: (datastore_vendor_id = owl.datastore_vendor_id)
13. 0.247 1.127 ↑ 1.3 3 161

Materialize (cost=0.00..83.32 rows=4 width=31) (actual time=0.004..0.007 rows=3 loops=161)

14. 0.880 0.880 ↑ 1.3 3 1

Seq Scan on organisation_vendor_overrides vor (cost=0.00..83.30 rows=4 width=31) (actual time=0.493..0.880 rows=3 loops=1)

  • Filter: ((org_id = 166) AND (field_name = 'Name'::text))
  • Rows Removed by Filter: 3091
15.          

SubPlan (for Nested Loop Left Join)

16. 1.288 114.149 ↑ 1.0 1 161

Aggregate (cost=18.23..18.24 rows=1 width=1) (actual time=0.709..0.709 rows=1 loops=161)

17. 0.805 112.861 ↓ 0.0 0 161

Nested Loop (cost=0.57..18.23 rows=1 width=0) (actual time=0.637..0.701 rows=0 loops=161)

  • Join Filter: (vl.label_id = l.id)
  • Rows Removed by Join Filter: 0
18. 3.703 3.703 ↑ 1.0 1 161

Index Scan using idx_labels_labelname on labels l (cost=0.28..8.30 rows=1 width=4) (actual time=0.022..0.023 rows=1 loops=161)

  • Index Cond: (lower(name) = 'in-use'::text)
  • Filter: (classification = 'S'::text)
19. 108.353 108.353 ↑ 2.0 1 161

Index Scan using organisation_vendor_labels_vendorwatch_id_idx on organisation_vendor_labels vl (cost=0.29..9.90 rows=2 width=4) (actual time=0.597..0.673 rows=1 loops=161)

  • Index Cond: (vendorwatch_id = owl.id)
20. 2.093 14.812 ↑ 1.0 1 161

Aggregate (cost=26.56..26.57 rows=1 width=32) (actual time=0.092..0.092 rows=1 loops=161)

21. 0.644 12.719 ↑ 2.0 1 161

Subquery Scan on labels (cost=26.53..26.55 rows=2 width=52) (actual time=0.079..0.079 rows=1 loops=161)

22. 1.932 12.075 ↑ 2.0 1 161

Sort (cost=26.53..26.53 rows=2 width=28) (actual time=0.074..0.075 rows=1 loops=161)

  • Sort Key: l_1.classification, l_1.name
  • Sort Method: quicksort Memory: 25kB
23. 0.591 10.143 ↑ 2.0 1 161

Nested Loop (cost=0.57..26.52 rows=2 width=28) (actual time=0.047..0.063 rows=1 loops=161)

24. 0.966 0.966 ↑ 2.0 1 161

Index Scan using organisation_vendor_labels_vendorwatch_id_idx on organisation_vendor_labels vl_1 (cost=0.29..9.90 rows=2 width=4) (actual time=0.006..0.006 rows=1 loops=161)

  • Index Cond: (vendorwatch_id = owl.id)
  • Filter: (deleted_at IS NULL)
25. 8.586 8.586 ↑ 1.0 1 81

Index Scan using labels_pkey on labels l_1 (cost=0.28..8.30 rows=1 width=28) (actual time=0.106..0.106 rows=1 loops=81)

  • Index Cond: (id = vl_1.label_id)
  • Filter: (deleted_at IS NULL)
26. 0.644 977.753 ↑ 1.0 1 161

Limit (cost=0.57..4.63 rows=1 width=12) (actual time=6.072..6.073 rows=1 loops=161)

27. 977.109 977.109 ↑ 699.0 1 161

Index Scan using vendor_scores_vendor_scored_at on vendor_scores (cost=0.57..2,836.80 rows=699 width=12) (actual time=6.069..6.069 rows=1 loops=161)

  • Index Cond: (datastore_vendor_id = watchdata.datastore_vendor_id)
Planning time : 1.645 ms
Execution time : 1,786.131 ms