Hi, What Albe said about PKs. I'm also not very fond of people using text in keys (even if it's a varchar or a char field). Test the same thing with a numeric key and you are likely to see a difference.
Apart from making sure your design is ok, you might want to keep this stuff well in the background, firing it as a frequent (and small) job. Sort of "your own private vacuum job". Something like delete from pref_users where id not in ( select distinct id from pref_money ) limit 100; Where 100 may be any number of records you find to be compatible with a smooth performance. Keep calling this stuff at a suitable interval (X secs in between each call), and it will silently do the cleaning without creating giant transactions. You definitely want to make a good design BEFORE doing this, though. Cheers Bèrto On 1 February 2013 09:38, Alexander Farber <alexander.far...@gmail.com> 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 > > # \d pref_users > Table "public.pref_users" > Column | Type | Modifiers > ------------+-----------------------------+-------------------- > id | character varying(32) | not null > first_name | character varying(64) | > last_name | character varying(64) | > female | boolean | > avatar | character varying(128) | > city | character varying(64) | > login | timestamp without time zone | default now() > last_ip | inet | > logout | timestamp without time zone | > vip | timestamp without time zone | > mail | character varying(254) | > medals | integer | not null default 0 > Indexes: > "pref_users_pkey" PRIMARY KEY, btree (id) > Referenced by: > TABLE "pref_cards" CONSTRAINT "pref_cards_id_fkey" FOREIGN KEY > (id) REFERENCES pref_users(id) ON DELETE CASCADE > TABLE "pref_catch" CONSTRAINT "pref_catch_id_fkey" FOREIGN KEY > (id) REFERENCES pref_users(id) ON DELETE CASCADE > TABLE "pref_chat" CONSTRAINT "pref_chat_id_fkey" FOREIGN KEY (id) > REFERENCES pref_users(id) ON DELETE CASCADE > TABLE "pref_discuss" CONSTRAINT "pref_discuss_id_fkey" FOREIGN KEY > (id) REFERENCES pref_users(id) ON DELETE CASCADE > TABLE "pref_game" CONSTRAINT "pref_game_id_fkey" FOREIGN KEY (id) > REFERENCES pref_users(id) ON DELETE CASCADE > TABLE "pref_luck" CONSTRAINT "pref_luck_id_fkey" FOREIGN KEY (id) > REFERENCES pref_users(id) ON DELETE CASCADE > TABLE "pref_match" CONSTRAINT "pref_match_id_fkey" FOREIGN KEY > (id) REFERENCES pref_users(id) ON DELETE CASCADE > TABLE "pref_misere" CONSTRAINT "pref_misere_id_fkey" FOREIGN KEY > (id) REFERENCES pref_users(id) ON DELETE CASCADE > TABLE "pref_money" CONSTRAINT "pref_money_id_fkey" FOREIGN KEY > (id) REFERENCES pref_users(id) ON DELETE CASCADE > TABLE "pref_pass" CONSTRAINT "pref_pass_id_fkey" FOREIGN KEY (id) > REFERENCES pref_users(id) ON DELETE CASCADE > TABLE "pref_payment" CONSTRAINT "pref_payment_id_fkey" FOREIGN KEY > (id) REFERENCES pref_users(id) ON DELETE CASCADE > TABLE "pref_rep" CONSTRAINT "pref_rep_author_fkey" FOREIGN KEY > (author) REFERENCES pref_users(id) ON DELETE CASCADE > TABLE "pref_rep" CONSTRAINT "pref_rep_id_fkey" FOREIGN KEY (id) > REFERENCES pref_users(id) ON DELETE CASCADE > TABLE "pref_scores" CONSTRAINT "pref_scores_id_fkey" FOREIGN KEY > (id) REFERENCES pref_users(id) ON DELETE CASCADE > TABLE "pref_status" CONSTRAINT "pref_status_id_fkey" FOREIGN KEY > (id) REFERENCES pref_users(id) ON DELETE CASCADE > TABLE "pref_votes" CONSTRAINT "pref_votes_id_fkey" FOREIGN KEY > (id) REFERENCES pref_users(id) ON DELETE CASCADE > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- ============================== If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general