explain.depesz.com

PostgreSQL's explain analyze made readable

Result: woo9

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 9,621.228 ↓ 0.0 0 1

Unique (cost=2,648.10..2,648.11 rows=2 width=124) (actual time=9,621.228..9,621.228 rows=0 loops=1)

2. 0.007 9,621.228 ↓ 0.0 0 1

Sort (cost=2,648.10..2,648.10 rows=2 width=124) (actual time=9,621.228..9,621.228 rows=0 loops=1)

  • Sort Key: ad.uuid
  • Sort Method: quicksort Memory: 25kB
3. 0.002 9,621.221 ↓ 0.0 0 1

Unique (cost=2,648.01..2,648.07 rows=2 width=124) (actual time=9,621.221..9,621.221 rows=0 loops=1)

4. 0.016 9,621.219 ↓ 0.0 0 1

Sort (cost=2,648.01..2,648.01 rows=2 width=124) (actual time=9,621.219..9,621.219 rows=0 loops=1)

  • Sort Key: ad.id, ad.organisation_id, ad.uuid, ad.notification_type_id, ad.enabled, ad.severity, ad.specific_parameters, ad.enable_webhook, ad.webhook_target_url, ad.scoring_surveys, ad.surveys_assume_worst
  • Sort Method: quicksort Memory: 25kB
5. 0.002 9,621.203 ↓ 0.0 0 1

Append (cost=1.54..2,648.00 rows=2 width=124) (actual time=9,621.203..9,621.203 rows=0 loops=1)

6. 0.001 7.998 ↓ 0.0 0 1

Nested Loop (cost=1.54..18.16 rows=1 width=99) (actual time=7.998..7.998 rows=0 loops=1)

  • Join Filter: (oo.id = ad.organisation_id)
7. 0.000 7.997 ↓ 0.0 0 1

Nested Loop (cost=1.25..17.48 rows=1 width=12) (actual time=7.997..7.997 rows=0 loops=1)

8. 0.007 7.997 ↓ 0.0 0 1

Nested Loop (cost=0.97..17.02 rows=1 width=8) (actual time=7.997..7.997 rows=0 loops=1)

9. 7.974 7.974 ↑ 1.0 1 1

Index Scan using cloudscans_hostname_by_hostname_vendorid on cloudscans_hostnames h (cost=0.69..8.71 rows=1 width=8) (actual time=7.973..7.974 rows=1 loops=1)

  • Index Cond: (hostname = 'abgabas11.tripod.com'::text)
10. 0.016 0.016 ↓ 0.0 0 1

Index Only Scan using unique_hostname_orgid on cloudscans_orgmap o (cost=0.29..8.30 rows=1 width=16) (actual time=0.016..0.016 rows=0 loops=1)

  • Index Cond: (hostname_id = h.id)
  • Heap Fetches: 0
11. 0.000 0.000 ↓ 0.0 0

Index Only Scan using organisations_enabled on organisations oo (cost=0.28..0.45 rows=1 width=4) (never executed)

  • Index Cond: ((active = true) AND (id = o.org_id))
  • Filter: active
  • Heap Fetches: 0
12. 0.000 0.000 ↓ 0.0 0

Index Scan using notifications_definitions_org_type on notifications_definitions ad (cost=0.29..0.66 rows=1 width=99) (never executed)

  • Index Cond: ((organisation_id = o.org_id) AND (notification_type_id = 5) AND (enabled = true) AND (deleted_at IS NULL))
  • Filter: enabled
13. 0.001 9,613.203 ↓ 0.0 0 1

Nested Loop (cost=2,454.02..2,629.82 rows=1 width=99) (actual time=9,613.203..9,613.203 rows=0 loops=1)

14.          

CTE host_vendor

15. 0.065 0.065 ↑ 1.0 1 1

Index Only Scan using cloudscans_hostname_by_hostname_vendorid on cloudscans_hostnames (cost=0.69..8.71 rows=1 width=8) (actual time=0.061..0.065 rows=1 loops=1)

  • Index Cond: (hostname = 'abgabas11.tripod.com'::text)
  • Heap Fetches: 1
16. 109.547 9,613.202 ↓ 0.0 0 1

Hash Join (cost=2,445.03..2,619.04 rows=1 width=4) (actual time=9,613.202..9,613.202 rows=0 loops=1)

  • Hash Cond: (o_1.primary_hostname = h_1.hostname)
17. 29.740 29.740 ↓ 1.0 916 1

Seq Scan on organisations o_1 (cost=0.00..170.57 rows=915 width=17) (actual time=0.048..29.740 rows=916 loops=1)

  • Filter: active
  • Rows Removed by Filter: 2,042
18. 259.586 9,473.915 ↓ 152.7 93,757 1

Hash (cost=2,437.36..2,437.36 rows=614 width=34) (actual time=9,473.915..9,473.915 rows=93,757 loops=1)

  • Buckets: 65,536 (originally 1024) Batches: 2 (originally 1) Memory Usage: 3,585kB
19. 131.997 9,214.329 ↓ 152.7 93,757 1

Nested Loop (cost=29.32..2,437.36 rows=614 width=34) (actual time=274.189..9,214.329 rows=93,757 loops=1)

20. 0.069 0.069 ↑ 1.0 1 1

CTE Scan on host_vendor (cost=0.00..0.02 rows=1 width=8) (actual time=0.064..0.069 rows=1 loops=1)

21. 8,901.408 9,082.263 ↓ 152.7 93,757 1

Bitmap Heap Scan on cloudscans_hostnames h_1 (cost=29.32..2,431.20 rows=614 width=42) (actual time=274.109..9,082.263 rows=93,757 loops=1)

  • Recheck Cond: (datastore_vendor_id = host_vendor.datastore_vendor_id)
  • Rows Removed by Index Recheck: 2,049,707
  • Heap Blocks: exact=40,148 lossy=27,130
22. 180.855 180.855 ↓ 165.8 101,829 1

Bitmap Index Scan on cloudscans_hostnames_vendorid (cost=0.00..29.17 rows=614 width=0) (actual time=180.855..180.855 rows=101,829 loops=1)

  • Index Cond: (datastore_vendor_id = host_vendor.datastore_vendor_id)
23. 0.000 0.000 ↓ 0.0 0

Index Scan using notifications_definitions_org_type on notifications_definitions ad_1 (cost=0.29..2.07 rows=1 width=99) (never executed)

  • Index Cond: ((organisation_id = o_1.id) AND (notification_type_id = 5) AND (enabled = true) AND (deleted_at IS NULL))
  • Filter: enabled
Planning time : 2.431 ms
Execution time : 9,621.526 ms