Forum OpenACS Q&A: tree_leaf_key_to_int
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)