From: Michael Stone <mstone+postgres@mathom.us>
To: pgsql-performance@postgresql.org
Subject: Re: How to determine cause of performance problem?
Date: Fri, 23 Sep 2005 11:31:18 -0400
Message-ID: <20050923153118.GB14918@mathom.us> (raw)
In-Reply-To: <1127483365.3524.36.camel@Panoramix>
References: <1127458167.2475.105.camel@Panoramix>
<20050923095517.GW14918@mathom.us>
<1127470875.3524.17.camel@Panoramix>
<20050923110500.GX14918@mathom.us>
<1127483365.3524.36.camel@Panoramix>
On Fri, Sep 23, 2005 at 03:49:25PM +0200, Joost Kraaijeveld wrote:
>On Fri, 2005-09-23 at 07:05 -0400, Michael Stone wrote:
>> Ok, that's great, but you didn't respond to the suggestion of using COPY
>> INTO instead of INSERT.
>Part of the code I left out are some data conversions (e.g. from
>path-to-file to blob, from text to date (not castable because of the
>homebrew original format)). I don't believe that I can do these in a SQL
>statement, can I (my knowledge of SQL as a langage is not that good)? .
>However I will investigate if I can do the conversion in two steps and
>check if it is faster.
I'm not sure what you're trying to say.
You're currently putting rows into the table by calling "INSERT INTO"
for each row. The sample code you send could be rewritten to use "COPY
INTO" instead. For bulk inserts like you're doing, the copy approach
will be a lot faster. Instead of inserting one row, waiting for a
reply, and inserting the next row, you just cram data down a pipe to the
server.
See:
http://www.postgresql.org/docs/8.0/interactive/sql-copy.htmlhttp://www.faqs.org/docs/ppbook/x5504.htm
Mike Stone
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mstone+postgres@mathom.us
Subject: Re: How to determine cause of performance problem?
In-Reply-To: <20050923153118.GB14918@mathom.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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