#	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)

 Hash Join  (cost=125815.44..128953.73 rows=10333 width=157) (actual time=1035.785..1117.410 rows=20778 loops=1)
   Hash Cond: (board.site_id = site.id)
   ->  Hash Join  (cost=125814.32..128920.49 rows=10333 width=127) (actual time=1035.759..1113.114 rows=20778 loops=1)
         Hash Cond: (thread.board_id = board.id)
         ->  Hash Right Join  (cost=125812.81..128886.86 rows=10333 width=93) (actual time=1035.734..1108.743 rows=20778 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
               ->  Nested Loop Left Join  (cost=124711.10..127749.00 rows=13772 width=12) (actual time=1024.606..1081.543 rows=27366 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
                     ->  HashAggregate  (cost=124711.10..124835.05 rows=12395 width=24) (actual time=1024.591..1031.591 rows=27366 loops=1)
                           Group Key: post.thread_id
                           ->  Seq Scan on post  (cost=0.00..87631.60 rows=2966360 width=16) (actual time=0.007..219.882 rows=2969070 loops=1)
                     ->  Materialize  (cost=0.00..1.15 rows=10 width=12) (actual time=0.000..0.000 rows=10 loops=27366)
                           ->  Seq Scan on upd_period  (cost=0.00..1.10 rows=10 width=12) (actual time=0.006..0.006 rows=10 loops=1)
               ->  Hash  (cost=752.98..752.98 rows=27898 width=93) (actual time=11.100..11.100 rows=27745 loops=1)
                     Buckets: 32768  Batches: 1  Memory Usage: 3991kB
                     ->  Seq Scan on thread  (cost=0.00..752.98 rows=27898 width=93) (actual time=0.008..4.660 rows=27745 loops=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
               ->  Seq Scan on board  (cost=0.00..1.23 rows=23 width=36) (actual time=0.007..0.012 rows=23 loops=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
         ->  Seq Scan on site  (cost=0.00..1.05 rows=5 width=34) (actual time=0.013..0.015 rows=5 loops=1)

Per node type stats

node type	count	sum of times	% of query
Hash	3	6.453 ms	0.6 %
Hash Join	2	8.627 ms	0.8 %
Hash Right Join	1	16.100 ms	1.4 %
HashAggregate	1	811.709 ms	72.6 %
Materialize	1	0.000 ms	0.0 %
Nested Loop Left Join	1	49.952 ms	4.5 %
Seq Scan	5	224.575 ms	20.1 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
board	1	0.012 ms	0.0 %
Seq Scan	1	0.012 ms	100.0 %
post	1	219.882 ms	19.7 %
Seq Scan	1	219.882 ms	100.0 %
site	1	0.015 ms	0.0 %
Seq Scan	1	0.015 ms	100.0 %
thread	1	4.660 ms	0.4 %
Seq Scan	1	4.660 ms	100.0 %
upd_period	1	0.006 ms	0.0 %
Seq Scan	1	0.006 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5bGT : Original query

Color mode:

Visible columns:

Per node type stats

Per table stats