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

On Thu, Jan 24, 2013 at 1:57 AM, Alexander Farber
<alexander.farber@gmail.com> wrote:
> Hello,
>
> for a PostgreSQL 8.4.13 database + pgbouncer

Using 8.4 is really going to limit your options.

..

>
> LOG:  duration: 12590.394 ms  statement:
>         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='OK471018960997'

Since you only care about ranking=1, it might be better to rewrite
that using something like:

where money = (select max(money....)

But, I doubt it.  I don't think even the 9.2 planner has the smarts to
do what you want efficiently.  It might be possible to make it do it
efficiently using a recursive query, once you have the index on
(yw,money).


> This command comes from a PHP-script
> of mine which displays "medals" on
> a player profile page - meaning how many
> times she won a weekly tournament:
..
>
> Does anybody please have an idea
> how could I optimize it or should
> I introduce a hourly job and a "medals"
> column (that would make my players
> stats less "live")?

This sounds like a good idea.  But if the tournament is weekly why
would the job have to be hourly?  Why do the results of a weekly
tournament need to be 'live'?

Cheers,

Jeff


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