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

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