Erronous sort used in query plan

I am putting together searches on the catalog info and came up with a
select that was rather slow and I noticed that in the explain analyze
there is a sort step on one of the left joins which I don't think
belongs there.

I found the small error in my query (using tl.oid instead of tr.oid and
tres.oid) that caused the query to slow down and generate the sort in
the plan but am not sure that the given condition should even generate a
sort step and if it does then I believe it should be a (more?) stable
decision.

Removing one of the left join's that is in error (tr or tres) changes
the column that is sorted, neither of which is related to the join/s
that appear to generate the step.

With tl, tr and tres in place the sort is performed on pjoin.oid.

Removing or correcting either tr or tres the sort is changed to perform
on olsort.oid.

Removing or correcting both tr and tres removes the sort from the plan.

Also - removing all the pg_operator joins the sort is still there (on
pjoin.oid) but if I remove one of the erroneous joins as well the sort
goes. (correcting one of the joins leaves the sort there but removing it
removes the sort)

Using postgres 8.2.0 on Mac OSX 10.4.8

The full query is -

explain analyze
SELECT
o.oid as "OID"
, n.nspname as "Schema"
, o.oprname as "Name"
, r.rolname as "Owner"
, CASE WHEN o.oprkind='b' THEN 'infix(left and right)'
WHEN o.oprkind='l' THEN 'prefix (left)'
WHEN o.oprkind='r' THEN 'postfix (right)'
END as "Kind"
, CASE WHEN o.oprcanhash='t' THEN 'Yes'
WHEN o.oprcanhash='f' THEN 'No' END as "Supports Hash Joins"
, tl.typname as "Left Operand"
, tr.typname as "Right Operand"
, tres.typname as "Result Type"
, ocom.oprname as "Commutator Operator"
, onegate.oprname as "Negator Operator"
, olsort.oprname as "Left Sort Operator"
, orsort.oprname as "Right Sort Operator"
, oltcm.oprname as "Less Than Operator"
, ogtcm.oprname as "Greater Than Operator"
, pcode.proname as "Operator Function"
, prest.proname as "Restriction Selectivity Function"
, pjoin.proname as "Join Selectivity Function"

FROM pg_catalog.pg_operator o
left join pg_catalog.pg_namespace n ON n.oid = o.oprnamespace
left join pg_catalog.pg_roles r on r.oid=o.oprowner
left join pg_catalog.pg_type tl on tl.oid=o.oprleft
left join pg_catalog.pg_type tr on tl.oid=o.oprright
left join pg_catalog.pg_type tres on tl.oid=o.oprresult
left join pg_catalog.pg_operator ocom on ocom.oid=o.oprcom
left join pg_catalog.pg_operator onegate on onegate.oid=o.oprnegate
left join pg_catalog.pg_operator oneg on oneg.oid=o.oprnegate
left join pg_catalog.pg_operator olsort on olsort.oid=o.oprlsortop
left join pg_catalog.pg_operator orsort on orsort.oid=o.oprrsortop
left join pg_catalog.pg_operator oltcm on oltcm.oid=o.oprltcmpop
left join pg_catalog.pg_operator ogtcm on ogtcm.oid=o.oprgtcmpop
left join pg_catalog.pg_proc pcode on pcode.oid=o.oprcode
left join pg_catalog.pg_proc prest on prest.oid=o.oprrest
left join pg_catalog.pg_proc pjoin on pjoin.oid=o.oprjoin

WHERE n.nspname like 'public'

I have attached a copy of the query and plan.
--

Shane Ambler
pgSQL(at)007Marketing(dot)com

Get Sheeky @ http://Sheeky.Biz