explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GNAE : Optimization for: plan #YolR

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 219.348 1,734.631 ↑ 13.3 21,139 1

HashSetOp Except (cost=3,332.35..721,237.47 rows=280,682 width=12) (actual time=1,713.054..1,734.631 rows=21,139 loops=1)

2. 52.465 1,515.283 ↓ 1.0 562,199 1

Append (cost=3,332.35..719,839.60 rows=559,147 width=12) (actual time=22.472..1,515.283 rows=562,199 loops=1)

3. 32.005 164.172 ↓ 1.0 291,669 1

Subquery Scan on *SELECT* 1 (cost=3,332.35..337,805.52 rows=280,682 width=12) (actual time=22.471..164.172 rows=291,669 loops=1)

4. 111.469 132.167 ↓ 1.0 291,669 1

Bitmap Heap Scan on audit_evs_all aea (cost=3,332.35..334,998.70 rows=280,682 width=8) (actual time=22.468..132.167 rows=291,669 loops=1)

  • Recheck Cond: (when_created <= (now() - '30 days'::interval))
  • Heap Blocks: exact=11,675
5. 20.698 20.698 ↓ 1.0 291,669 1

Bitmap Index Scan on _idx_audit_evs_all_when_created_ev_id_is_null_original (cost=0.00..3,262.18 rows=280,682 width=0) (actual time=20.698..20.698 rows=291,669 loops=1)

  • Index Cond: (when_created <= (now() - '30 days'::interval))
6. 26.473 1,298.646 ↑ 1.0 270,530 1

Subquery Scan on *SELECT* 2 (cost=46,235.40..379,238.35 rows=278,465 width=12) (actual time=569.346..1,298.646 rows=270,530 loops=1)

7. 82.546 1,272.173 ↑ 1.0 270,530 1

Hash Join (cost=46,235.40..376,453.70 rows=278,465 width=8) (actual time=569.344..1,272.173 rows=270,530 loops=1)

  • Hash Cond: (aea_1.ev_id = evs.ev_id)
8. 695.695 890.836 ↓ 1.0 290,051 1

Bitmap Heap Scan on audit_evs_all aea_1 (cost=3,855.72..333,337.22 rows=280,682 width=16) (actual time=268.474..890.836 rows=290,051 loops=1)

  • Recheck Cond: ((when_created <= (now() - '30 days'::interval)) AND (ev_id IS NOT NULL))
  • Heap Blocks: exact=11,671
9. 195.141 195.141 ↓ 9.2 2,551,257 1

Bitmap Index Scan on idx_audit_evs_all_when_created_ev_id_is_null (cost=0.00..3,785.55 rows=278,464 width=0) (actual time=195.141..195.141 rows=2,551,257 loops=1)

  • Index Cond: (when_created <= (now() - '30 days'::interval))
10. 155.128 298.791 ↑ 1.0 583,815 1

Hash (cost=35,054.86..35,054.86 rows=585,986 width=8) (actual time=298.791..298.791 rows=583,815 loops=1)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 30,998kB
11. 143.663 143.663 ↑ 1.0 583,815 1

Seq Scan on evs (cost=0.00..35,054.86 rows=585,986 width=8) (actual time=0.011..143.663 rows=583,815 loops=1)

Planning time : 1.125 ms
Execution time : 1,739.460 ms