Re: SELECT INTO large FKyed table is slow

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