explain.depesz.com

PostgreSQL's explain analyze made readable

Result: t1qK

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 8,046.983 ↓ 15.0 30 1

Limit (cost=64,504.09..64,504.09 rows=2 width=145) (actual time=8,046.969..8,046.983 rows=30 loops=1)

2. 81.921 8,046.973 ↓ 15.0 30 1

Sort (cost=64,504.09..64,504.09 rows=2 width=145) (actual time=8,046.968..8,046.973 rows=30 loops=1)

  • Sort Key: (count(br.id)) DESC, acc.name, acc.domain
  • Sort Method: top-N heapsort Memory: 33kB
3. 1,073.822 7,965.052 ↓ 82,584.5 165,169 1

GroupAggregate (cost=64,504.00..64,504.08 rows=2 width=145) (actual time=6,784.097..7,965.052 rows=165,169 loops=1)

  • Group Key: acc.id, oa.ignored
4. 899.627 6,891.230 ↓ 223,866.0 447,732 1

Sort (cost=64,504.00..64,504.00 rows=2 width=49) (actual time=6,784.054..6,891.230 rows=447,732 loops=1)

  • Sort Key: acc.id, oa.ignored
  • Sort Method: external sort Disk: 24,816kB
5. 312.782 5,991.603 ↓ 223,866.0 447,732 1

Nested Loop Left Join (cost=581.79..64,503.99 rows=2 width=49) (actual time=15.334..5,991.603 rows=447,732 loops=1)

  • Join Filter: (oed.organisation_id = oab.organisation_id)
6. 142.882 4,783.357 ↓ 223,866.0 447,732 1

Nested Loop (cost=581.51..64,503.36 rows=2 width=68) (actual time=15.326..4,783.357 rows=447,732 loops=1)

7. 413.815 3,297.279 ↓ 223,866.0 447,732 1

Nested Loop Left Join (cost=581.24..64,502.75 rows=2 width=54) (actual time=15.316..3,297.279 rows=447,732 loops=1)

  • Join Filter: (oed.organisation_id = oa.organisation_id)
  • Filter: ((oa.ignored IS NULL) OR (NOT oa.ignored))
  • Rows Removed by Filter: 104
8. 856.773 2,435.628 ↓ 1,763.1 447,836 1

Nested Loop Left Join (cost=581.09..64,457.67 rows=254 width=53) (actual time=15.307..2,435.628 rows=447,836 loops=1)

  • Join Filter: ((oed.organisation_id = ob.organisation_id) AND (ab.breach_id = ob.breach_id))
  • Rows Removed by Join Filter: 1,795,316
  • Filter: ((ob.archived IS NULL) OR (NOT ob.archived))
  • Rows Removed by Filter: 1,324
9. 143.734 1,578.855 ↓ 46.2 449,160 1

Nested Loop (cost=576.93..64,109.86 rows=9,729 width=53) (actual time=15.275..1,578.855 rows=449,160 loops=1)

10. 53.374 112.289 ↓ 33.0 165,354 1

Nested Loop (cost=576.36..60,483.42 rows=5,006 width=37) (actual time=15.254..112.289 rows=165,354 loops=1)

11. 0.014 0.014 ↑ 1.0 1 1

Index Only Scan using unique_domain_for_org_2 on organisation_email_domains oed (cost=0.28..4.29 rows=1 width=26) (actual time=0.012..0.014 rows=1 loops=1)

  • Index Cond: (organisation_id = 231)
  • Heap Fetches: 1
12. 43.960 58.901 ↓ 7.6 165,354 1

Bitmap Heap Scan on hibp_exposed_accounts acc (cost=576.09..60,262.97 rows=21,616 width=29) (actual time=15.237..58.901 rows=165,354 loops=1)

  • Recheck Cond: (domain = oed.domain)
  • Heap Blocks: exact=1,362
13. 14.941 14.941 ↓ 7.6 165,354 1

Bitmap Index Scan on hibp_exposed_accounts_domain_idx (cost=0.00..570.68 rows=21,616 width=0) (actual time=14.941..14.941 rows=165,354 loops=1)

  • Index Cond: (domain = oed.domain)
14. 1,322.832 1,322.832 ↑ 1.0 3 165,354

Index Scan using hibp_account_breaches_accid on hibp_account_breaches ab (cost=0.56..0.69 rows=3 width=16) (actual time=0.005..0.008 rows=3 loops=165,354)

  • Index Cond: (account_id = acc.id)
15. 0.000 0.000 ↓ 2.0 4 449,160

Materialize (cost=4.16..7.30 rows=2 width=9) (actual time=0.000..0.000 rows=4 loops=449,160)

16. 0.007 0.015 ↓ 2.0 4 1

Bitmap Heap Scan on hibp_organisation_breaches ob (cost=4.16..7.29 rows=2 width=9) (actual time=0.012..0.015 rows=4 loops=1)

  • Recheck Cond: (organisation_id = 231)
  • Heap Blocks: exact=2
17. 0.008 0.008 ↓ 2.0 4 1

Bitmap Index Scan on hibp_organisation_breaches_pkey (cost=0.00..4.16 rows=2 width=0) (actual time=0.008..0.008 rows=4 loops=1)

  • Index Cond: (organisation_id = 231)
18. 447.836 447.836 ↓ 0.0 0 447,836

Index Scan using hibp_organisation_accounts_account_id_organisation_id_key on hibp_organisation_accounts oa (cost=0.14..0.17 rows=1 width=9) (actual time=0.001..0.001 rows=0 loops=447,836)

  • Index Cond: ((ab.account_id = account_id) AND (organisation_id = 231))
19. 1,343.196 1,343.196 ↑ 1.0 1 447,732

Index Scan using hibp_breaches_pkey on hibp_breaches br (cost=0.28..0.29 rows=1 width=14) (actual time=0.002..0.003 rows=1 loops=447,732)

  • Index Cond: (id = ab.breach_id)
20. 895.464 895.464 ↓ 0.0 0 447,732

Index Scan using hibp_organisation_account_bre_account_id_breach_id_organisa_key on hibp_organisation_account_breaches oab (cost=0.28..0.30 rows=1 width=17) (actual time=0.002..0.002 rows=0 loops=447,732)

  • Index Cond: ((ab.account_id = account_id) AND (ab.breach_id = breach_id) AND (organisation_id = 231))
Planning time : 3.327 ms
Execution time : 8,054.765 ms