From: Bill Moran <wmoran@potentialtech.com>
Cc: Qing Zhao <qzhao@quotefx.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: inserting large number of rows was: Re: Increasing
Date: Mon, 02 Feb 2004 16:37:02 -0500
Message-ID: <401EC2FE.3000500@potentialtech.com> (raw)
In-Reply-To: <Pine.LNX.4.33.0402021346040.20085-100000@css120.ihs.com>
References: <Pine.LNX.4.33.0402021346040.20085-100000@css120.ihs.com>
I must have missed this post when it was made earlier. Pardon the noise if
my suggestion has already been made.
Unlike MySQL (and possibly other database servers) PostgreSQL is faster when
inserting inside a transaction. Depending on the method in which you are
actually adding the records.
In my own experience (generating a list of INSERT statements from a perl
script and using psql to execute them) the difference in performance was
incredibly dramatic when I added a "BEGIN WORK" at the beginning and
"COMMIT WORK" at the end.
scott.marlowe wrote:
> On Mon, 2 Feb 2004, Qing Zhao wrote:
>
>
>>I am new here. I have a question related to this in some way.
>>
>>Our web site needs to upload a large volume of data into Postgres at a
>>time. The performance deterioates as number of rows becomes larger.
>>When it reaches 2500 rows, it never come back to GUI. Since the tests
>>were run through GUI, my suspision is
>>that it might be caused by the way the application server talking to
>>Postgres server, the connections, etc.. What might be the factors
>>involved here? Does anyone know?
>
>
> Actually, I'm gonna go out on a limb here and assume two things:
>
> 1. you've got lotsa fk/pk relationships setup.
> 2. you're analyzing the table empty before loading it up.
>
> What happens in this instance is that the analyze on an empty, or nearly
> so, table, means that during the inserts, postgresql thinks you have only
> a few rows. At first, this is fine, as pgsql will seq scan the
> tables to make sure there is a proper key in both. As the number of
> rows increases, the planner needs to switch to index scans but doesn't,
> because it doesn't know that the number of rows is increasing.
>
> Fix: insert a few hundred rows, run analyze, check to see if the explain
> for inserts is showing index scans or not. If not, load a few more
> hundred rows, analyze, rinse, repeat.
>
> Also, look for fk/pk mismatches. I.e. an int4 field pointing to an int8
> field. That's a performance killer, so if the pk/fk types don't match,
> see if you can change your field types to match and try again.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
--
Bill Moran
Potential Technologies
http://www.potentialtech.com
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: wmoran@potentialtech.com, qzhao@quotefx.net
Subject: Re: inserting large number of rows was: Re: Increasing
In-Reply-To: <401EC2FE.3000500@potentialtech.com>
* 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