#	exclusive	inclusive	rows x	node
1.	0.000	0.000	↓ 0.0	→ Unique (cost=6,202,409,704.02..6,208,154,170.38 rows=3,216,840 width=33) (actual rows= loops=)
2.	0.000	0.000	↓ 0.0	→ Sort (cost=6,202,409,704.02..6,204,324,526.14 rows=765,928,847 width=33) (actual rows= loops=) Sort Key: u.email_address, ou.organisation_id
3.	0.000	0.000	↓ 0.0	→ Merge Join (cost=102,883,281.18..6,005,613,331.02 rows=765,928,847 width=33) (actual rows= loops=) Merge Cond: (o.id = ou.organisation_id)
4.	0.000	0.000	↓ 0.0	→ Nested Loop (cost=112.80..6,030,091,378.51 rows=355,268,742 width=12) (actual rows= loops=) Join Filter: (SubPlan 1)
5.	0.000	0.000	↓ 0.0	→ Merge Join (cost=111.96..168.46 rows=372 width=26) (actual rows= loops=) Merge Cond: (d.organisation_id = o.id)
6.	0.000	0.000	↓ 0.0	→ Index Only Scan using unique_domain_for_org_2 on organisation_email_domains d (cost=0.28..47.59 rows=1,021 width=22) (actual rows= loops=)
7.	0.000	0.000	↓ 0.0	→ Sort (cost=111.64..113.15 rows=606 width=4) (actual rows= loops=) Sort Key: o.id
8.	0.000	0.000	↓ 0.0	→ Seq Scan on organisations o (cost=0.00..83.63 rows=606 width=4) (actual rows= loops=) Filter: active
9.	0.000	0.000	↓ 0.0	→ Materialize (cost=0.84..193,375.14 rows=1,910,047 width=8) (actual rows= loops=)
10.	0.000	0.000	↓ 0.0	→ Nested Loop (cost=0.84..176,362.90 rows=1,910,047 width=8) (actual rows= loops=)
11.	0.000	0.000	↓ 0.0	→ Index Only Scan using hibp_breaches_on_id_occurred on hibp_breaches b (cost=0.28..8.29 rows=1 width=4) (actual rows= loops=) Index Cond: (id = 1950)
12.	0.000	0.000	↓ 0.0	→ Index Only Scan using hibp_account_breaches_breach_id_account_id_key on hibp_account_breaches ab (cost=0.56..157,254.14 rows=1,910,047 width=16) (actual rows= loops=) Index Cond: (breach_id = 1950)
13.				SubPlan (for Nested Loop)
14.	0.000	0.000	↓ 0.0	→ Unique (cost=8.46..8.47 rows=1 width=11) (actual rows= loops=)
15.	0.000	0.000	↓ 0.0	→ Sort (cost=8.46..8.47 rows=1 width=11) (actual rows= loops=) Sort Key: hibp_exposed_accounts.domain
16.	0.000	0.000	↓ 0.0	→ Index Scan using hibp_exposed_accounts_pkey on hibp_exposed_accounts (cost=0.43..8.45 rows=1 width=11) (actual rows= loops=) Index Cond: (id = ab.account_id)
17.	0.000	0.000	↓ 0.0	→ Sort (cost=1,750.63..1,759.59 rows=3,585 width=33) (actual rows= loops=) Sort Key: ou.organisation_id
18.	0.000	0.000	↓ 0.0	→ Hash Join (cost=1,402.83..1,538.98 rows=3,585 width=33) (actual rows= loops=) Hash Cond: (ou.user_id = u.id)
19.	0.000	0.000	↓ 0.0	→ Seq Scan on organisation_users ou (cost=0.00..86.85 rows=3,585 width=8) (actual rows= loops=)
20.	0.000	0.000	↓ 0.0	→ Hash (cost=1,372.37..1,372.37 rows=2,437 width=29) (actual rows= loops=)
21.	0.000	0.000	↓ 0.0	→ Seq Scan on users u (cost=0.00..1,372.37 rows=2,437 width=29) (actual rows= loops=)

                                                                                     QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=6202409704.02..6208154170.38 rows=3216840 width=33)
   ->  Sort  (cost=6202409704.02..6204324526.14 rows=765928847 width=33)
         Sort Key: u.email_address, ou.organisation_id
         ->  Merge Join  (cost=102883281.18..6005613331.02 rows=765928847 width=33)
               Merge Cond: (o.id = ou.organisation_id)
               ->  Nested Loop  (cost=112.80..6030091378.51 rows=355268742 width=12)
                     Join Filter: (SubPlan 1)
                     ->  Merge Join  (cost=111.96..168.46 rows=372 width=26)
                           Merge Cond: (d.organisation_id = o.id)
                           ->  Index Only Scan using unique_domain_for_org_2 on organisation_email_domains d  (cost=0.28..47.59 rows=1021 width=22)
                           ->  Sort  (cost=111.64..113.15 rows=606 width=4)
                                 Sort Key: o.id
                                 ->  Seq Scan on organisations o  (cost=0.00..83.63 rows=606 width=4)
                                       Filter: active
                     ->  Materialize  (cost=0.84..193375.14 rows=1910047 width=8)
                           ->  Nested Loop  (cost=0.84..176362.90 rows=1910047 width=8)
                                 ->  Index Only Scan using hibp_breaches_on_id_occurred on hibp_breaches b  (cost=0.28..8.29 rows=1 width=4)
                                       Index Cond: (id = 1950)
                                 ->  Index Only Scan using hibp_account_breaches_breach_id_account_id_key on hibp_account_breaches ab  (cost=0.56..157254.14 rows=1910047 width=16)
                                       Index Cond: (breach_id = 1950)
                     SubPlan 1
                       ->  Unique  (cost=8.46..8.47 rows=1 width=11)
                             ->  Sort  (cost=8.46..8.47 rows=1 width=11)
                                   Sort Key: hibp_exposed_accounts.domain
                                   ->  Index Scan using hibp_exposed_accounts_pkey on hibp_exposed_accounts  (cost=0.43..8.45 rows=1 width=11)
                                         Index Cond: (id = ab.account_id)
               ->  Sort  (cost=1750.63..1759.59 rows=3585 width=33)
                     Sort Key: ou.organisation_id
                     ->  Hash Join  (cost=1402.83..1538.98 rows=3585 width=33)
                           Hash Cond: (ou.user_id = u.id)
                           ->  Seq Scan on organisation_users ou  (cost=0.00..86.85 rows=3585 width=8)
                           ->  Hash  (cost=1372.37..1372.37 rows=2437 width=29)
                                 ->  Seq Scan on users u  (cost=0.00..1372.37 rows=2437 width=29)
(33 rows)

Per node type stats

node type	count	sum of times	% of query
Hash	1	0.000 ms	0.0 %
Hash Join	1	0.000 ms	0.0 %
Index Only Scan	3	0.000 ms	0.0 %
Index Scan	1	0.000 ms	0.0 %
Materialize	1	0.000 ms	0.0 %
Merge Join	2	0.000 ms	0.0 %
Nested Loop	2	0.000 ms	0.0 %
Seq Scan	3	0.000 ms	0.0 %
Sort	4	0.000 ms	0.0 %
Unique	2	0.000 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
hibp_account_breaches	1	0.000 ms	0.0 %
Index Only Scan	1	0.000 ms	0.0 %
hibp_breaches	1	0.000 ms	0.0 %
Index Only Scan	1	0.000 ms	0.0 %
hibp_exposed_accounts	1	0.000 ms	0.0 %
Index Scan	1	0.000 ms	0.0 %
organisation_email_domains	1	0.000 ms	0.0 %
Index Only Scan	1	0.000 ms	0.0 %
organisation_users	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %
organisations	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %
users	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sX1l

Color mode:

Visible columns:

Per node type stats

Per table stats