explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BHFk : Optimization for: plan #FUWs

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.003 3.011 ↑ 1.0 1 1

Unique (cost=300,995.83..300,995.83 rows=1 width=292) (actual time=3.010..3.011 rows=1 loops=1)

2.          

CTE org_breaches

3. 0.043 1.885 ↑ 8,964.2 17 1

Unique (cost=290,666.36..291,809.29 rows=152,391 width=24) (actual time=1.818..1.885 rows=17 loops=1)

4. 0.116 1.842 ↑ 459.0 332 1

Sort (cost=290,666.36..291,047.34 rows=152,391 width=24) (actual time=1.817..1.842 rows=332 loops=1)

  • Sort Key: ab.breach_id, b.name
  • Sort Method: quicksort Memory: 42kB
5. 0.101 1.726 ↑ 459.0 332 1

Hash Join (cost=624.11..274,421.96 rows=152,391 width=24) (actual time=0.798..1.726 rows=332 loops=1)

  • Hash Cond: (ab.breach_id = b.id)
6. 0.069 0.935 ↑ 459.0 332 1

Nested Loop (cost=515.56..272,218.03 rows=152,391 width=8) (actual time=0.087..0.935 rows=332 loops=1)

7. 0.030 0.194 ↑ 350.6 224 1

Nested Loop (cost=515.12..225,346.44 rows=78,539 width=4) (actual time=0.073..0.194 rows=224 loops=1)

8. 0.028 0.028 ↑ 3.0 2 1

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

  • Index Cond: (organisation_id = 166)
  • Heap Fetches: 0
9. 0.072 0.136 ↑ 174.7 112 2

Bitmap Heap Scan on hibp_exposed_accounts acc (cost=514.85..37,361.37 rows=19,564 width=15) (actual time=0.038..0.068 rows=112 loops=2)

  • Recheck Cond: (domain = d.domain)
  • Heap Blocks: exact=15
10. 0.064 0.064 ↑ 174.7 112 2

Bitmap Index Scan on hibp_exposed_accounts_domain_idx (cost=0.00..509.96 rows=19,564 width=0) (actual time=0.032..0.032 rows=112 loops=2)

  • Index Cond: (domain = d.domain)
11. 0.672 0.672 ↑ 3.0 1 224

Index Scan using hibp_account_breaches_accid on hibp_account_breaches ab (cost=0.44..0.57 rows=3 width=16) (actual time=0.003..0.003 rows=1 loops=224)

  • Index Cond: (account_id = acc.id)
12. 0.270 0.690 ↓ 1.0 1,982 1

Hash (cost=83.80..83.80 rows=1,980 width=20) (actual time=0.690..0.690 rows=1,982 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 122kB
13. 0.420 0.420 ↓ 1.0 1,982 1

Seq Scan on hibp_breaches b (cost=0.00..83.80 rows=1,980 width=20) (actual time=0.006..0.420 rows=1,982 loops=1)

14. 0.006 3.008 ↑ 1.0 1 1

Sort (cost=9,186.54..9,186.54 rows=1 width=292) (actual time=3.008..3.008 rows=1 loops=1)

  • Sort Key: a.id
  • Sort Method: quicksort Memory: 25kB
15. 0.016 3.002 ↑ 1.0 1 1

Hash Join (cost=5,567.23..9,186.53 rows=1 width=292) (actual time=2.992..3.002 rows=1 loops=1)

  • Hash Cond: (org_breaches.breach_id = a.entity_id)
16. 1.890 1.890 ↑ 8,964.2 17 1

CTE Scan on org_breaches (cost=0.00..3,047.82 rows=152,391 width=40) (actual time=1.819..1.890 rows=17 loops=1)

17. 0.036 1.096 ↓ 110.0 110 1

Hash (cost=5,567.22..5,567.22 rows=1 width=260) (actual time=1.096..1.096 rows=110 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 33kB
18. 0.007 1.060 ↓ 110.0 110 1

Nested Loop Left Join (cost=1.12..5,567.22 rows=1 width=260) (actual time=0.061..1.060 rows=110 loops=1)

  • Filter: (dis.read_at IS NULL)
19. 0.503 0.503 ↑ 3.9 110 1

Index Scan using notifications_occurred_uuid on notifications a (cost=0.56..1,851.16 rows=434 width=260) (actual time=0.044..0.503 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)
20. 0.550 0.550 ↓ 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.005..0.005 rows=0 loops=110)

  • Index Cond: ((a.id = notification_id) AND (user_id = 915) AND (org_id = 166))
Planning time : 11.411 ms
Execution time : 3.123 ms