explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Rn3A

Settings
# exclusive inclusive rows x rows loops node
1. 10.108 4,887.378 ↓ 1.4 975 1

Subquery Scan on watchdata (cost=467.97..2,654,479.22 rows=684 width=129) (actual time=6.058..4,887.378 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. 19.668 4,877.270 ↓ 1.1 975 1

Unique (cost=467.97..2,654,463.26 rows=912 width=132) (actual time=6.040..4,877.270 rows=975 loops=1)

3. 11.872 4,857.602 ↓ 1.1 975 1

Nested Loop Left Join (cost=467.97..2,654,460.94 rows=928 width=132) (actual time=6.038..4,857.602 rows=975 loops=1)

  • Join Filter: ((vor.org_id = owl.organisation_id) AND (owl.datastore_vendor_id = vor.datastore_vendor_id))
4. 2.282 38.980 ↓ 1.1 975 1

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

5. 1.779 4.523 ↓ 1.1 975 1

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

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

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

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

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

  • Recheck Cond: (organisation_id = 1586)
  • Filter: watched
  • Heap Blocks: exact=84
8. 0.144 0.144 ↑ 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=0.144..0.144 rows=975 loops=1)

  • Index Cond: (organisation_id = 1586)
9. 0.025 0.034 ↑ 1.0 1 1

Sort (cost=1.02..1.02 rows=1 width=8) (actual time=0.034..0.034 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.009..0.009 rows=1 loops=1)

  • Filter: (deleted_by IS NULL)
11. 32.175 32.175 ↑ 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=0.032..0.033 rows=1 loops=975)

  • Index Cond: (datastore_vendor_id = owl.datastore_vendor_id)
12. 0.269 0.975 ↓ 0.0 0 975

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

13. 0.706 0.706 ↓ 0.0 0 1

Seq Scan on organisation_vendor_overrides vor (cost=0.00..83.30 rows=1 width=31) (actual time=0.706..0.706 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. 137.475 4,613.700 ↑ 1.0 1 975

Aggregate (cost=2,806.61..2,806.62 rows=1 width=32) (actual time=4.732..4.732 rows=1 loops=975)

16. 23.400 4,476.225 ↑ 10.9 64 975

Subquery Scan on vendor_scores (cost=2,796.12..2,804.86 rows=699 width=36) (actual time=4.559..4.591 rows=64 loops=975)

17. 36.075 4,452.825 ↑ 10.9 64 975

Sort (cost=2,796.12..2,797.87 rows=699 width=12) (actual time=4.557..4.567 rows=64 loops=975)

  • Sort Key: vendor_scores_1.score_at
  • Sort Method: quicksort Memory: 25kB
18. 1,837.875 4,416.750 ↑ 10.9 64 975

Bitmap Heap Scan on vendor_scores vendor_scores_1 (cost=25.98..2,763.10 rows=699 width=12) (actual time=3.328..4.530 rows=64 loops=975)

  • Recheck Cond: (datastore_vendor_id = owl.datastore_vendor_id)
  • Heap Blocks: exact=2894
19. 2,578.875 2,578.875 ↑ 7.4 94 975

Bitmap Index Scan on vendor_scores_datastore_vendor_id_idx (cost=0.00..25.81 rows=699 width=0) (actual time=2.645..2.645 rows=94 loops=975)

  • Index Cond: (datastore_vendor_id = owl.datastore_vendor_id)
20. 4.875 78.975 ↑ 1.0 1 975

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

21. 3.900 74.100 ↓ 4.0 4 975

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

  • Join Filter: (vl.label_id = l.id)
  • Rows Removed by Join Filter: 2
22. 18.525 18.525 ↑ 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.019..0.019 rows=1 loops=975)

  • Index Cond: (lower(name) = 'in-use'::text)
  • Filter: (classification = 'S'::text)
23. 51.675 51.675 ↓ 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.028..0.053 rows=6 loops=975)

  • Index Cond: (vendorwatch_id = owl.id)
24. 32.175 113.100 ↑ 1.0 1 975

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

25. 5.850 80.925 ↓ 3.0 6 975

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

26. 15.600 75.075 ↓ 3.0 6 975

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

  • Sort Key: l_1.classification, l_1.name
  • Sort Method: quicksort Memory: 25kB
27. 8.443 59.475 ↓ 3.0 6 975

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

28. 7.800 7.800 ↓ 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.008 rows=6 loops=975)

  • Index Cond: (vendorwatch_id = owl.id)
  • Filter: (deleted_at IS NULL)
29. 43.232 43.232 ↑ 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.008..0.008 rows=1 loops=5,404)

  • Index Cond: (id = vl_1.label_id)
  • Filter: (deleted_at IS NULL)
Planning time : 2.207 ms
Execution time : 4,887.993 ms