On Tue, Apr 13, 2004 at 03:42:54PM -0400, Tom Lane wrote: > Michael Fuhr <mike@fuhr.org> writes: > > I have a query that works in 7.3.6 but not in 7.4.2 unless I turn > > off enable_hashjoin. I'm joining a table of network interfaces and > > a table of networks so I can find additional info about a particular > > interface's network. > > Hmm. The inet = operator is marked hashable in 7.4 but not in 7.3 ... > I wonder if that is a mistake? I recall looking at the datatype and > deciding there were no insignificant bits in it, but that could be > wrong. Or it could be that the network() function is taking some > shortcut it shouldn't. So would a workaround be to set oprcanhash to false for that operator? I did the following and it appeared to solve the problem: UPDATE pg_operator SET oprcanhash = FALSE WHERE oid = 1201; Or, without knowing that 1201 is the correct OID: UPDATE pg_operator SET oprcanhash = FALSE WHERE oprname = '=' AND oprleft IN (SELECT oid FROM pg_type WHERE typname = 'inet'); > Is any of this data IPv6 addresses by any chance? Nope -- all IPv4. > > From my sample data set (available upon request), > > Could we see the specific values that join in 7.3 and fail to do so in > 7.4? I can duplicate the problem with the following data: INSERT INTO ipinterface VALUES (1, '10.0.1.1'); INSERT INTO ipinterface VALUES (2, '10.0.2.1'); INSERT INTO ipnet VALUES (10, '10.0.1.0/24'); INSERT INTO ipnet VALUES (20, '10.0.2.0/24'); Thanks for looking into this. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
