I 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? Digging further, I find that indeed this seems to be a mistake. CIDR and INET values that have the same address and masklen compare as equal according to network_eq(), but they will not hash the same because there's a flag identifying whether a given value is considered CIDR or INET. And what the network() function returns is marked as a CIDR. It's a bit surprising that your hash join produces any matches at all... I believe I got misled on this because there is a hash index operator class for inet; at one point during the 7.4 cycle I went around and cleaned up cases where the equality operator's canhash flag was inconsistent with the set of hash index opclasses. Arguably the hash opclass is broken, although in practice people probably don't notice the failure since a given column is likely to contain either all inet or all cidr values. (And of course it's entirely likely that there *aren't* any people using the inet hash opclass, period...) I can think of a number of possible fixes: 1. Mark inet = as not hashjoinable. We'd probably want to remove the inet hash opclass too. 2. Redefine inet = so that CIDR and INET values are never considered equal, thus eliminating the unused field. This could be back-patched into 7.4 but otherwise seems to have little to recommend it. It would certainly not help solve Michael's problem. 3. Provide a specialized hash method for type inet that ignores the iptype field. #3 seems the most desirable going forward, but is probably impractical to back-patch into 7.4.*, so I'm not sure what to do about the problem in that branch. Given the relatively low incidence of the problem, maybe it's okay to just clear the oprcanhash flag in future 7.4.* releases. This would not fix the problem for existing installations (unless they initdb) but any complainers could be told how to adjust their catalogs manually. Can anyone think of any other approaches? regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
