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

Thank you, I've ended up with this cronjob
(yes I'm unfrotunately having week numbers as strings):

/* reset and then update medals count */

update pref_users set medals = 0;
UPDATE 223456

update pref_users u
set medals = s.medals
from (
    select id, count(id) medals
    from (
        select id,
            row_number() over(partition by yw order by money desc) as ranking
        from pref_money where yw <> to_char(CURRENT_TIMESTAMP, 'IYYY-IW')
    ) x
    where ranking = 1
    group by id
) s
where u.id = s.id;
UPDATE 65