Long-running query blocks all other queries

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/

On Thu, Mar 19, 2009 at 8:42 AM, Anton V. Belyaev
<anton.belyaev@gmail.com> wrote:

I have 4-cored machine, so I expected the query to be able to run for
a long time without performance degrade for application.

As Bill mentioned, this won't help a bit if you've got a slow / dodgy
/ overloaded IO subsystem. What does vmstat 1 60 have to say about
the situation when the queries are running?