After sourcing ...../postgresql/content-revision.sql in older OpenACS installations, the performance of queries involving the content repository can improve drastically. This is due to the fact that probably an update script was omitted ages ago in the history of OpenACS. Note that the stored procedures executed in the database are not necessarily the same as in the .sql files if one upgrades an old database. This situations is certainly a pain do debug. Apparently the old revision-specific stored procedures had some performance flaws.
For example, the query
select * from xowiki_pagei p, cr_items ci ,xowiki_tags tags where ci.parent_id =363021 and ci.item_id = p.item_id and ci.live_revision = p.page_id and tags.item_id = ci.item_id and tags.tag = 'COMET';
on the database of openacs.org was running after sourcing the actual stored procedures faster by a factor of 700 (before 2483.00 ms, after 3.20 ms)
The fix for this will be added to the upgrade scripts of 5.3. So if you are planing to upgrade to 5.3 soon, there is no action required.