References: <511CBC55.9080804@lignano.it>
Mime-Version: 1.0 (1.0)
In-Reply-To: <511CBC55.9080804@lignano.it>
Content-Type: text/plain;
	charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Message-Id: <B3454B20-7024-43C2-A2E2-265AADBEBD1F@gmail.com>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
From: nik9000@gmail.com
Subject: Re: 700K Inserts in transaction
Date: Thu, 14 Feb 2013 06:51:49 -0500
To: Asmir Mustafic <goetas@lignano.it>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Are the duplicates evenly distributed?  You might have started on a big chu=
nk 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 mor=
e granular information about the process. And you can do the initial load w=
ith copy if you need it. And you can remove the dupes outside of a transact=
ion.=20

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.
>=20
>=20
> The tables are:
> postalcodes (place_id, code),  PK(place_id, code) 600K of rws
> places (id, name),  PK(id), INDEX(name) 3M of rows
>=20
> I've to insert another 600k of rows into postalcodes table, in a single t=
ransaction, omitting duplicates.
>=20
> The insert query is a prepared statement like this:
>=20
> INSERT INTO postalcodes (place_id, code)
> SELECT places.id, :code
> FROM places
> LEFT JOIN postalcodes (postalcodes.place_id =3D places.id and postalcodes=
.code =3D :code)
> WHERE places.name =3D :name AND postalcodes.place_id IS NULL
>=20
> 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).
>=20
> Doing a commit every 20K of inserts, the insert rate remain 3000 queries =
per second.
>=20
> There is a limit of inserts in a transaction?
>=20
>=20
>=20
>=20
>=20
> --=20
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance