Forum OpenACS Development: Re: Very slow query

Collapse
2: Re: Very slow query (response to 1)
Posted by Andrei Popov on

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