explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qrpK

Settings
# exclusive inclusive rows x rows loops node
1. 1.623 3,871.334 ↑ 1.5 1,128 1

Sort (cost=249,072.64..249,076.98 rows=1,737 width=55) (actual time=3,871.206..3,871.334 rows=1,128 loops=1)

  • Output: thread.id, thread.no, site.slug, (COALESCE(date_part('epoch'::text, (((thread.last_chk + (upd_period.upd_period)::interval))::time
  • Sort Key: (COALESCE(date_part('epoch'::text, (((thread.last_chk + (upd_period.upd_period)::interval))::timestamp with time zone - now())), '0'::double precision))
  • Sort Method: quicksort Memory: 137kB
2. 5.493 3,869.711 ↑ 1.5 1,128 1

Nested Loop Left Join (cost=248,102.96..248,979.17 rows=1,737 width=55) (actual time=3,816.454..3,869.711 rows=1,128 loops=1)

  • Output: thread.id, thread.no, site.slug, COALESCE(date_part('epoch'::text, (((thread.last_chk + (upd_period.upd_period)::interval))::timestamp with time zone - now())), '0'::double precision), board.slug
  • Join Filter: (((max((now() - post.post_ts))) >= (upd_period.min_age)::interval) AND ((max((now() - post.post_ts))) <= (upd_period.max_age)::interval))
  • Rows Removed by Join Filter: 10152
3. 11.677 3,863.090 ↑ 1.4 1,128 1

Hash Right Join (cost=248,102.96..248,600.31 rows=1,563 width=71) (actual time=3,816.352..3,863.090 rows=1,128 loops=1)

  • Output: thread.id, thread.no, thread.last_chk, (max((now() - post.post_ts))), board.slug, site.slug
  • Hash Cond: (post.thread_id = thread.id)
4. 2,523.230 3,846.864 ↓ 2.6 54,457 1

HashAggregate (cost=246,961.02..247,168.02 rows=20,700 width=24) (actual time=3,811.682..3,846.864 rows=54,457 loops=1)

  • Output: post.thread_id, max((now() - post.post_ts))
  • Group Key: post.thread_id
5. 1,323.634 1,323.634 ↑ 1.0 6,194,803 1

Seq Scan on ib.post (cost=0.00..184,684.51 rows=6,227,651 width=16) (actual time=0.026..1,323.634 rows=6,194,803 loops=1)

  • Output: post.id, post.thread_id, post.no, post.post_ts, post.com, post.name, post.email, post.tripcode, post.capcode, post.tags, post.subject, post.deleted, post.fileposts
6. 0.669 4.549 ↑ 1.4 1,128 1

Hash (cost=1,122.40..1,122.40 rows=1,563 width=55) (actual time=4.549..4.549 rows=1,128 loops=1)

  • Output: thread.id, thread.no, thread.last_chk, board.slug, site.slug
  • Buckets: 2048 Batches: 1 Memory Usage: 88kB
7. 0.320 3.880 ↑ 1.4 1,128 1

Nested Loop (cost=47.87..1,122.40 rows=1,563 width=55) (actual time=0.537..3.880 rows=1,128 loops=1)

  • Output: thread.id, thread.no, thread.last_chk, board.slug, site.slug
8. 0.059 0.152 ↓ 1.6 8 1

Hash Join (cost=1.07..2.43 rows=5 width=37) (actual time=0.128..0.152 rows=8 loops=1)

  • Output: board.slug, board.id, site.slug
  • Inner Unique: true
  • Hash Cond: (board.site_id = site.id)
9. 0.028 0.028 ↑ 1.0 24 1

Seq Scan on ib.board (cost=0.00..1.24 rows=24 width=7) (actual time=0.020..0.028 rows=24 loops=1)

  • Output: board.id, board.site_id, board.name, board.slug, board.description, board.last_chk, board.crawl_all, board.chk_delay
10. 0.009 0.065 ↑ 1.0 1 1

Hash (cost=1.06..1.06 rows=1 width=34) (actual time=0.065..0.065 rows=1 loops=1)

  • Output: site.slug, site.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
11. 0.056 0.056 ↑ 1.0 1 1

Seq Scan on ib.site (cost=0.00..1.06 rows=1 width=34) (actual time=0.055..0.056 rows=1 loops=1)

  • Output: site.slug, site.id
  • Filter: (site.slug = 'fchan'::text)
  • Rows Removed by Filter: 4
12. 2.816 3.408 ↑ 2.3 141 8

Bitmap Heap Scan on ib.thread (cost=46.80..220.73 rows=326 width=22) (actual time=0.096..0.426 rows=141 loops=8)

  • Output: thread.id, thread.slug, thread.no, thread.subject, thread.pinned, thread.bump_limit, thread.deleted, thread.last_chk, thread.board_id, thread.status
  • Recheck Cond: (thread.board_id = board.id)
  • Filter: (thread.status <> 404)
  • Rows Removed by Filter: 23
  • Heap Blocks: exact=559
13. 0.592 0.592 ↑ 13.1 179 8

Bitmap Index Scan on thread_pkey (cost=0.00..46.71 rows=2,351 width=0) (actual time=0.074..0.074 rows=179 loops=8)

  • Index Cond: (thread.board_id = board.id)
14. 1.069 1.128 ↑ 1.0 10 1,128

Materialize (cost=0.00..1.15 rows=10 width=12) (actual time=0.000..0.001 rows=10 loops=1,128)

  • Output: upd_period.upd_period, upd_period.min_age, upd_period.max_age
15. 0.059 0.059 ↑ 1.0 10 1

Seq Scan on ib.upd_period (cost=0.00..1.10 rows=10 width=12) (actual time=0.056..0.059 rows=10 loops=1)

  • Output: upd_period.upd_period, upd_period.min_age, upd_period.max_age
Planning time : 2.778 ms
Execution time : 3,876.653 ms