How to limit PostgreSQL INSERTs to assure that the caller has an attribute conistent with the parent table?

Question

In the parent table, there is a column that defines the 'owner' of the row. When inserting into the child, the caller provides an owner id or '%', to indicate that the the caller is the administrator. I was expecting the insert with this check to be slower that a straight insert, but I didn't expect a 70x penalty. Can you give me some ideas for how to optimize the performance to achieve the same result as this statement?

INSERT INTO child (parent_key, value1, value2)
 SELECT 1,ドル 2,ドル 3ドル FROM parent 
 WHERE parent_key = 1ドル
 AND owner LIKE 4ドル
 LIMIT 1;

Table definitions:

CREATE TABLE parent (
 parent_key VARCHAR(255) PRIMARY KEY, 
 owner VARCHAR(255)
);
CREATE TABLE child (
 child_key SERIAL PRIMARY KEY, 
 parent_key VARCHAR(255) REFERENCES parent, 
 value1 VARCHAR(255), 
 value2 VARCHAR(255)
);

I ran an explain on my statement, and this is what I see.

 Insert on child (cost=0.42..8.46 rows=1 width=1670)
 -> Subquery Scan on "*SELECT*" (cost=0.42..8.46 rows=1 width=1670)
 -> Limit (cost=0.42..8.44 rows=1 width=296)
 -> Index Scan using parent_pkey on parent (cost=0.42..8.44 rows=1 width=296)
 Index Cond: ((parent_key)::text = '111'::text)
 Filter: ((owner)::text ~~ '%'::text)

Since parent_pkey is a unique index, I would expect the LIKE filter to contribute an insignificant amount to the execution time. This conditional INSERT takes >70 times as long as an INSERT of VALUES. What would be a more efficient way of enforcing this constraint?

• Vérace: Hi, and welcome to the forum! Could you please give us the structures of both of your tables - DDL as text - use the edit link to provide them in the body of the question.
• John: If I change the index on the parent table to be (parent_key, owner), will the foreign key constraint on child.parent_key still make use of the index?

Answer 1

Nevermind. The long response time seems to be some aberration in the DB server. Running the same code a little later is doing the INSERTs is around 10msec pretty consistently. Sorry to bother you!