explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bD6na

Settings
# exclusive inclusive rows x rows loops node
1. 1.750 604.553 ↓ 1.3 264 1

Subquery Scan on watchdata (cost=403.89..15,369.49 rows=208 width=129) (actual time=25.339..604.553 rows=264 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.407 602.803 ↑ 1.1 264 1

Unique (cost=403.89..15,364.62 rows=278 width=132) (actual time=25.293..602.803 rows=264 loops=1)

3. 2.275 602.396 ↑ 1.1 264 1

Nested Loop Left Join (cost=403.89..15,363.92 rows=279 width=132) (actual time=25.291..602.396 rows=264 loops=1)

  • Join Filter: ((vor.org_id = owl.organisation_id) AND (owl.datastore_vendor_id = vor.datastore_vendor_id))
  • Rows Removed by Join Filter: 790
4. 0.398 517.225 ↑ 1.1 264 1

Nested Loop (cost=403.89..2,757.21 rows=279 width=63) (actual time=24.351..517.225 rows=264 loops=1)

5. 0.302 23.147 ↑ 1.1 264 1

Merge Left Join (cost=403.46..404.87 rows=279 width=29) (actual time=22.667..23.147 rows=264 loops=1)

  • Merge Cond: (owl.datastore_vendor_id = vv.datastore_vendor_id)
6. 0.415 22.819 ↑ 1.1 264 1

Sort (cost=402.44..403.14 rows=279 width=21) (actual time=22.637..22.819 rows=264 loops=1)

  • Sort Key: owl.datastore_vendor_id
  • Sort Method: quicksort Memory: 45kB
7. 22.317 22.404 ↑ 1.1 264 1

Bitmap Heap Scan on organisation_vendor_watchlist owl (cost=10.64..391.10 rows=279 width=21) (actual time=0.124..22.404 rows=264 loops=1)

  • Recheck Cond: (organisation_id = 166)
  • Filter: watched
  • Rows Removed by Filter: 36
  • Heap Blocks: exact=166
8. 0.087 0.087 ↑ 1.0 300 1

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

  • Index Cond: (organisation_id = 166)
9. 0.019 0.026 ↑ 1.0 1 1

Sort (cost=1.02..1.02 rows=1 width=8) (actual time=0.025..0.026 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. 493.680 493.680 ↑ 1.0 1 264

Index Scan using vendors_datastore_vendor_id_idx on vendors v (cost=0.43..8.42 rows=1 width=42) (actual time=1.855..1.870 rows=1 loops=264)

  • Index Cond: (datastore_vendor_id = owl.datastore_vendor_id)
12. 0.336 1.056 ↑ 1.3 3 264

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

13. 0.720 0.720 ↑ 1.3 3 1

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

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

SubPlan (for Nested Loop Left Join)

15. 0.792 70.488 ↑ 1.0 1 264

Aggregate (cost=18.23..18.24 rows=1 width=1) (actual time=0.267..0.267 rows=1 loops=264)

16. 0.528 69.696 ↓ 0.0 0 264

Nested Loop (cost=0.57..18.23 rows=1 width=0) (actual time=0.249..0.264 rows=0 loops=264)

  • Join Filter: (vl.label_id = l.id)
  • Rows Removed by Join Filter: 0
17. 3.168 3.168 ↑ 1.0 1 264

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

  • Index Cond: (lower(name) = 'in-use'::text)
  • Filter: (classification = 'S'::text)
18. 66.000 66.000 ↓ 0.0 0 264

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.230..0.250 rows=0 loops=264)

  • Index Cond: (vendorwatch_id = owl.id)
19. 1.584 11.352 ↑ 1.0 1 264

Aggregate (cost=26.56..26.57 rows=1 width=32) (actual time=0.043..0.043 rows=1 loops=264)

20. 0.528 9.768 ↓ 0.0 0 264

Subquery Scan on labels (cost=26.53..26.55 rows=2 width=52) (actual time=0.037..0.037 rows=0 loops=264)

21. 1.848 9.240 ↓ 0.0 0 264

Sort (cost=26.53..26.53 rows=2 width=28) (actual time=0.035..0.035 rows=0 loops=264)

  • Sort Key: l_1.classification, l_1.name
  • Sort Method: quicksort Memory: 25kB
22. 0.396 7.392 ↓ 0.0 0 264

Nested Loop (cost=0.57..26.52 rows=2 width=28) (actual time=0.022..0.028 rows=0 loops=264)

23. 0.792 0.792 ↓ 0.0 0 264

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.003..0.003 rows=0 loops=264)

  • Index Cond: (vendorwatch_id = owl.id)
  • Filter: (deleted_at IS NULL)
24. 6.204 6.204 ↑ 1.0 1 132

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

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