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?