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

The cronjob gives me now occasionally:

/* reset and then update medals count */
update pref_users set medals = 0;
psql:/home/afarber/bin/clean-database.sql:63: ERROR:  deadlock detected

DETAIL:  Process 31072 waits for ShareLock on transaction 124735679;
blocked by process 30368.
Process 30368 waits for ShareLock on transaction 124735675; blocked by
process 31072.
HINT:  See server log for query details.
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;

Any ideas please how to workaround?

Thank you
Alex