Skip site navigation (1) Skip section navigation (2)

Re: SELECT INTO large FKyed table is slow

From: Mario Splivalo <mario(dot)splivalo(at)megafon(dot)hr>
To: Pierre C <lists(at)peufeu(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: SELECT INTO large FKyed table is slow
Date: 2010-11-28 19:08:22
Message-ID: 4CF2A8A6.4010400@megafon.hr (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Pierre CDate: 2010-11-28 21:50:42
Subject: Re: SELECT INTO large FKyed table is slow
Previous:From: Pierre CDate: 2010-11-28 18:56:17
Subject: Re: SELECT INTO large FKyed table is slow

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group