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

On Thu, Jan 24, 2013 at 1:57 AM, Alexander Farber
<alexander(dot)farber(at)gmail(dot)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