explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2Blk

Settings
# exclusive inclusive rows x rows loops node
1. 8.978 5,832.703 ↓ 1.4 975 1

Subquery Scan on watchdata (cost=467.97..54,252.42 rows=684 width=129) (actual time=36.162..5,832.703 rows=975 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. 2.457 5,823.725 ↓ 1.1 975 1

Unique (cost=467.97..54,236.46 rows=912 width=132) (actual time=36.143..5,823.725 rows=975 loops=1)

3. 11.493 5,821.268 ↓ 1.1 975 1

Nested Loop Left Join (cost=467.97..54,234.14 rows=928 width=132) (actual time=36.141..5,821.268 rows=975 loops=1)

  • Join Filter: ((vor.org_id = owl.organisation_id) AND (owl.datastore_vendor_id = vor.datastore_vendor_id))
4. 2.235 2,201.300 ↓ 1.1 975 1

Nested Loop (cost=467.97..8,227.08 rows=928 width=63) (actual time=29.710..2,201.300 rows=975 loops=1)

5. 1.763 31.640 ↓ 1.1 975 1

Merge Left Join (cost=467.54..472.20 rows=928 width=29) (actual time=28.953..31.640 rows=975 loops=1)

  • Merge Cond: (owl.datastore_vendor_id = vv.datastore_vendor_id)
6. 1.394 29.842 ↓ 1.1 975 1

Sort (cost=466.52..468.84 rows=928 width=21) (actual time=28.914..29.842 rows=975 loops=1)

  • Sort Key: owl.datastore_vendor_id
  • Sort Method: quicksort Memory: 101kB
7. 24.261 28.448 ↓ 1.1 975 1

Bitmap Heap Scan on organisation_vendor_watchlist owl (cost=28.12..420.78 rows=928 width=21) (actual time=4.212..28.448 rows=975 loops=1)

  • Recheck Cond: (organisation_id = 1586)
  • Filter: watched
  • Heap Blocks: exact=84
8. 4.187 4.187 ↑ 1.0 975 1

Bitmap Index Scan on organisation_vendor_watchlist_org_id_idx (cost=0.00..27.89 rows=1,013 width=0) (actual time=4.187..4.187 rows=975 loops=1)

  • Index Cond: (organisation_id = 1586)
9. 0.026 0.035 ↑ 1.0 1 1

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

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

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

  • Filter: (deleted_by IS NULL)
11. 2,167.425 2,167.425 ↑ 1.0 1 975

Index Scan using vendors_datastore_vendor_id_idx on vendors v (cost=0.43..8.35 rows=1 width=42) (actual time=2.216..2.223 rows=1 loops=975)

  • Index Cond: (datastore_vendor_id = owl.datastore_vendor_id)
12. 0.700 4.875 ↓ 0.0 0 975

Materialize (cost=0.00..83.30 rows=1 width=31) (actual time=0.005..0.005 rows=0 loops=975)

13. 4.175 4.175 ↓ 0.0 0 1

Seq Scan on organisation_vendor_overrides vor (cost=0.00..83.30 rows=1 width=31) (actual time=4.174..4.175 rows=0 loops=1)

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

SubPlan (for Nested Loop Left Join)

15. 16.575 3,477.825 ↑ 1.0 1 975

Aggregate (cost=4.64..4.65 rows=1 width=32) (actual time=3.567..3.567 rows=1 loops=975)

16. 3.900 3,461.250 ↑ 1.0 1 975

Subquery Scan on vendor_scores (cost=0.57..4.64 rows=1 width=36) (actual time=3.549..3.550 rows=1 loops=975)

17. 1.950 3,457.350 ↑ 1.0 1 975

Limit (cost=0.57..4.63 rows=1 width=12) (actual time=3.546..3.546 rows=1 loops=975)

18. 3,455.400 3,455.400 ↑ 699.0 1 975

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=3.543..3.544 rows=1 loops=975)

  • Index Cond: (datastore_vendor_id = owl.datastore_vendor_id)
19. 3.900 45.825 ↑ 1.0 1 975

Aggregate (cost=18.23..18.24 rows=1 width=1) (actual time=0.047..0.047 rows=1 loops=975)

20. 4.875 41.925 ↓ 4.0 4 975

Nested Loop (cost=0.57..18.23 rows=1 width=0) (actual time=0.029..0.043 rows=4 loops=975)

  • Join Filter: (vl.label_id = l.id)
  • Rows Removed by Join Filter: 2
21. 14.625 14.625 ↑ 1.0 1 975

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

  • Index Cond: (lower(name) = 'in-use'::text)
  • Filter: (classification = 'S'::text)
22. 22.425 22.425 ↓ 3.0 6 975

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.012..0.023 rows=6 loops=975)

  • Index Cond: (vendorwatch_id = owl.id)
23. 27.300 79.950 ↑ 1.0 1 975

Aggregate (cost=26.56..26.57 rows=1 width=32) (actual time=0.082..0.082 rows=1 loops=975)

24. 4.875 52.650 ↓ 3.0 6 975

Subquery Scan on labels (cost=26.53..26.55 rows=2 width=52) (actual time=0.051..0.054 rows=6 loops=975)

25. 17.550 47.775 ↓ 3.0 6 975

Sort (cost=26.53..26.53 rows=2 width=28) (actual time=0.048..0.049 rows=6 loops=975)

  • Sort Key: l_1.classification, l_1.name
  • Sort Method: quicksort Memory: 25kB
26. 7.188 30.225 ↓ 3.0 6 975

Nested Loop (cost=0.57..26.52 rows=2 width=28) (actual time=0.012..0.031 rows=6 loops=975)

27. 6.825 6.825 ↓ 3.0 6 975

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.004..0.007 rows=6 loops=975)

  • Index Cond: (vendorwatch_id = owl.id)
  • Filter: (deleted_at IS NULL)
28. 16.212 16.212 ↑ 1.0 1 5,404

Index Scan using labels_pkey on labels l_1 (cost=0.28..8.30 rows=1 width=28) (actual time=0.003..0.003 rows=1 loops=5,404)

  • Index Cond: (id = vl_1.label_id)
  • Filter: (deleted_at IS NULL)
Planning time : 1.601 ms
Execution time : 5,833.255 ms