On Fri, 1 Feb 2013 10:38:26 +0100, Alexander Farber wrote: > Hello, > > in a Facebook game running on > PostgreSQL 8.4.13 and having so many players: > > # select count(*) from pref_users; > count > -------- > 223964 > > I am trying to get rid of inactive users, > who just visited the canvas page, but > never played (I'm sure, Facebook has > a clever-sounding name for them): > > # select count(*) from pref_users > where id not in (select distinct id from pref_money); > count > -------- > 173936 > (1 row) > > So I call: > > # delete from pref_users > where id not in (select distinct id from pref_money); > > but that query lasts forever and > what's more troubling me - it blocks > the async queries of my game daemon > (the Perl function pg_ready starts returning > false all the time and my game accumulates > thousands of yet-to-be-executed SQL queries). > > The good news is, that my quad server > doesn't hang - I just see 1 postmaster > process at 90-100% CPU but total load is 20%. > > Also my game daemon in Perl recovers > and executes the thousands of queued > up async queries, when I interrupt the > above DELETE query with CTRL-C at > the pgsql prompt - i.e. my game is not buggy. > > My question is how handle this? > > Why does deleting takes so long, > is it because of CASCADES? > > And why does it make the pg_ready > calls of my game daemon return false? > The users I'm deleting aren't active, > they shouldn't "intersect" with the > async queries of my game daemon. > > Below are the both SQL tables involved, > thank you for any insights. > > Regards > Alex > > # \d pref_money > Table "public.pref_money" > Column | Type | Modifiers > --------+-----------------------+----------------------------------------- > id | character varying(32) | > money | integer | not null > yw | character(7) | default to_char(now(), 'IYYY-IW'::text) > Indexes: > "pref_money_money_index" btree (money DESC) > "pref_money_yw_index" btree (yw) > Foreign-key constraints: > "pref_money_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id) ON > DELETE CASCADE I believe an index on pref_money.id could help. Without it Postgres is, if I understand correctly, making 173936 table scans on pref_money to try (and fail) to find for each deleted row the referencing row in that table. > # \d pref_users [...] -- Michał Politowski -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
