explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qhH5

Settings
# exclusive inclusive rows x rows loops node
1. 0.060 320,390.257 ↑ 1.0 1 1

Aggregate (cost=11,922,922.01..11,922,922.02 rows=1 width=8) (actual time=320,390.257..320,390.257 rows=1 loops=1)

  • Buffers: shared hit=8662669 read=5954704, temp read=16378 written=65626
2. 0.022 320,390.197 ↓ 0.0 0 1

Merge Semi Join (cost=11,753,974.12..11,922,921.99 rows=10 width=0) (actual time=320,390.196..320,390.197 rows=0 loops=1)

  • Merge Cond: (aa.ctid = audit.ctid)
  • Buffers: shared hit=8662669 read=5954704, temp read=16378 written=65626
3. 12,443.137 136,280.639 ↑ 33,789,544.0 1 1

Sort (cost=11,753,815.09..11,838,288.95 rows=33,789,544 width=6) (actual time=136,280.639..136,280.639 rows=1 loops=1)

  • Sort Key: aa.ctid
  • Sort Method: external merge Disk: 524864kB
  • Buffers: shared hit=4375932 read=2385626, temp read=16378 written=65626
4. 123,837.502 123,837.502 ↑ 1.0 33,516,404 1

Seq Scan on account_audit aa (cost=0.00..7,099,449.44 rows=33,789,544 width=6) (actual time=0.035..123,837.502 rows=33,516,404 loops=1)

  • Buffers: shared hit=4375929 read=2385625
5. 8.153 184,109.536 ↓ 0.0 0 1

Sort (cost=159.03..159.05 rows=10 width=6) (actual time=184,109.536..184,109.536 rows=0 loops=1)

  • Sort Key: audit.ctid
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=4286737 read=3569078
6. 23.419 184,101.383 ↓ 0.0 0 1

Limit (cost=0.56..158.76 rows=10 width=6) (actual time=184,101.383..184,101.383 rows=0 loops=1)

  • Buffers: shared hit=4286737 read=3569078
7. 37,227.995 184,077.964 ↓ 0.0 0 1

Nested Loop Semi Join (cost=0.56..8,016,408.61 rows=506,740 width=6) (actual time=184,077.963..184,077.964 rows=0 loops=1)

  • Buffers: shared hit=4286737 read=3569078
  • -> Index Scan using idx_account_audit_account_id on account_audit auditsecond (cost=0.56..1989.95 rows=328 width=8) (actual time=0.318..0.318 rows=0 loops=1
8. 146,849.969 146,849.969 ↑ 4.3 116,664 1

Seq Scan on account_audit audit (cost=0.00..7,690,766.46 rows=506,740 width=14) (actual time=63,740.767..146,849.969 rows=116,664 loops=1)

  • Filter: (((operation)::text = 'ensureAuthenticationWithUsernameAndPasswordAllowed'::text) AND (operation_date > (CURRENT_DATE - 1825)) AND (operation_da
  • Rows Removed by Filter: 33399740
  • Buffers: shared hit=3220099 read=3541455
  • Index Cond: (account_id = audit.account_id)
  • Filter: (((operation)::text = 'ensureAuthenticationWithUsernameAndPasswordAllowed'::text) AND (operation_date > (CURRENT_DATE - 365)))
  • Rows Removed by Filter: 7
  • Buffers: shared hit=1066638 read=27623
Planning time : 119.666 ms
Execution time : 320,476.507 ms