explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qIy3 : In house explain

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 3,848.557 ↓ 0.0 0 1

Limit (cost=23.99..1,585.14 rows=500 width=40) (actual time=3,848.557..3,848.557 rows=0 loops=1)

2. 0.051 3,848.556 ↓ 0.0 0 1

Hash Anti Join (cost=23.99..670,356.29 rows=214,692 width=40) (actual time=3,848.556..3,848.556 rows=0 loops=1)

  • Hash Cond: (r.requestid = pbr.requestid)
3. 37.528 3,848.488 ↑ 107,346.5 2 1

Nested Loop Anti Join (cost=22.68..665,497.57 rows=214,693 width=24) (actual time=77.469..3,848.488 rows=2 loops=1)

4. 1,284.826 3,025.430 ↑ 4.0 78,553 1

Hash Join (cost=22.24..479,113.60 rows=311,904 width=24) (actual time=0.308..3,025.430 rows=78,553 loops=1)

  • Hash Cond: ((r.requestersymbol)::text = (p.institutionsymbol)::text)
  • Join Filter: ((date_part('epoch'::text, age((('now'::cstring)::date)::timestamp without time zone, r.closeddate)) / '2628000'::double precision) > (p.patrondataretentionmonths)::double precision)
  • Rows Removed by Join Filter: 787369
5. 1,740.366 1,740.366 ↓ 1.0 4,909,456 1

Seq Scan on request r (cost=0.00..432,741.54 rows=4,874,254 width=20) (actual time=0.014..1,740.366 rows=4,909,456 loops=1)

6. 0.100 0.238 ↑ 1.2 608 1

Hash (cost=13.22..13.22 rows=722 width=8) (actual time=0.238..0.238 rows=608 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 32kB
7. 0.138 0.138 ↑ 1.2 608 1

Seq Scan on patronsettings p (cost=0.00..13.22 rows=722 width=8) (actual time=0.015..0.138 rows=608 loops=1)

8. 785.530 785.530 ↑ 2.0 1 78,553

Index Scan using event_requestid_idx on event e (cost=0.43..0.72 rows=2 width=8) (actual time=0.010..0.010 rows=1 loops=78,553)

  • Index Cond: (requestid = r.requestid)
  • Filter: ((eventtype)::text = 'PATRON_DATA_PURGE'::text)
  • Rows Removed by Filter: 1
9. 0.006 0.017 ↑ 1.0 14 1

Hash (cost=1.14..1.14 rows=14 width=8) (actual time=0.017..0.017 rows=14 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 0.011 0.011 ↑ 1.0 14 1

Seq Scan on patronpurgeblacklistedrequest pbr (cost=0.00..1.14 rows=14 width=8) (actual time=0.010..0.011 rows=14 loops=1)