On 24 January 2013 10:57, Alexander Farber <alexander.farber@xxxxxxxxx> wrote:
--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.
# 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).
-> Seq Scan on pref_money (cost=0.00..7923.74
rows=433574 width=26) (actual time=0.006..41.907 rows=429803 loops=1)
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...).
Total runtime: 4525.662 ms
(9 rows)
Thank you for any hints
Alex
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.
