Forum OpenACS Development: Very slow query

Collapse
1: Very slow query
Posted by Janine Ohmer on 08/22/03 02:49 PM
This query (from the trigger acs_objects_context_id_up_tr, defined in acs-kernel/sql/oracle/acs-objects-create.sql):

  delete from acs_object_context_index
  where object_id in (select object_id
                      from acs_object_contexts
                      where ancestor_id = :old.object_id)
  and ancestor_id in (select ancestor_id
                      from acs_object_contexts
                      where object_id = :old.object_id);

Is very slow on one of our systems, which has almost 2 million rows in the acs_object_context_index table.  It takes about 3 minutes to run.

acs_object_contexts is a view into acs_object_context_index, so this can be simplified a bit by rewriting without that view, but presumably the optimizer is already doing that.

This query *looks* like it should be possible to rewrite it to eliminate the sub-selects, but when I try I end up with my head spinning.  This kind of tuning is not my forte, unfortunately. Suggestions?

Thanks in advance!

Collapse
2: Re: Very slow query (response to 1)
Posted by Andrei Popov on 08/22/03 04:11 PM

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

Collapse
3: Re: Very slow query (response to 1)
Posted by Dirk Gomez on 08/22/03 06:08 PM

Andrei, I think your query is not semantically identical to Janine's query. (Actually I am pretty sure)

I tried temporary tables with amazing success on a loaded system. Can you try Janine?

Firstly create the two temporary tables: 


create global temporary table object_id_tmp
  (object_id number)
  on commit delete rows ;
create global temporary table ancestor_id_tmp
  (ancestor_id number)
  on commit delete rows ;
Then replace the offending SQL query with 

insert into object_id_tmp select object_id
   from acs_object_context_index
   where ancestor_id = :object_id and object_id != ancestor_id;
insert into ancestor_id_tmp select ancestor_id
   from acs_object_context_index aoci2
   where object_id = :object_id and object_id != ancestor_id;

 delete from acs_object_context_index 
  where object_id in (select object_id from object_id_tmp) and ancestor_id in (select ancestor_id from ancestor_id_tmp);

Runs very fast in the lab - loaded data but no concurrent users - and looks correct.

Collapse
8: Re: Very slow query (response to 3)
Posted by Andrei Popov on 08/23/03 04:42 PM

Dirk,

You may be right, but it looked to be the same at first glance. Given that acs_object_context_index is actually 

create table acs_object_context_index (
	object_id	integer not null
                        constraint acs_obj_context_idx_obj_id_fk
			references acs_objects(object_id),
	ancestor_id	integer not null
                        constraint acs_obj_context_idx_anc_id_fk
			references acs_objects(object_id),
	n_generations	integer not null
			constraint acs_obj_context_idx_n_gen_ck
			check (n_generations >= 0),
        constraint acs_object_context_index_pk
	primary key (object_id, ancestor_id)
);
and acs_object_contexts is 
create view acs_object_contexts
as select object_id, ancestor_id, n_generations
   from acs_object_context_index
   where object_id != ancestor_id;
i.e. it is a view at acs_object_context_index where object_id and ancestor_id are not the same, doing a delete on acs_object_context_index for specified object_id/ancestor_id excluding the case when they are the same should do the trick, shouldn't it?

Collapse
4: Re: Very slow query (response to 1)
Posted by Don Baccus on 08/23/03 01:09 AM
I'm surprised Oracle can't do a better job with this one, but it's very very deadly for Postgres (and we execute the same query there.)

Another approach: try looping on acs_object_context_index where ancestor_id = :old.object_id.  This will loop over the object's descendent entries in the denormalized table.  For each descendent, delete from the denormalized table where object_id = the loop variable value and ancestor_id = the old object id.

Make sense?

Collapse
7: Re: Very slow query (response to 4)
Posted by Dirk Gomez on 08/23/03 10:43 AM
Yeah the optimizer seems to take some weird decisions...with Barry's and mine approach the optimizer knows much quicker than one of the two subqueries usually yields very few rows.

I'll use Barry's code for the system I am working on because it doesn't require DDL.

Collapse
5: Re: Very slow query (response to 1)
Posted by Don Baccus on 08/23/03 01:36 AM
Looking more closely the insertion code (for Postgres at least, didn't check Oracle, you can do that) uses the loop approach I outline above.

Try it for the delete and let us know what happens.

Collapse
6: Re: Very slow query (response to 1)
Posted by Barry Books on 08/23/03 02:09 AM
see this post for a fix I've been running in production for a very long time. I've got over 40 million rows in acs_object_context_index
Collapse
9: Re: Very slow query (response to 1)
Posted by Don Baccus on 08/23/03 11:58 PM
Andrei - take a close look at your query, it never deletes any rows!

If object_id = :old.object_id and ancestor_id = :old.object_id, then "object_id <> ancestor_id" always returns false!

If you remove the <> then it's still wrong but I'll leave that to you ...

Barry - your patch is exactly what I had in mind when I suggested that Janine loop over the ancestors and delete the relevant rows within the loop.

Collapse
10: Re: Very slow query (response to 1)
Posted by Don Baccus on 08/25/03 11:42 PM
OK I have munged the upgrade script for both Oracle and Postgres to use Barry's loop approach.  Janine reports that this appears to run much faster, as was claimed.  Cool!  Assuming I managed to make the changes correctly :)
Collapse
11: Re: Very slow query (response to 1)
Posted by Lars Pind on 08/26/03 09:43 AM
Does this fix the "slow object creation" problem which has been discussed in the past?
Collapse
12: Re: Very slow query (response to 1)
Posted by Barry Books on 08/26/03 12:24 PM
The trigger only runs on updates to the context_id so I doubt it unless the creation does an insert then updates the context_id.