explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IzgL

Settings
# exclusive inclusive rows x rows loops node
1. 0.013 306,367.214 ↓ 15.0 30 1

Limit (cost=64,358.16..64,358.16 rows=2 width=145) (actual time=306,367.196..306,367.214 rows=30 loops=1)

2. 56.365 306,367.201 ↓ 15.0 30 1

Sort (cost=64,358.16..64,358.16 rows=2 width=145) (actual time=306,367.195..306,367.201 rows=30 loops=1)

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

GroupAggregate (cost=64,358.07..64,358.15 rows=2 width=145) (actual time=305,218.076..306,310.836 rows=165,169 loops=1)

  • Group Key: acc.id, oa.ignored
4. 1,994.818 305,345.737 ↓ 223,866.0 447,732 1

Sort (cost=64,358.07..64,358.07 rows=2 width=49) (actual time=305,218.028..305,345.737 rows=447,732 loops=1)

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

Nested Loop Left Join (cost=581.38..64,358.06 rows=2 width=49) (actual time=21.445..303,350.919 rows=447,732 loops=1)

  • Join Filter: ((oed.organisation_id = oab.organisation_id) AND (ab.breach_id = oab.breach_id) AND (ab.account_id = oab.account_id))
  • Rows Removed by Join Filter: 949,638,774
6. 821.577 11,141.792 ↓ 223,866.0 447,732 1

Nested Loop (cost=581.38..64,202.41 rows=2 width=68) (actual time=19.304..11,141.792 rows=447,732 loops=1)

7. 444.185 7,186.091 ↓ 223,866.0 447,732 1

Nested Loop Left Join (cost=581.10..64,201.80 rows=2 width=54) (actual time=19.294..7,186.091 rows=447,732 loops=1)

  • Join Filter: (oed.organisation_id = ob.organisation_id)
  • Filter: ((ob.archived IS NULL) OR (NOT ob.archived))
  • Rows Removed by Filter: 1,323
8. 604.869 4,945.686 ↓ 5,410.3 449,055 1

Hash Left Join (cost=580.95..64,186.86 rows=83 width=54) (actual time=19.286..4,945.686 rows=449,055 loops=1)

  • Hash Cond: ((oed.organisation_id = oa.organisation_id) AND (ab.account_id = oa.account_id))
  • Filter: ((oa.ignored IS NULL) OR (NOT oa.ignored))
  • Rows Removed by Filter: 105
9. 446.423 4,340.766 ↓ 46.2 449,160 1

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

10. 106.709 256.555 ↓ 33.0 165,354 1

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

11. 0.013 0.013 ↑ 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.011..0.013 rows=1 loops=1)

  • Index Cond: (organisation_id = 231)
  • Heap Fetches: 1
12. 130.941 149.833 ↓ 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=19.183..149.833 rows=165,354 loops=1)

  • Recheck Cond: (domain = oed.domain)
  • Heap Blocks: exact=1,362
13. 18.892 18.892 ↓ 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=18.892..18.892 rows=165,354 loops=1)

  • Index Cond: (domain = oed.domain)
14. 3,637.788 3,637.788 ↑ 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.013..0.022 rows=3 loops=165,354)

  • Index Cond: (account_id = acc.id)
15. 0.004 0.051 ↑ 1.2 4 1

Hash (cost=3.95..3.95 rows=5 width=9) (actual time=0.051..0.051 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
16. 0.047 0.047 ↑ 1.2 4 1

Seq Scan on hibp_organisation_accounts oa (cost=0.00..3.95 rows=5 width=9) (actual time=0.022..0.047 rows=4 loops=1)

  • Filter: (organisation_id = 231)
  • Rows Removed by Filter: 154
17. 1,796.220 1,796.220 ↓ 0.0 0 449,055

Index Scan using hibp_organisation_breaches_pkey on hibp_organisation_breaches ob (cost=0.15..0.17 rows=1 width=9) (actual time=0.004..0.004 rows=0 loops=449,055)

  • Index Cond: ((organisation_id = 231) AND (ab.breach_id = breach_id))
18. 3,134.124 3,134.124 ↑ 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.006..0.007 rows=1 loops=447,732)

  • Index Cond: (id = ab.breach_id)
19. 80,143.045 80,144.028 ↓ 1.0 2,121 447,732

Materialize (cost=0.00..77.09 rows=2,095 width=17) (actual time=0.000..0.179 rows=2,121 loops=447,732)

20. 0.983 0.983 ↓ 1.0 2,121 1

Seq Scan on hibp_organisation_account_breaches oab (cost=0.00..66.61 rows=2,095 width=17) (actual time=0.009..0.983 rows=2,121 loops=1)

  • Filter: (organisation_id = 231)
  • Rows Removed by Filter: 968
Planning time : 4.948 ms
Execution time : 306,370.665 ms