pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuestion of using COPY on a table with triggers
4+ messages / 3 participants
[nested] [flat]
* Question of using COPY on a table with triggers
@ 2010-07-15 17:27 Benjamin Krajmalnik <kraj@servoyant.com>
0 siblings, 1 reply; 4+ messages in thread
From: Benjamin Krajmalnik @ 2010-07-15 17:27 UTC (permalink / raw)
To: pgsql-performance
First of all, a little background.
We have a table which is used as a trigger table for entering and
processing data for a network monitoring system.
Essentially, we insert a set of columns into a table, and each row fires
a trigger function which calls a very large stored procedure which
aggregates data, etc. At that point, the row is deleted from the temp
table.
Currently, records are transferred from the data collector as a series
of multi-row inserts.
Before going through the exercise of recoding, and given the fact that
each of this inserts fires of a trigger, will I see any noticeable
performance?
The table definition follows:
CREATE TABLE tbltmptests
(
tmptestsysid bigserial NOT NULL,
testhash character varying(32),
testtime timestamp with time zone,
statusid integer,
replytxt text,
replyval real,
groupid integer,
CONSTRAINT tbltmptests_pkey PRIMARY KEY (tmptestsysid)
)
WITH (
OIDS=FALSE
);
ALTER TABLE tbltmptests OWNER TO postgres;
-- Trigger: tbltmptests_tr on tbltmptests
-- DROP TRIGGER tbltmptests_tr ON tbltmptests;
CREATE TRIGGER tbltmptests_tr
AFTER INSERT
ON tbltmptests
FOR EACH ROW
EXECUTE PROCEDURE fn_testtrigger();
Another question - is there anything special we need to do to handle the
primary constraint field?
Now, on a related note and looking forward to the streaming replication
of v9, will this work with it, since we have multiple tables being
update by a trigger function?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Question of using COPY on a table with triggers
@ 2010-07-15 22:46 Pierre C <lists@peufeu.com>
parent: Benjamin Krajmalnik <kraj@servoyant.com>
0 siblings, 1 reply; 4+ messages in thread
From: Pierre C @ 2010-07-15 22:46 UTC (permalink / raw)
To: Benjamin Krajmalnik <kraj@servoyant.com>; pgsql-performance
> Essentially, we insert a set of columns into a table, and each row fires
> a trigger function which calls a very large stored procedure
For inserting lots of rows, COPY is much faster than INSERT because it
parses data (a lot) faster and is more "data-stream-friendly". However the
actual inserting into the tbale and trigger-calling has to be done for
both.
If the trigger is a "very large stored procedure" it is very likely that
executing it will take a lot more time than parsing & executing the
INSERT. So, using COPY instead of INSERT will not gain you anything.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Question of using COPY on a table with triggers
@ 2010-07-15 23:28 Benjamin Krajmalnik <kraj@servoyant.com>
parent: Pierre C <lists@peufeu.com>
0 siblings, 1 reply; 4+ messages in thread
From: Benjamin Krajmalnik @ 2010-07-15 23:28 UTC (permalink / raw)
To: Pierre C <lists@peufeu.com>; pgsql-performance
That is what I thought.
The trigger calls a 3000 row stored procedure which does all of the calculations to aggregate data into 3 separate tables and then insert the raw data point into a 4th table.
> -----Original Message-----
> From: Pierre C [mailto:lists@peufeu.com]
> Sent: Thursday, July 15, 2010 4:47 PM
> To: Benjamin Krajmalnik; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Question of using COPY on a table with triggers
>
> > Essentially, we insert a set of columns into a table, and each row
> fires
> > a trigger function which calls a very large stored procedure
>
>
> For inserting lots of rows, COPY is much faster than INSERT because it
> parses data (a lot) faster and is more "data-stream-friendly". However
> the
> actual inserting into the tbale and trigger-calling has to be done for
> both.
>
> If the trigger is a "very large stored procedure" it is very likely
> that
> executing it will take a lot more time than parsing & executing the
> INSERT. So, using COPY instead of INSERT will not gain you anything.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Question of using COPY on a table with triggers
@ 2010-07-16 00:24 Tom Lane <tgl@sss.pgh.pa.us>
parent: Benjamin Krajmalnik <kraj@servoyant.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2010-07-16 00:24 UTC (permalink / raw)
To: Benjamin Krajmalnik <kraj@servoyant.com>; +Cc: Pierre C <lists@peufeu.com>; pgsql-performance
"Benjamin Krajmalnik" <kraj@servoyant.com> writes:
> That is what I thought.
> The trigger calls a 3000 row stored procedure which does all of the calculations to aggregate data into 3 separate tables and then insert the raw data point into a 4th table.
Youch. Seems like you might want to rethink the idea of doing those
calculations incrementally for each added row. Wouldn't it be better
to add all the new data and then do the aggregation once?
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2010-07-16 00:24 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-07-15 17:27 Question of using COPY on a table with triggers Benjamin Krajmalnik <kraj@servoyant.com>
2010-07-15 22:46 ` Pierre C <lists@peufeu.com>
2010-07-15 23:28 ` Benjamin Krajmalnik <kraj@servoyant.com>
2010-07-16 00:24 ` Tom Lane <tgl@sss.pgh.pa.us>
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