explain.depesz.com

PostgreSQL's explain analyze made readable

Result: k6GC

Settings
# exclusive inclusive rows x rows loops node
1. 22.954 120,416.997 ↑ 1.0 1 1

Aggregate (cost=11,924,464.27..11,924,464.28 rows=1 width=8) (actual time=120,416.997..120,416.997 rows=1 loops=1)

  • Buffers: shared hit=4371142 read=2391220, temp read=17240 written=66160
2. 129.792 120,394.043 ↑ 1.0 10 1

Merge Semi Join (cost=11,755,516.37..11,924,464.24 rows=10 width=0) (actual time=120,252.761..120,394.043 rows=10 loops=1)

  • Merge Cond: (aa.ctid = "ANY_subquery".ctid)
  • Buffers: shared hit=4371142 read=2391220, temp read=17240 written=66160
3. 19,027.482 120,254.582 ↑ 29.1 1,162,277 1

Sort (cost=11,753,815.09..11,838,288.95 rows=33,789,544 width=6) (actual time=120,060.365..120,254.582 rows=1,162,277 loops=1)

  • Sort Key: aa.ctid
  • Sort Method: external merge Disk: 529136kB
  • Buffers: shared hit=4370334 read=2391220, temp read=17240 written=66160
4. 101,227.100 101,227.100 ↑ 1.0 33,789,544 1

Seq Scan on account_audit aa (cost=0.00..7,099,449.44 rows=33,789,544 width=6) (actual time=0.046..101,227.100 rows=33,789,544 loops=1)

  • Buffers: shared hit=4370334 read=2391220
5. 8.184 9.669 ↑ 1.0 10 1

Sort (cost=1,701.28..1,701.31 rows=10 width=6) (actual time=9.664..9.669 rows=10 loops=1)

  • Sort Key: "ANY_subquery".ctid
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=808
6. 0.005 1.485 ↑ 1.0 10 1

Subquery Scan on ANY_subquery (cost=1.13..1,701.12 rows=10 width=6) (actual time=0.240..1.485 rows=10 loops=1)

  • Buffers: shared hit=808
7. 0.011 1.480 ↑ 1.0 10 1

Limit (cost=1.13..1,701.02 rows=10 width=14) (actual time=0.236..1.480 rows=10 loops=1)

  • Buffers: shared hit=808
8. 1.469 1.469 ↑ 50,674.0 10 1

Nested Loop Semi Join (cost=1.13..86,139,999.99 rows=506,740 width=14) (actual time=0.233..1.469 rows=10 loops=1)

  • Buffers: shared hit=808
  • -> Index Scan using idx_account_audit_account_id on account_audit audit (cost=0.56..85814357.84 rows=506740 width=14) (actual time=0.205..1.327 rows=1
  • Filter: (((operation)::text = 'ensureAuthenticationWithUsernameAndPasswordAllowed'::text) AND (operation_date > (CURRENT_DATE - 1825)) AND (operat
  • Rows Removed by Filter: 852
  • Buffers: shared hit=647
  • -> Index Scan using idx_account_audit_account_id on account_audit auditsecond (cost=0.56..1989.95 rows=328 width=8) (actual time=0.008..0.008 rows=1 l
  • Index Cond: (account_id = audit.account_id)
  • Filter: (((operation)::text = 'ensureAuthenticationWithUsernameAndPasswordAllowed'::text) AND (operation_date > (CURRENT_DATE - 365)))
  • Rows Removed by Filter: 6
  • Buffers: shared hit=161