Performance tuning using copy

On Tue, Feb 08, 2005 at 05:15:55AM -0800, sid tow wrote:

Hi,

I have to know why does copy commands work faster as compared to
the insert commands. The thing is that i have a lot of
constraints and triggers. I know insert will check all the
triggers and constraints, but i wonder if copy will also do it
and then if it does then this has also to be slow. But copy
loads the database very fast. Can u tell me why.

Easy, because each INSERT statement has to be sent to the backend,
parsed, planned, and executed. The result is then sent to the client,
which then sends the next query. Talk about overhead. On my machine I
get a minimum of 0.65ms for an insert.

In contrast, COPY does one thing and does it well. Once started, a copy
has no planning overhead. The only thing that needs to happen is
convert each string element into the right data type. There is no
response to the client until the copy is complete. So your load speed
is limited only by fast you can transfer data.

I also have a concern about the performace tuninig while
updating the database. Can some one suggest me to tune in more
than the use of copy command. I have tried to even disable the
triggers and constraints but what I get is only minimal gain. Is
there any other mechanism by which we can do faster updations.

Run EXPLAIN ANALYZE on the queries you do often and check they are
being executed optimally.

Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/