explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 52Qw

Settings
# exclusive inclusive rows x rows loops node
1. 8.364 5,123.203 ↓ 1.4 975 1

Subquery Scan on watchdata (cost=467.97..58,567.63 rows=684 width=129) (actual time=65.764..5,123.203 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.186 5,114.839 ↓ 1.1 975 1

Unique (cost=467.97..58,551.67 rows=912 width=132) (actual time=65.741..5,114.839 rows=975 loops=1)

3. 10.679 5,112.653 ↓ 1.1 975 1

Nested Loop Left Join (cost=467.97..58,549.35 rows=928 width=132) (actual time=65.739..5,112.653 rows=975 loops=1)

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

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

5. 1.448 58.457 ↓ 1.1 975 1

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

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

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

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

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

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

  • Index Cond: (organisation_id = 1586)
9. 0.016 0.022 ↑ 1.0 1 1

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

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

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

  • Filter: (deleted_by IS NULL)
11. 1,790.100 1,790.100 ↑ 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=1.831..1.836 rows=1 loops=975)

  • Index Cond: (datastore_vendor_id = owl.datastore_vendor_id)
12. 0.528 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.447 0.447 ↓ 0.0 0 1

Seq Scan on organisation_vendor_overrides vor (cost=0.00..83.30 rows=1 width=31) (actual time=0.447..0.447 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. 18.525 3,009.825 ↑ 1.0 1 975

Aggregate (cost=9.29..9.30 rows=1 width=32) (actual time=3.086..3.087 rows=1 loops=975)

16. 1.950 2,991.300 ↑ 1.0 2 975

Append (cost=0.57..9.28 rows=2 width=32) (actual time=2.818..3.068 rows=2 loops=975)

17. 3.900 2,721.225 ↑ 1.0 1 975

Subquery Scan on first (cost=0.57..4.65 rows=1 width=32) (actual time=2.790..2.791 rows=1 loops=975)

18. 5.850 2,717.325 ↑ 1.0 1 975

Limit (cost=0.57..4.64 rows=1 width=12) (actual time=2.786..2.787 rows=1 loops=975)

19. 2,711.475 2,711.475 ↑ 90.0 1 975

Index Scan Backward using vendor_scores_vendor_scored_at on vendor_scores (cost=0.57..366.37 rows=90 width=12) (actual time=2.781..2.781 rows=1 loops=975)

  • Index Cond: ((datastore_vendor_id = owl.datastore_vendor_id) AND (score_at > (now() - '30 days'::interval)))
20. 2.925 268.125 ↑ 1.0 1 975

Subquery Scan on last (cost=0.57..4.64 rows=1 width=32) (actual time=0.274..0.275 rows=1 loops=975)

21. 1.950 265.200 ↑ 1.0 1 975

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

22. 263.250 263.250 ↑ 688.0 1 975

Index Scan using vendor_scores_vendor_scored_at on vendor_scores vendor_scores_1 (cost=0.57..2,792.58 rows=688 width=12) (actual time=0.270..0.270 rows=1 loops=975)

  • Index Cond: (datastore_vendor_id = owl.datastore_vendor_id)
23. 2.925 175.500 ↑ 1.0 1 975

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

24. 4.875 172.575 ↓ 4.0 4 975

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

  • Join Filter: (vl.label_id = l.id)
  • Rows Removed by Join Filter: 2
25. 11.700 11.700 ↑ 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.011..0.012 rows=1 loops=975)

  • Index Cond: (lower(name) = 'in-use'::text)
  • Filter: (classification = 'S'::text)
26. 156.000 156.000 ↓ 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.043..0.160 rows=6 loops=975)

  • Index Cond: (vendorwatch_id = owl.id)
27. 20.475 64.350 ↑ 1.0 1 975

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

28. 3.900 43.875 ↓ 3.0 6 975

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

29. 13.650 39.975 ↓ 3.0 6 975

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

  • Sort Key: l_1.classification, l_1.name
  • Sort Method: quicksort Memory: 25kB
30. 3.288 26.325 ↓ 3.0 6 975

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

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

  • Index Cond: (vendorwatch_id = owl.id)
  • Filter: (deleted_at IS NULL)
32. 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.749 ms
Execution time : 5,123.673 ms