explain.depesz.com

PostgreSQL's explain analyze made readable

Result: slGj

Settings
# exclusive inclusive rows x rows loops node
1. 0.226 7.644 ↑ 1.3 161 1

Subquery Scan on watchdata (cost=403.89..16,671.55 rows=208 width=129) (actual time=1.528..7.644 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)))
2. 0.060 7.418 ↑ 1.7 161 1

Unique (cost=403.89..16,666.69 rows=278 width=132) (actual time=1.516..7.418 rows=161 loops=1)

3. 0.215 7.358 ↑ 1.7 161 1

Nested Loop Left Join (cost=403.89..16,665.99 rows=279 width=132) (actual time=1.514..7.358 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
4. 0.000 1.991 ↑ 1.7 161 1

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

5. 0.045 0.721 ↑ 1.7 161 1

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

  • Merge Cond: (owl.datastore_vendor_id = vv.datastore_vendor_id)
6. 0.089 0.662 ↑ 1.7 161 1

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

  • Sort Key: owl.datastore_vendor_id
  • Sort Method: quicksort Memory: 37kB
7. 0.524 0.573 ↑ 1.7 161 1

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

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

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

  • Index Cond: (organisation_id = 166)
9. 0.007 0.014 ↑ 1.0 1 1

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

  • Sort Key: vv.datastore_vendor_id
  • Sort Method: quicksort Memory: 25kB
10. 0.007 0.007 ↑ 1.0 1 1

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

  • Filter: (deleted_by IS NULL)
11. 1.288 1.288 ↑ 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=0.007..0.008 rows=1 loops=161)

  • Index Cond: (datastore_vendor_id = owl.datastore_vendor_id)
12. 0.091 0.805 ↑ 1.3 3 161

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

13. 0.714 0.714 ↑ 1.3 3 1

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

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

SubPlan (for Nested Loop Left Join)

15. 0.483 2.093 ↑ 1.0 1 161

Aggregate (cost=4.64..4.65 rows=1 width=32) (actual time=0.013..0.013 rows=1 loops=161)

16. 0.000 1.610 ↑ 1.0 1 161

Subquery Scan on vendor_scores (cost=0.57..4.64 rows=1 width=36) (actual time=0.010..0.010 rows=1 loops=161)

17. 0.161 1.610 ↑ 1.0 1 161

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

18. 1.449 1.449 ↑ 699.0 1 161

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

  • Index Cond: (datastore_vendor_id = owl.datastore_vendor_id)
19. 0.000 1.127 ↑ 1.0 1 161

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

20. 0.161 1.127 ↓ 0.0 0 161

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

  • Join Filter: (vl.label_id = l.id)
  • Rows Removed by Join Filter: 0
21. 0.483 0.483 ↑ 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.003..0.003 rows=1 loops=161)

  • Index Cond: (lower(name) = 'in-use'::text)
  • Filter: (classification = 'S'::text)
22. 0.483 0.483 ↑ 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.003..0.003 rows=1 loops=161)

  • Index Cond: (vendorwatch_id = owl.id)
23. 0.322 1.127 ↑ 1.0 1 161

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

24. 0.000 0.805 ↑ 2.0 1 161

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

25. 0.322 0.805 ↑ 2.0 1 161

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

  • Sort Key: l_1.classification, l_1.name
  • Sort Method: quicksort Memory: 25kB
26. 0.000 0.483 ↑ 2.0 1 161

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

27. 0.322 0.322 ↑ 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.002..0.002 rows=1 loops=161)

  • Index Cond: (vendorwatch_id = owl.id)
  • Filter: (deleted_at IS NULL)
28. 0.162 0.162 ↑ 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.002..0.002 rows=1 loops=81)

  • Index Cond: (id = vl_1.label_id)
  • Filter: (deleted_at IS NULL)
Planning time : 1.132 ms
Execution time : 7.801 ms