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);
Fri, 24 Sep 2004 22:03:36 -0400
Rod Taylor <pg@rbt.ca>
Possible Optimization?