Re: SELECT INTO large FKyed table is slow - Mailing list pgsql-performance

From Pierre C
Subject Re: SELECT INTO large FKyed table is slow
Date
Msg-id op.vmv9j3oneorkce@apollo13
Whole thread Raw
In response to SELECT INTO large FKyed table is slow  (Mario Splivalo <mario.splivalo@megafon.hr>)
Responses Re: SELECT INTO large FKyed table is slow  (Mario Splivalo <mario.splivalo@megafon.hr>)
List pgsql-performance
> 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.

pgsql-performance by date:

Previous
From: Mario Splivalo
Date:
Subject: SELECT INTO large FKyed table is slow
Next
From: Mario Splivalo
Date:
Subject: Re: SELECT INTO large FKyed table is slow