After importing openacs.org users, forums and messages I discovered that it
took literally several minutes to load the APM's home page. This was due
to a rather stupid subselect of the form:
select count(*)
from content_revisions r
where r.revision_id = content_item.get_latest_revision(item_id)
where item_id comes from the package versions table.
It's stupid in both PG and Oracle because get_latest_revision already
joins the latest_revision_id value from cr_items with the content
revisions table thus a simple check for a null return by get_latest_revision
would be sufficient.
It's *really* stupid in Oracle because Oracle won't use the index on
revision_id when checking NULLs so we get two sequential scans of the
cr_revisions table tucked into that one itty-bitty subquery (itself called
once for each package version in ths system).
Which explains why I hadn't noticed it while working on scalability testing
in PG - PG uses the index because its btree index structure handles NULLs.
Meaning this query only fell apart at a rate of O(log2(R)) rather than O(R)
as in Oracle (R being the number of revision objects in the system).
My solution was to rewrite the subselect using "case" rather than "count(*)"
and also to speed up get_latest_revision by having it check for NULL and
return NULL immediately rather than execute the query (in PG this is
accomplished by declaring the function "isstrict", and the executor won't
even call the function if the argument's NULL, making it REALLY fast!).
