pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedWeird Database Performance problem!
3+ messages / 2 participants
[nested] [flat]
* Weird Database Performance problem!
@ 2004-08-13 14:43 Arash Zaryoun <Arash_Zaryoun@CBC.CA>
2004-08-13 15:20 ` Re: Weird Database Performance problem! Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 3+ messages in thread
From: Arash Zaryoun @ 2004-08-13 14:43 UTC (permalink / raw)
To: pgsql-performance
Hi,
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?
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!
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Weird Database Performance problem!
2004-08-13 14:43 Weird Database Performance problem! Arash Zaryoun <Arash_Zaryoun@CBC.CA>
@ 2004-08-13 15:20 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 3+ messages in thread
From: Richard Huxton @ 2004-08-13 15:20 UTC (permalink / raw)
To: Arash Zaryoun <Arash_Zaryoun@CBC.CA>; +Cc: pgsql-performance
Arash Zaryoun wrote:
> Hi,
>
> 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:
> gctwebu_srl | numeric(12,0) |
> GCTWEBU:
> srl | integer | not null default
Your types don't match. You have a numeric referencing an integer. PG
probably isn't using the index (it's smarter about this in 8.0 iirc).
HTH
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Weird Database Performance problem!
@ 2004-08-13 19:23 Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 3+ messages in thread
From: Richard Huxton @ 2004-08-13 19:23 UTC (permalink / raw)
To: Arash Zaryoun <Arash_Zaryoun@CBC.CA>; +Cc: pgsql-performance
Arash Zaryoun wrote:
> Hi Richard,
>
> Thanks for your prompt reply. It fixed the problem.
> Just one more question: Do I need to create an index for FKs?
You don't _need_ to, but on the referring side (e.g. table GCTBALLOT in
your example) PostgreSQL won't create one automatically.
Of course, the primary-key side will already have an index being used as
part of the constraint.
I've cc:ed the list on this, the question pops up quite commonly.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-08-13 19:23 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-13 14:43 Weird Database Performance problem! Arash Zaryoun <Arash_Zaryoun@CBC.CA>
2004-08-13 15:20 ` Richard Huxton <dev@archonet.com>
2004-08-13 19:23 Re: Weird Database Performance problem! Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox