Forum OpenACS Development: Tuning selects with tree_sortkey

Hello!

We are tuning our installation (OpenACS 5.1 with dot
and Postgres 7.4.8 and many users)
and we are detected one slowly sql select like this:

select r.package_id,
r.folder_id as root_folder_id
from fs_root_folders r,
(select parent.item_id as folder_id
from cr_items parent,
cr_items children
where children.item_id = '7023982'
and children.tree_sortkey
between parent.tree_sortkey
and tree_right(parent.tree_sortkey)) t
where r.folder_id = t.folder_id;

The explain of this select reveals that between clause
do not use index (tree_sortkey). We are rewrite that
select like:

select r.package_id,
r.folder_id as root_folder_id
from fs_root_folders r,
(select parent.item_id as folder_id
from cr_items parent,
(select tree_ancestor_keys ((select tree_sortkey
from cr_items children
where item_id = '7023982')) as tree_sortkey)
skparents
where parent.tree_sortkey = skparents.tree_sortkey) t
where r.folder_id = t.folder_id;

Now the predicate is indexable and the result is fastest
(10x) than the original clause.

The questions are:

-Is the second select equivalent to first?
-Must we rewrite all conditions with "tree_sortkey between"
for use the index? This kind of clause is expanded around
all the OpenACS / dotLearn code.

Best regards,
Agustin

Collapse
Posted by Don Baccus on
PG normally uses the available index for BETWEEN expression, so I'm more interested in why it isn't in this case than I am in evaluating the rewritten query.

When was the last time you folks did an ANALYZE on the database? You should be doing it via a scheduled proc nightly, if you're not.

Sometimes PG guesses wrong and fails to use an index when it should.

But often it's because the statistics aren't up to date and it doesn't "know" that the tables have grown large.