Possible Optimization?

From: Rod Taylor <pg(at)rbt(dot)ca>
To: PostgreSQL Development <pgsql-hackers(at)postgresql(dot)org>
Subject: Possible Optimization?
Date: 2004-09-25 02:03:36
Message-ID: 1096077815.40463.55.camel@jester
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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);

Attachment Content-Type Size
quick.txt text/plain 13.2 KB
slow.txt text/plain 14.8 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2004-09-25 02:24:51 Re: 7.4.5 losing committed transactions
Previous Message Francisco Figueiredo Jr. 2004-09-25 01:43:05 Parse message problem, maybe a bug?