On Thu, Jan 24, 2013 at 1:57 AM, Alexander Farber <alexander.farber@xxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
