explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MNyR

Settings
# exclusive inclusive rows x rows loops node
1. 179.339 87,173.577 ↓ 1.6 304 1

GroupAggregate (cost=3,226,811.99..3,227,182.33 rows=187 width=16) (actual time=86,662.990..87,173.577 rows=304 loops=1)

  • Group Key: ab.breach_id
2. 973.400 86,994.238 ↓ 18.7 919,817 1

Sort (cost=3,226,811.99..3,226,934.81 rows=49,129 width=12) (actual time=86,662.985..86,994.238 rows=919,817 loops=1)

  • Sort Key: ab.breach_id
  • Sort Method: external merge Disk: 19,712kB
3. 215.435 86,020.838 ↓ 18.7 919,817 1

Merge Left Join (cost=1,733,111.77..3,222,983.79 rows=49,129 width=12) (actual time=63,650.727..86,020.838 rows=919,817 loops=1)

  • Merge Cond: (ab.account_id = oa.account_id)
  • Join Filter: (dom.organisation_id = oa.organisation_id)
  • Filter: ((oa.ignored IS NULL) OR (NOT oa.ignored))
4. 4,619.868 85,805.393 ↑ 6.2 919,817 1

Merge Join (cost=1,733,111.63..3,208,600.48 rows=5,748,067 width=28) (actual time=63,650.714..85,805.393 rows=919,817 loops=1)

  • Merge Cond: (acc.id = ab.account_id)
5. 693.246 4,931.477 ↑ 4.9 612,654 1

Sort (cost=1,732,992.88..1,740,428.56 rows=2,974,273 width=12) (actual time=4,581.618..4,931.477 rows=612,654 loops=1)

  • Sort Key: acc.id
  • Sort Method: external merge Disk: 15,512kB
6. 142.553 4,238.231 ↑ 4.9 612,654 1

Nested Loop (cost=0.84..1,311,536.47 rows=2,974,273 width=12) (actual time=2.400..4,238.231 rows=612,654 loops=1)

7. 3.033 3.033 ↓ 1.5 1,165 1

Index Only Scan using unique_domain_for_org_2 on organisation_email_domains dom (cost=0.28..86.28 rows=800 width=26) (actual time=0.017..3.033 rows=1,165 loops=1)

  • Index Cond: (organisation_id = 1,834)
  • Heap Fetches: 1,165
8. 4,092.645 4,092.645 ↑ 40.4 526 1,165

Index Scan using hibp_exposed_accounts_domain_idx on hibp_exposed_accounts acc (cost=0.56..1,426.94 rows=21,237 width=16) (actual time=0.329..3.513 rows=526 loops=1,165)

  • Index Cond: (domain = dom.domain)
9. 76,254.048 76,254.048 ↓ 1.0 35,706,712 1

Index Only Scan using hibp_account_breaches_accbr_idx on hibp_account_breaches ab (cost=0.56..1,314,627.20 rows=35,706,699 width=16) (actual time=4.267..76,254.048 rows=35,706,712 loops=1)

  • Heap Fetches: 472,774
10. 0.010 0.010 ↓ 0.0 0 1

Index Scan using hibp_organisation_accounts_account_id_organisation_id_key on hibp_organisation_accounts oa (cost=0.14..13.13 rows=1 width=9) (actual time=0.009..0.010 rows=0 loops=1)

  • Index Cond: (organisation_id = 1,834)
Planning time : 1.779 ms
Execution time : 87,183.241 ms