When creating a new acs_object, our system is taking almost 1 minute to generate the object. I ran the query analyzer and narrowed it down to the acs_objects_insert_tr() function. Specifically, the tree_leaf_key_to_int() is relatively slow. Please see the four queries below. The first two look at (1) the speed of retrieving the tree_sortkey from acs_objects and (2) the speed of retrieving the tree_sortkey from acs_objects and performing tree_leaf_key_to_int on tree_sortkey. The last two queries perform the same queries on all rows with the context_id (299218 rows).
Can anyone suggest a way to make tree_leaf_key_to_int faster? Note that creating acs_objects has become incrementally slower as the site has become larger.
We are running OpenACS 4.6.3 and PostgreSQL 7.4.16.
Thanks!
Gilbert
sandbox=# explain analyze select tree_sortkey from acs_objects where context_id = 3020 limit 1;
QUERY PLAN
------------------------------------------------------
Limit (cost=0.00..0.05 rows=1 width=14) (actual time=0.117..0.118 rows=1 loops=1)
-> Seq Scan on acs_objects (cost=0.00..15196.99 rows=309551 width=14) (actual time=0.114..0.114 rows=1 loops=1)
Filter: (context_id = 3020)
Total runtime: 0.140 ms
sandbox=# explain analyze select tree_leaf_key_to_int(tree_sortkey) from acs_objects where context_id = 3020 limit 1;
QUERY PLAN
----------------------------------------------------------
Limit (cost=0.00..0.05 rows=1 width=14) (actual time=0.243..0.245 rows=1 loops=1)
-> Seq Scan on acs_objects (cost=0.00..15970.86 rows=309551 width=14) (actual time=0.238..0.238 rows=1 loops=1)
Filter: (context_id = 3020)
Total runtime: 0.276 ms
(4 rows)
sandbox=# explain analyze select tree_sortkey from acs_objects where context_id = 3020;
QUERY PLAN
-----------------------------------------------------------
Seq Scan on acs_objects (cost=0.00..15196.99 rows=309551 width=14) (actual time=3.041..941.749 rows=299218 loops=1)
Filter: (context_id = 3020)
Total runtime: 1363.428 ms
(3 rows)
sandbox=# explain analyze select tree_leaf_key_to_int(tree_sortkey) from acs_objects where context_id = 3020;
QUERY PLAN
----------------------------------------------------------
Seq Scan on acs_objects (cost=0.00..15970.86 rows=309551 width=14) (actual time=0.219..52441.630 rows=299218 loops=1)
Filter: (context_id = 3020)
Total runtime: 52862.768 ms
(3 rows)
