explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5bGT : Original query

Settings
# exclusive inclusive rows x rows loops node
1. 4.276 1,117.410 ↓ 2.0 20,778 1

Hash Join (cost=125,815.44..128,953.73 rows=10,333 width=157) (actual time=1,035.785..1,117.410 rows=20,778 loops=1)

  • Hash Cond: (board.site_id = site.id)
2. 4.351 1,113.114 ↓ 2.0 20,778 1

Hash Join (cost=125,814.32..128,920.49 rows=10,333 width=127) (actual time=1,035.759..1,113.114 rows=20,778 loops=1)

  • Hash Cond: (thread.board_id = board.id)
3. 16.100 1,108.743 ↓ 2.0 20,778 1

Hash Right Join (cost=125,812.81..128,886.86 rows=10,333 width=93) (actual time=1,035.734..1,108.743 rows=20,778 loops=1)

  • Hash Cond: (post.thread_id = thread.id)
  • Filter: (((now() - (thread.last_chk)::timestamp with time zone) > (upd_period.upd_period)::interval) OR (thread.last_chk IS NULL))
  • Rows Removed by Filter: 6967
4. 49.952 1,081.543 ↓ 2.0 27,366 1

Nested Loop Left Join (cost=124,711.10..127,749.00 rows=13,772 width=12) (actual time=1,024.606..1,081.543 rows=27,366 loops=1)

  • Join Filter: (((max((now() - (post.post_ts)::timestamp with time zone))) >= (upd_period.min_age)::interval) AND ((max((now() - (post.post_ts)::timestamp with time zone))) <= (upd_period.max_age)::interval))
  • Rows Removed by Join Filter: 246294
5. 811.709 1,031.591 ↓ 2.2 27,366 1

HashAggregate (cost=124,711.10..124,835.05 rows=12,395 width=24) (actual time=1,024.591..1,031.591 rows=27,366 loops=1)

  • Group Key: post.thread_id
6. 219.882 219.882 ↓ 1.0 2,969,070 1

Seq Scan on post (cost=0.00..87,631.60 rows=2,966,360 width=16) (actual time=0.007..219.882 rows=2,969,070 loops=1)

7. 0.000 0.000 ↑ 1.0 10 27,366

Materialize (cost=0.00..1.15 rows=10 width=12) (actual time=0.000..0.000 rows=10 loops=27,366)

8. 0.006 0.006 ↑ 1.0 10 1

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

9. 6.440 11.100 ↑ 1.0 27,745 1

Hash (cost=752.98..752.98 rows=27,898 width=93) (actual time=11.100..11.100 rows=27,745 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 3991kB
10. 4.660 4.660 ↑ 1.0 27,745 1

Seq Scan on thread (cost=0.00..752.98 rows=27,898 width=93) (actual time=0.008..4.660 rows=27,745 loops=1)

11. 0.008 0.020 ↑ 1.0 23 1

Hash (cost=1.23..1.23 rows=23 width=36) (actual time=0.020..0.020 rows=23 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.012 0.012 ↑ 1.0 23 1

Seq Scan on board (cost=0.00..1.23 rows=23 width=36) (actual time=0.007..0.012 rows=23 loops=1)

13. 0.005 0.020 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=34) (actual time=0.020..0.020 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.015 0.015 ↑ 1.0 5 1

Seq Scan on site (cost=0.00..1.05 rows=5 width=34) (actual time=0.013..0.015 rows=5 loops=1)