From: Pierre C <lists@peufeu.com>
To: pgsql-performance@postgresql.org, "Mario Splivalo" <mario.splivalo@megafon.hr>
Subject: Re: SELECT INTO large FKyed table is slow
Date: Sun, 28 Nov 2010 19:56:17 +0100
Message-ID: <op.vmv9j3oneorkce@apollo13> (raw)
In-Reply-To: <4CF24103.8030209@megafon.hr>
References: <4CF24103.8030209@megafon.hr>
> When I remove foreign constraints (drones_history_fk__samples and
> drones_history_fk__drones) (I leave the primary key on drones_history)
> than that INSERT, even for 50k rows, takes no more than a second.
>
> So, my question is - is there anything I can do to make INSERTS with PK
> faster? Or, since all the reference checking is done inside the
> procedure for loading data, shall I abandon those constraints entirely?
>
> Mario
Maybe... or not. Can you post details about :
- the foreign keys
- the tables that are referred to (including indexes)
CREATE TABLE foo (x INTEGER PRIMARY KEY); INSERT INTO foo SELECT * FROM
generate_series( 1,100000 );
Temps : 766,182 ms
test=> VACUUM ANALYZE foo;
Temps : 71,938 ms
test=> CREATE TABLE bar ( x INTEGER REFERENCES foo(x) );
CREATE TABLE
test=> INSERT INTO bar SELECT * FROM generate_series( 1,100000 );
Temps : 2834,430 ms
As you can see, 100.000 FK checks take less than 3 seconds on this very
simple example. There is probably something that needs fixing.
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: lists@peufeu.com, mario.splivalo@megafon.hr
Subject: Re: SELECT INTO large FKyed table is slow
In-Reply-To: <op.vmv9j3oneorkce@apollo13>
* 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