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;
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.
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 🙂