X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 90B00D1D957 for ; Mon, 22 Mar 2004 18:24:55 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 28900-04 for ; Mon, 22 Mar 2004 14:25:03 -0400 (AST) Received: from servidor.silcom (silcom.com.br [200.138.113.93]) by svr1.postgresql.org (Postfix) with ESMTP id D4FABD1D695 for ; Mon, 22 Mar 2004 14:24:51 -0400 (AST) Received: from paranagua.silcom (paranagua.silcom [192.168.0.11]) by servidor.silcom (8.11.6p2/linuxconf) with ESMTP id i2MIOxJ26343 for ; Mon, 22 Mar 2004 15:24:59 -0300 Subject: Slow Foreign Key From: Ricardo Vaz Mannrich To: pgsql-admin@postgresql.org Content-Type: text/plain Message-Id: <1079979898.5609.15.camel@paranagua.silcom> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.2 Date: 22 Mar 2004 15:24:58 -0300 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/288 X-Sequence-Number: 12857 I'm trying to do a lot of inserts on a detail table, but with foreign key schema it's too slow. I made few tests. 1) Master table with 290,000 rows and 4 columns (primary key is SERIAL) 2) Detail table now with 1,300,000 rows and 3 columns (primary key is SERIAL and I have a column master_id here pointing to a master table record). 3) If I use: ALTER TABLE detail ADD CONSTRAINT master_fk FOREIGN KEY master_id REFERENCES master I get 5 INSERTs per second using this: INSERT INTO master VALUES (DEFAULT, 'Random 1', 'Random 2', 'Random 3'); SELECT CURRVAL('master_master_id_seq'); INSERT INTO detail VALUES (DEFAULT, XX, 999); (random number of detail records, alternating with a insert into master) I can insert just 5 rows/second. 4) So, I change it: ALTER TABLE detail DROP CONSTRAINT master_fk; And run INSERTs again. Now, I have 1000 rows/second (with SELECT checking before every INSERT). Is FOREIGN KEY so slow like that? What can I change in PostgreSQL configuration? Note that I run some VACUUM and CLUSTER. This let my INSERTs a very little fast. My (test) system is: Linux 2.4.21 PostgreSQL 7.4.1 Pentium IV 1.8Ghz 256MB RAM HD IDE Could anyone help me? -- Ricardo Vaz Mannrich