Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Thu, 2013-01-24 at 15:45 +0100, Alexander Farber wrote:
> Hello -
> 
> On Thu, Jan 24, 2013 at 3:39 PM, Alban Hertroys <haramrae@xxxxxxxxx> wrote:
> > On 24 January 2013 10:57, Alexander Farber <alexander.farber@xxxxxxxxx>
> > 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)
> 

You should better create an index on pref_money(yw, money). It could
help you get rid of the seqscan and sort operations.


-- 
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux