explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FUWs

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.006 137,843.992 ↑ 181.0 1 1

Unique (cost=4,105.20..4,106.11 rows=181 width=292) (actual time=137,843.985..137,843.992 rows=1 loops=1)

2. 0.086 137,843.986 ↑ 11.3 16 1

Sort (cost=4,105.20..4,105.66 rows=181 width=292) (actual time=137,843.984..137,843.986 rows=16 loops=1)

  • Sort Key: a.id
  • Sort Method: quicksort Memory: 29kB
3. 1,614.160 137,843.900 ↑ 11.3 16 1

Hash Semi Join (cost=42.55..4,098.42 rows=181 width=292) (actual time=3,115.232..137,843.900 rows=16 loops=1)

  • Hash Cond: (acc.domain = organisation_email_domains.domain)
4. 3,788.437 136,229.554 ↓ 270.1 3,172,859 1

Merge Join (cost=38.09..4,059.98 rows=11,746 width=287) (actual time=21.792..136,229.554 rows=3,172,859 loops=1)

  • Merge Cond: (a.entity_id = b.id)
5. 4,075.928 130,209.120 ↓ 396,607.4 3,172,859 1

Nested Loop (cost=2.12..31,294.47 rows=8 width=279) (actual time=14.405..130,209.120 rows=3,172,859 loops=1)

6. 1,520.918 69,021.730 ↓ 396,607.4 3,172,859 1

Nested Loop (cost=1.68..31,272.87 rows=8 width=276) (actual time=12.302..69,021.730 rows=3,172,859 loops=1)

7. 0.543 108.652 ↓ 110.0 110 1

Nested Loop Left Join (cost=1.12..5,592.12 rows=1 width=260) (actual time=10.646..108.652 rows=110 loops=1)

  • Filter: (dis.read_at IS NULL)
8. 14.829 14.829 ↑ 4.0 110 1

Index Scan using notifications_occurred_uuid on notifications a (cost=0.56..1,858.88 rows=436 width=260) (actual time=8.523..14.829 rows=110 loops=1)

  • Index Cond: ((uuid = '11-0000-0000-0000000000000000'::text) AND (occurred_at > (now() - '30 days'::interval)) AND (resolved = false))
  • Filter: (resolved IS FALSE)
9. 93.280 93.280 ↓ 0.0 0 110

Index Scan using notifications_read_by_user on notifications_users dis (cost=0.56..8.55 rows=1 width=16) (actual time=0.848..0.848 rows=0 loops=110)

  • Index Cond: ((a.id = notification_id) AND (user_id = 915) AND (org_id = 166))
10. 67,392.160 67,392.160 ↑ 4.2 28,844 110

Index Only Scan using hibp_account_breaches_breach_id_account_id_key on hibp_account_breaches ab (cost=0.56..24,481.90 rows=119,885 width=16) (actual time=0.030..612.656 rows=28,844 loops=110)

  • Index Cond: (breach_id = a.entity_id)
  • Heap Fetches: 3167536
11. 57,111.462 57,111.462 ↑ 1.0 1 3,172,859

Index Scan using hibp_exposed_accounts_pkey on hibp_exposed_accounts acc (cost=0.43..2.69 rows=1 width=15) (actual time=0.017..0.018 rows=1 loops=3,172,859)

  • Index Cond: (id = ab.account_id)
12. 2,231.997 2,231.997 ↓ 1,603.4 3,174,657 1

Index Scan using hibp_breaches_pkey on hibp_breaches b (cost=0.28..141.36 rows=1,980 width=20) (actual time=5.039..2,231.997 rows=3,174,657 loops=1)

13. 0.115 0.186 ↑ 3.0 2 1

Hash (cost=4.38..4.38 rows=6 width=14) (actual time=0.186..0.186 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.071 0.071 ↑ 3.0 2 1

Index Only Scan using unique_domain_for_org_2 on organisation_email_domains (cost=0.28..4.38 rows=6 width=14) (actual time=0.069..0.071 rows=2 loops=1)

  • Index Cond: (organisation_id = 166)
  • Heap Fetches: 0
Planning time : 21.402 ms
Execution time : 137,844.268 ms