In response to "Anton V. Belyaev" <anton.belyaev@xxxxxxxxx>: > 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/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
