Re: Optimizing select count query which often takes over 10 seconds - Mailing list pgsql-general

From Alexander Farber
Subject Re: Optimizing select count query which often takes over 10 seconds
Date
Msg-id CAADeyWiaUX+M-zf_nTEJvPEBRr2tcHevrgmM0xumNmrKMcWHnA@mail.gmail.com
Whole thread Raw
In response to Re: Optimizing select count query which often takes over 10 seconds  (Alban Hertroys <haramrae@gmail.com>)
List pgsql-general
Hello -

On Thu, Jan 24, 2013 at 3:39 PM, Alban Hertroys <haramrae@gmail.com> wrote:
> On 24 January 2013 10:57, Alexander Farber <alexander.farber@gmail.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).

I've noticed that too, but what
does "sorting on disk" mean?

I have a lot of RAM (32 GB) ,
should I increase work_mem even more?
(it is currenlty 32 MB)

Regards
Alex


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


pgsql-general by date:

Previous
From: Magnus Hagander
Date:
Subject: Re: Throttling Streamming Replication
Next
From: Jeff Janes
Date:
Subject: Re: Optimizing select count query which often takes over 10 seconds