On 11/28/2010 07:56 PM, Pierre C wrote:
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)
I pasted DDL at the begining of my post. The only indexes tables have
are the ones created because of PK constraints. Table drones has around
100k rows. Table drones_history has around 30M rows. I'm not sure what
additional info you'd want but I'll be more than happy to provide more
relevant information.
CREATE TABLE foo (x INTEGER PRIMARY KEY); I
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.
Yes, when the FKyed table is small enough inserts are quite fast. But
when they grow larger the whole system slows down.
I just repeated your test and I'm getting similar results - on my
desktop. I'll try to assemble some code to recreate workload and see if
I'll run into same problems.
Mario
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
