#	exclusive	inclusive	rows x	rows	loops	node
1.	19792.346	45760.144	↓ 2.9	2123655	1	→ Sort (cost=28078918.96..28080757.81 rows=735537 width=24) (actual time=43410.638..45760.144 rows=2123655 loops=1) Sort Key: u.username, u.some_ts Sort Method: external merge Disk: 78864kB
2.	1695.077	25967.798	↓ 2.9	2123655	1	→ Seq Scan on test u (cost=0.00..27977076.63 rows=735537 width=24) (actual time=0.111..25967.798 rows=2123655 loops=1) Filter: (5 > (SubPlan 1)) Rows Removed by Filter: 82956
3.						SubPlan (forSeq Scan)
4.	2206.611	24272.721	↑ 1.0	1	2206611	→ Aggregate (cost=12.65..12.66 rows=1 width=0) (actual time=0.011..0.011 rows=1 loops=2206611)
5.	22066.110	22066.110	↑ 1.0	1	2206611	→ Index Only Scan using i on test su (cost=0.00..12.65 rows=1 width=0) (actual time=0.010..0.010 rows=1 loops=2206611) Index Cond: ((username = u.username) AND (some_ts > u.some_ts)) Heap Fetches: 2482901

                                                                QUERY PLAN
âââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââ
 Sort  (cost=28078918.96..28080757.81 rows=735537 width=24) (actual time=43410.638..45760.144 rows=2123655 loops=1)
   Sort Key: u.username, u.some_ts
   Sort Method: external merge  Disk: 78864kB
   ->  Seq Scan on test u  (cost=0.00..27977076.63 rows=735537 width=24) (actual time=0.111..25967.798 rows=2123655 loops=1)
         Filter: (5 > (SubPlan 1))
         Rows Removed by Filter: 82956
         SubPlan 1
           ->  Aggregate  (cost=12.65..12.66 rows=1 width=0) (actual time=0.011..0.011 rows=1 loops=2206611)
                 ->  Index Only Scan using i on test su  (cost=0.00..12.65 rows=1 width=0) (actual time=0.010..0.010 rows=1 loops=2206611)
                       Index Cond: ((username = u.username) AND (some_ts > u.some_ts))
                       Heap Fetches: 2482901
 Total runtime: 45850.787 ms
(12 rows)

Per node type stats

node type	count	sum of times	% of query
Aggregate	1	2206.611 ms	4.8 %
Index Only Scan using i on test su	1	22066.110 ms	48.2 %
Seq Scan	1	1695.077 ms	3.7 %
Sort	1	19792.346 ms	43.3 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
test	1	1695.077 ms	3.7 %
Seq Scan	1	1695.077 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: hgS

Colorize:

Columns visibility:

Per node type stats

Per table stats