explain.depesz.com

PostgreSQL's explain analyze made readable

Result: p4Ch

Settings
# exclusive inclusive rows x rows loops node
1. 4.757 29,136.299 ↓ 1.3 264 1

Subquery Scan on watchdata (cost=403.89..752,968.56 rows=208 width=129) (actual time=15.545..29,136.299 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. 15.867 29,131.542 ↑ 1.1 264 1

Unique (cost=403.89..752,963.70 rows=278 width=132) (actual time=15.044..29,131.542 rows=264 loops=1)

3. 28,394.944 29,115.675 ↑ 1.1 264 1

Nested Loop Left Join (cost=403.89..752,963.00 rows=279 width=132) (actual time=15.028..29,115.675 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: 790re_vendor_id)
  • SubPlan 1 SELECT array_to_json(array_agg(labels)) FROM (
  • SubPlan 2 AND vl.label_id = l.id
4. 1.183 699.611 ↑ 1.1 264 1

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

5. 0.864 4.636 ↑ 1.1 264 1

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

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

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

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

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

  • Recheck Cond: (organisation_id = 166))) FROM (
  • Filter: watchedscore_at from vendor_scores
  • Rows Removed by Filter: 36 = owl.datastore_vendor_id
  • Heap Blocks: exact=166
8. 0.124 0.124 ↑ 1.0 300 1

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

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

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

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

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

  • Filter: (deleted_by IS NULL)
11. 693.792 693.792 ↑ 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=2.610..2.628 rows=1 loops=264)

  • Index Cond: (datastore_vendor_id = owl.datastore_vendor_id)
12. 0.000 1.320 ↑ 1.3 3 264

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

13. 0.862 0.862 ↑ 1.3 3 1

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

  • Filter: ((org_id = 166) AND (field_name = 'Name'::text))
  • Rows Removed by Filter: 3087
14. 397.848 28,245.096 ↑ 1.0 1 264

Aggregate (cost=2,643.71..2,643.72 rows=1 width=32) (actual time=106.988..106.989 rows=1 loops=264)

15. 60.192 27,847.248 ↓ 1.3 837 264

Subquery Scan on vendor_scores (cost=2,633.82..2,642.06 rows=659 width=36) (actual time=105.137..105.482 rows=837 loops=264)

16. 93.720 27,787.056 ↓ 1.3 837 264

Sort (cost=2,633.82..2,635.47 rows=659 width=12) (actual time=105.132..105.254 rows=837 loops=264)

  • Sort Key: vendor_scores_1.score_at
  • Sort Method: quicksort Memory: 34kB
17. 27,693.336 27,693.336 ↓ 1.3 837 264

Bitmap Heap Scan on vendor_scores vendor_scores_1 (cost=21.67..2,602.97 rows=659 width=12) (actual time=77.136..104.899 rows=837 loops=264)

  • Recheck Cond: (datastore_vendor_id = owl.datastore_vendor_id)
  • Heap Blocks: exact=57056
  • -> Bitmap Index Scan on vendor_scores_datastore_vendor_id_idx (cost=0.00..21.51 rows=659 width=0) (actual time=27.176..27.176 rows=7770 loops=264) WHERE
  • Index Cond: (datastore_vendor_id = owl.datastore_vendor_id)
18. 2.112 132.528 ↑ 1.0 1 264

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

  • -> Nested Loop (cost=0.57..18.23 rows=1 width=0) (actual time=0.477..0.496 rows=0 loops=264)vendor_id AND vor.org_id = owl.organisation_id AND vor.field_
  • Join Filter: (vl.label_id = l.id)
  • Rows Removed by Join Filter: 0
19. 7.656 7.656 ↑ 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.028..0.029 rows=1 loops=264)

  • Index Cond: (lower(name) = 'in-use'::text)
  • Filter: (classification = 'S'::text)
20. 122.760 122.760 ↓ 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.439..0.465 rows=0 loops=264)

  • Index Cond: (vendorwatch_id = owl.id)
21.          

SubPlan (for Nested Loop Left Join)

22. 2.112 19.800 ↑ 1.0 1 264

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

23. 3.696 17.688 ↓ 0.0 0 264

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

24. 2.904 13.992 ↓ 0.0 0 264

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

  • Sort Key: l_1.classification, l_1.name
  • Sort Method: quicksort Memory: 25kB
25. 0.924 11.088 ↓ 0.0 0 264

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

26. 1.056 1.056 ↓ 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.004..0.004 rows=0 loops=264)

  • Index Cond: (vendorwatch_id = owl.id)
  • Filter: (deleted_at IS NULL)
27. 9.108 9.108 ↑ 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.069..0.069 rows=1 loops=132)

  • Index Cond: (id = vl_1.label_id)
  • Filter: (deleted_at IS NULL)
Planning time : 1.615 ms
Execution time : 29,136.623 ms