pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

700K Inserts in transaction
2+ messages / 2 participants
[nested] [flat]

* 700K Inserts in transaction
@ 2013-02-14 10:28 Asmir Mustafic <goetas@lignano.it>
  2013-02-14 11:51 ` Re: 700K Inserts in transaction nik9000@gmail.com
  0 siblings, 1 reply; 2+ messages in thread

From: Asmir Mustafic @ 2013-02-14 10:28 UTC (permalink / raw)
  To: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: 700K Inserts in transaction
  2013-02-14 10:28 700K Inserts in transaction Asmir Mustafic <goetas@lignano.it>
@ 2013-02-14 11:51 ` nik9000@gmail.com
  0 siblings, 0 replies; 2+ messages in thread

From: nik9000@gmail.com @ 2013-02-14 11:51 UTC (permalink / raw)
  To: Asmir Mustafic <goetas@lignano.it>; +Cc: pgsql-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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2013-02-14 11:51 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-02-14 10:28 700K Inserts in transaction Asmir Mustafic <goetas@lignano.it>
2013-02-14 11:51 ` nik9000@gmail.com

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