From: nik9000@gmail.com
To: Asmir Mustafic <goetas@lignano.it>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: 700K Inserts in transaction
Date: Thu, 14 Feb 2013 06:51:49 -0500
Message-ID: <B3454B20-7024-43C2-A2E2-265AADBEBD1F@gmail.com> (raw)
In-Reply-To: <511CBC55.9080804@lignano.it>
References: <511CBC55.9080804@lignano.it>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Are the duplicates evenly distributed? You might have started on a big chunk of dupes.
I'd go about this by loading my new data in a new table, removing the dupes, then inserting all the new data into the old table. That way you have more granular information about the process. And you can do the initial load with copy if you need it. And you can remove the dupes outside of a transaction.
Nik
Sent from my iPhone
On Feb 14, 2013, at 5:28 AM, Asmir Mustafic <goetas@lignano.it> wrote:
> Hi everybody!
> I'm new in mailing list, and i have a little question.
>
>
> The tables are:
> postalcodes (place_id, code), PK(place_id, code) 600K of rws
> places (id, name), PK(id), INDEX(name) 3M of rows
>
> I've to insert another 600k of rows into postalcodes table, in a single transaction, omitting duplicates.
>
> The insert query is a prepared statement like this:
>
> INSERT INTO postalcodes (place_id, code)
> SELECT places.id, :code
> FROM places
> LEFT JOIN postalcodes (postalcodes.place_id = places.id and postalcodes.code = :code)
> WHERE places.name = :name AND postalcodes.place_id IS NULL
>
> Inserting rows works well (3000 queries per second), but when i reach 30K of executed statements, the insert rate slows down to 500/1000 queries per second).
>
> Doing a commit every 20K of inserts, the insert rate remain 3000 queries per second.
>
> There is a limit of inserts in a transaction?
>
>
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: nik9000@gmail.com, goetas@lignano.it
Subject: Re: 700K Inserts in transaction
In-Reply-To: <B3454B20-7024-43C2-A2E2-265AADBEBD1F@gmail.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