Merge joins badly broken in current sources

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-hackers(at)postgreSQL(dot)org
Subject: Merge joins badly broken in current sources
Date: 1999-03-27 21:43:04
Message-ID: 5405.922570984@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 1999-03-28 02:27:10 Another new regress test
Previous Message Hal Snyder 1999-03-27 19:26:59 Re: [HACKERS] PostgreSQL LOGO (was: Developers Globe (FINAL))