Merge joins badly broken in current sources

Started by Tom Laneabout 27 years ago1 messageshackers
Jump to latest
#1Tom Lane
tgl@sss.pgh.pa.us

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