With a CVS update from this morning, I'm finding that the 3-way join

SELECT p1.oid, p2.oid, p2.oprname, p3.oid, p3.opcname
FROM pg_amop AS p1, pg_operator AS p2, pg_opclass AS p3
WHERE p1.amopopr = p2.oid AND p1.amopclaid = p3.oid;

is only returning a small fraction of the rows that it should.
The problem is obvious from EXPLAIN:

NOTICE:  QUERY PLAN:

Merge Join  (cost=64.12 size=507 width=84)
  ->  Seq Scan on pg_operator p2  (cost=24.73 size=507 width=36)
  ->  Hash Join  (cost=17.34 size=161 width=48)
        ->  Seq Scan on pg_opclass p3  (cost=1.86 size=26 width=36)
        ->  Hash  (cost=0.00 size=0 width=0)
              ->  Seq Scan on pg_amop p1  (cost=7.31 size=161 width=12)

The optimizer seems to have forgotten that Merge Join needs sorted
inputs... either that or it now believes that a sequential scan
will yield sorted data...

			regards, tom lane

Sat, 27 Mar 1999 16:43:04 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Merge joins badly broken in current sources