Would this be faster?
delete from acs_object_context_index
where
ancestor_id = :old.object_id and
object_id = :old.object_id and
object_id <> ancestor_id;
On Postgres first results in the following plan (I've put some dummy vals in the qry):
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Seq Scan on acs_object_context_index (cost=0.00..740166.29 rows=1917 width=6)
Filter: ((subplan) AND (subplan))
SubPlan
-> Seq Scan on acs_object_context_index (cost=0.00..157.03 rows=1482 width=4)
Filter: ((object_id <> ancestor_id) AND (ancestor_id = -4))
-> Materialize (cost=17.98..17.98 rows=6 width=4)
-> Index Scan using acs_object_context_index_pk on acs_object_context_index (cost=0.00..17.98 rows=6 width=4)
Index Cond: (object_id = -4)
Filter: (object_id <> ancestor_id)
(9 rows)
while second --
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Index Scan using acs_object_context_index_pk on acs_object_context_index (cost=0.00..5.45 rows=1 width=6)
Index Cond: ((object_id = -4) AND (ancestor_id = -4))
Filter: (object_id <> ancestor_id)
Looks like it would be worth re-writing without a view...
