X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 8287E32B414 for ; Sat, 9 Oct 2004 04:38:10 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 49228-01 for ; Sat, 9 Oct 2004 03:38:00 +0000 (GMT) Received: from fusion.bennyvision.com (fusion.bennyvision.com [216.161.119.28]) by svr1.postgresql.org (Postfix) with SMTP id A32E632B413 for ; Sat, 9 Oct 2004 04:37:56 +0100 (BST) Received: (qmail 10213 invoked from network); 9 Oct 2004 03:37:55 -0000 Received: from localhost (HELO webmail.stinkweasel.net) (127.0.0.1) by fusion.bennyvision.com with SMTP; 9 Oct 2004 03:37:55 -0000 Received: from 63.227.74.41 (SquirrelMail authenticated user benny); by webmail.stinkweasel.net with HTTP; Fri, 8 Oct 2004 22:37:33 -0500 (CDT) Message-ID: <51319.63.227.74.41.1097293075.squirrel@63.227.74.41> Date: Fri, 8 Oct 2004 22:37:33 -0500 (CDT) Subject: Impact of foreign keys on a simple count(*) ? From: "C. Bensend" To: pgsql-sql@postgresql.org User-Agent: SquirrelMail/1.4.3a X-Mailer: SquirrelMail/1.4.3a MIME-Version: 1.0 Content-Type: text/plain;charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) Importance: Normal X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/55 X-Sequence-Number: 19158 Hey folks (long email, my apologies), I am wrapping up my schema upgrade, and I just noticed a real show-stopper for me... Here is the "before" table structure: email_id | integer | not null joejob | boolean | default false bayes_poison | boolean | default false perm_error | boolean | default false bait_id | integer | default 0 num_recip | integer | default 1 product | integer | default 1043 date_received | timestamp with time zone | only_date | date | only_time | time with time zone | maildir_file | character varying(64) | errors_to | character varying(512) | reply_to | character varying(512) | spammer | character varying(512) | return_path | character varying(512) | received_from | character varying(512) | message_id | character varying(512) | target_domain | character varying(512) | mail_date | character varying(512) | x_priority | character varying(512) | x_msmail_priority | character varying(512) | x_mimeole | character varying(512) | mime_version | character varying(512) | subject | character varying(1024) | mail_to | character varying(2048) | x_mailer | character varying(2048) | content_type | character varying(2048) | user_agent | character varying(2048) | cc | character varying(2048) | comments | character varying(8192) | last_mod | timestamp without time zone | default ('now'::text)::timest amp(6) with time zone Indexes: "emails_pkey" primary key, btree (email_id) "emails_idx_bait_id" btree (bait_id) "emails_idx_mail_to" btree (mail_to) "emails_idx_only_date" btree (only_date) "emails_idx_only_time" btree (only_time) "emails_idx_product" btree (product) "emails_idx_received_from" btree (received_from) "emails_idx_subject" btree (subject) "emails_idx_target_domain" btree (target_domain) And here is the "after" structure: email_id | integer | not null joejob | boolean | default false bayes_poison | boolean | default false perm_error | boolean | default false num_recip | integer | default 1 mydom_id | integer | default 0 spamv_id | integer | default 1053 spammer_id | integer | default 1003 last_mod | timestamp with time zone | default ('now'::text)::timestamp with time zone bait_id | integer | product_id | integer | date_received | timestamp with time zone | only_date | date | only_time | time with time zone | maildir_file | character varying(128) | x_priority | character varying(128) | x_msmail_priority | character varying(128) | x_mimeole | character varying(128) | mime_version | character varying(512) | received_from | character varying(512) | content_type | character varying(512) | errors_to | character varying(512) | user_agent | character varying(512) | mail_date | character varying(512) | x_mailer | character varying(512) | return_path | character varying(512) | message_id | character varying(512) | reply_to | character varying(512) | subject | character varying(1024) | mail_to | character varying(1024) | cc | character varying(2048) | Indexes: "emails_pkey" primary key, btree (email_id) "emails_maildir_file_key" unique, btree (maildir_file) Foreign-key constraints: "$1" FOREIGN KEY (bait_id) REFERENCES bait(bait_id) ON UPDATE CASCADE ON DELETE SET NULL "$2" FOREIGN KEY (product_id) REFERENCES products(product_id) ON UPDATE CASCADE ON DELETE SET NULL "$3" FOREIGN KEY (mydom_id) REFERENCES my_domains(mydom_id) ON UPDATE CASCADE ON DELETE SET NULL "$4" FOREIGN KEY (spamv_id) REFERENCES spamvertisers(spamv_id) ON UPDATE CASCADE ON DELETE SET NULL "$5" FOREIGN KEY (spammer_id) REFERENCES spammers(spammer_id) ON UPDATE CASCADE ON DELETE SET NULL I am absolutely aware of the fact that the new schema can still use some work. :) But, it's a step in the right direction as I muddle my way through this. Not to mention, I really did need those foreign keys. The problem is this: Original schema: prod01=> EXPLAIN ANALYZE SELECT count(*) FROM emails; QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Aggregate (cost=6345.61..6345.61 rows=1 width=0) (actual time=1066.727..1066.728 rows=1 loops=1) -> Seq Scan on emails (cost=0.00..6121.49 rows=89649 width=0) (actual time=18.214..980.040 rows=89649 loops=1) Total runtime: 1066.931 ms (3 rows) New schema: prod01=> EXPLAIN ANALYZE SELECT count(*) FROM emails; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------- Aggregate (cost=22.50..22.50 rows=1 width=0) (actual time=40818.750..40818.752 rows=1 loops=1) -> Seq Scan on emails (cost=0.00..20.00 rows=1000 width=0) (actual time=38801.127..40458.369 rows=89649 loops=1) Total runtime: 40819.115 ms Is it the addition of the multiple foreign keys that is slowing this down so much? I have several calculations I need to do with the count, so this is a big thing for me. Suggestions appreciated, both on the immediate problem or regarding the new schema. Even "you're an idiot" is fine, if it accompanies a bit of advice. :) Thanks! Benny -- "Even if a man chops off your hand with a sword, you still have two nice, sharp bones to stick in his eyes." -- .sig on Slashdot