# explain analyze select count(id) from ( select id, row_number() over(partition by yw order by money desc) as ranking from pref_money ) x where x.ranking = 1 and id='OK452217781481'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=63694.22..63694.23 rows=1 width=82) (actual time=4520.719..4520.719 rows=1 loops=1) -> Subquery Scan x (cost=48519.10..63694.19 rows=11 width=82) (actual time=4470.620..4520.710 rows=6 loops=1) Filter: ((x.ranking = 1) AND ((x.id)::text = 'OK452217781481'::text)) -> WindowAgg (cost=48519.10..57190.58 rows=433574 width=26) (actual time=4293.315..4491.652 rows=429803 loops=1) -> Sort (cost=48519.10..49603.03 rows=433574 width=26) (actual time=4293.306..4352.544 rows=429803 loops=1) Sort Key: pref_money.yw, pref_money.money Sort Method: external sort Disk: 15856kB
It's sorting on disk. That's not going to be fast. Indeed, it's taking nearly all the time the query takes (4.4s for this step out of 4.5s for the query).
And then it's doing a sequential scan to sort the data. I suspect that's because it's sorting on disk. Then again, this only takes 42ms, just once (loops=1), so perhaps a seqscan is indeed the fastest approach here (actually, wow, it scans 10000 records/ms - rows are 26 bytes wide, so that's 260MB/s! I'm doubting my math here...).
