Performance Problem With Postgresql! - Mailing list pgsql-sql
From | Arash Zaryoun |
---|---|
Subject | Performance Problem With Postgresql! |
Date | |
Msg-id | s11b965b.088@cbc.ca Whole thread Raw |
List | pgsql-sql |
Hi All, We are having a performance problem with our database. The problem exists when we include a constraint in GCTBALLOT. The constraint is as follows: alter table GCTBALLOT add constraint FK_GCTBALLOT_GCTWEBU foreign key (GCTWEBU_SRL) references GCTWEBU (SRL) on delete restrict on update restrict; The two tables that we insert into are the following: GCTBALLOT: Table "cbcca.gctballot" Column | Type | Modifiers ------------------+-----------------------------+----------------------------------------------------------- srl | integer | not null default nextval('cbcca.gctballot_srl_seq'::text) gctbwindow_srl | numeric(12,0) | not null gctcandidate_srl | numeric(12,0) | not null gctwebu_srl | numeric(12,0) | gctphoneu_srl | numeric(12,0) | ballot_time | timestamp without time zone | not null ip_addr | character varying(15) | Indexes: "pk_gctballot" primary key, btree (srl) "i1_gctballot_webusrl" btree (gctwebu_srl) Foreign-key constraints: "fk_gctbwindow_gctballot" FOREIGN KEY (gctbwindow_srl) REFERENCES gctbwindow(srl) ON UPDATE RESTRICT ON DELETE RESTRICT "fk_gctcandidate_gctballot" FOREIGN KEY (gctcandidate_srl) REFERENCES gctcandidate(srl) ON UPDATE RESTRICT ON DELETE RESTRICT "fk_gctphoneu_gctballot" FOREIGN KEY (gctphoneu_srl) REFERENCES gctphoneu(srl) ON UPDATE RESTRICT ON DELETE RESTRICT with the extra constraint: "fk_gctballot_gctwebu" FOREIGN KEY (gctwebu_srl) REFERENCES gctwebu(srl) ON UPDATE RESTRICT ON DELETE RESTRICT GCTWEBU: Table "cbcca.gctwebu" Column | Type | Modifiers -----------------+-----------------------------+--------------------------------------------------------- srl | integer | not null default nextval('cbcca.gctwebu_srl_seq'::text) gctlocation_srl | numeric(12,0) | not null gctagerange_srl | numeric(12,0) | not null email | character varying(255) | not null uhash | character varying(255) | not null sex | character varying(1) | not null created_time | timestamp without time zone | not null Indexes: "pk_gctwebu" primary key, btree (srl) "i1_gctwebu_email" unique, btree (email) Foreign-key constraints: "fk_gctagerang_gctwebu" FOREIGN KEY (gctagerange_srl) REFERENCES gctagerange(srl) ON UPDATE RESTRICT ON DELETE RESTRICT "fk_gctwebu_gctlocation" FOREIGN KEY (gctlocation_srl) REFERENCES gctlocation(srl) ON UPDATE RESTRICT ON DELETE RESTRICT To begin, GCTBALLOT has 6122546 rows and GCTWEBU has 231444 rows. Now when we try and insert 100 entries into GCTBALLOT with the extra constraint it takes: 37981 milliseconds Also, when we try and insert 100 entries into GCTBALLOT with the extra constraint, but insert 'null' into the column gctwebu_srl it takes: 286 milliseconds However when we try and insert 100 entries into GCTBALLOT without the extra constraint (no foreign key between GCTBALLOT & GCTWEBU) it takes: 471 milliseconds In summary, inserting into GCTBALLOT without the constraint or inserting null for gctwebu_srl in GCTBALLOT gives us good performance. However, inserting into GCTBALLOT with the constraint and valid gctwebu_srl values gives us poor performance. Also, the insert we use is as follows: INSERT INTO GCTBALLOT (gctbwindow_srl, gctcandidate_srl, gctwebu_srl, gctphoneu_srl, ballot_time, ip_addr) VALUES (CBCCA.gcf_getlocation(?), ?, CBCCA.gcf_validvoter(?,?), null, ?, ?); NOTE: "gcf_validvoter" find 'gctweb_srl' value " CREATE OR REPLACE FUNCTION gcf_validvoter (VARCHAR, VARCHAR) RETURNS NUMERIC AS ' DECLARE arg1 ALIAS FOR $1; arg2 ALIAS FOR $2; return_val NUMERIC; BEGIN SELECT SRL INTO return_val FROM gctwebu WHERE EMAIL = arg1 AND UHASH = arg2; RETURN return_val; END; ' LANGUAGE plpgsql; " Where the question marks are filled in with values in our java code. We are puzzled as to why there is this difference in performance when inserting b/c we believe that we have indexed all columns used by this constraint. And we realize that inserting 'null' into GCTBALLOT doesn't use this constraint b/c no look up is necessary. So this causes good performance. Why is it that when we use this constraint that the performance is effected so much? Any help would be much appreciated. Thanks P.S. Even we added an index on 'gctwebu_srl' column and did 1- "Analyzed ALL TABLES" 2- "analyze GCTBALLOT(gctwebu_srl);" but still have the same problem!