Re: Optimizing select count query which often takes over 10 seconds

On 24 January 2013 10:57, Alexander Farber <alexander(dot)farber(at)gmail(dot)com>wrote:

> # 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(at)postgresql(dot)org)
> 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.