'Cascade' Insert - how to speed up insert with reference key

PostgreSQL database administration questions.

'Cascade' Insert - how to speed up insert with reference key

Postby alex » Tue Sep 11, 2012 1:30 am

Hi Experts,

Let's assume we ve got tables like that (I've got much complicated structure but I just want to show you my problem by example):


#ID serial primary key
name text unique


#Photo_Id serial primary key
person_id integer references Person (ID)
size integer

Now, I want to do the following steps:

Add 1 Person and for this record add 1 Photo. I have implemented it in this way so far:

INSERT INTO Person (name) VALUES ('Tom');
SELECT ID FROM Person WHERE name = 'Tom'; # I save it to the variable f. e. X
INSERT INTO Photo (person_id, size) VALUES (X, 1234);

It works really slow because I do it in my code millions times and SELECT query is expensive.

Could you tell me how I can increase the speed of that

I implemented code in perl using DBI if you need this information

Thank you in advance
Posts: 6
Joined: Tue Jul 14, 2009 9:43 am

Invitations sent: 0
Successful invitations: 0