In response to "Anton V. Belyaev" <anton.belyaev@gmail.com>:
> Hello,
>
> I want to run a long running query in background to collect
> statistics. I.e. how many users played the game last month more than
> 50 times.
>
> SELECT count(*) FROM (SELECT user_id, COUNT(*) AS total_games FROM
> games, game2user WHERE games.finished > '2009-02-19' AND games.id =
> game2user.game_id GROUP BY user_id) AS aaa WHERE aaa.total_games > 50;
>
> I have 4-cored machine, so I expected the query to be able to run for
> a long time without performance degrade for application.
>
> But the query has completely blocked all other queries. Even those,
> which are not related to tables, used by the long-running query.
>
> Why does this block happen and how to aid it?
Off the top of my head, I can't think of any reason why that query
would block anything else.
Start with SELECT * FROM pg_locks; to see if there are actually any
locks occurring that could be causing problems.
If there are no explicit locks, you may be looking at IO starvation.
All the cores in the world won't help you if your disks are too slow
to feed the required information. Have you investigated this
possibility yet?
--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/