Thread: 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/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.