It would appear that region_id = parent_id is not internally converted
to region_id = 1129, despite parent_id being enforced to 1129 at the top
level.
In this case, it makes a difference in performance of about 4 (2 minutes
vs 30 second).
The reason I didn't do this myself upfront, is that parent_id is
calculated by a function which I didn't want to call twice. I've split
the query into 2 parts as a result.
Plans attached from PostgreSQL 7.4.5.
QUERY:
SELECT region_id, region_title
FROM bric_extension.region_in_region
WHERE parent_id = 1129
AND class = (SELECT region_class
FROM region_classes
WHERE "order" >
(SELECT "order"
FROM region
JOIN region_classes
ON (region_class = class)
WHERE region_id = parent_id) -- 1129
ORDER BY "order"
LIMIT 1);