Forum .LRN Q&A: slow select statement
I have downloaded and installed the latest .LRN 1.0 beta 1, and am using OpenACS 4.6.2 beta 1, and postgres 7.2.1. When I create a class, and then click on Class Home, things run very slowly.
It seems that this is down to a reasonably complex select statement (see end of this message), which on my (debian) machine, when I run under psql, takes a little under 30s to complete. My machine is by no means slow - so if nobody else is seeing this problem, perhaps it is something to do with my installation...
The select statement is:
select file_storage__get_package_id(fs_objects.object_id) as package_id,
(CASE WHEN fs_objects.type = 'url' THEN (select site_node__url(site_nodes.node_id)
from site_nodes where site_nodes.object_id = file_storage__get_package_id(fs_objects.parent_id))
ELSE (select site_node__url(site_nodes.node_id)
from site_nodes where site_nodes.object_id = file_storage__get_package_id(fs_objects.object_id))
END) as url, fs_objects.object_id, fs_objects.file_upload_name, fs_objects.name, fs_objects.live_revision, fs_objects.type,
fs_objects.content_size from fs_objects
where fs_objects.parnt_id in (2368)
and 't' = acs_permission__permission_p(fs_objects.object_id, '290', 'read')
order by fs_objects.parent_id, fs_objects.sort_key, fs_objects.name;
you might want to make a script that vacuums your database automatically daily. If you're making a daily backup, that should of course be made before the vacuum, just in case it screws up your db.
The pg backup script (see documentation) makes also the vacuuming. You might want to check it for an example.
Thanks for the info. Will do.
Many thanks - will do.
/usr/lib/postgresql/bin/do.maintenancewith various options.