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

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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!

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: ,

Leave a Reply