Join works in 7.3.6, fails in 7.4.2
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. To speed up the join, I'm indexing the
interface IP addresses using a function that converts the IP address
to its network address; this way the join doesn't have to scan using
the << or >> operator.
Here's a reduced example of what I'm doing:
CREATE FUNCTION inet2net (INET) RETURNS INET AS '
SELECT NETWORK(SET_MASKLEN($1, 24));
' LANGUAGE SQL IMMUTABLE;
CREATE TABLE ipinterface (
ifid INTEGER NOT NULL PRIMARY KEY,
ifaddr INET NOT NULL
);
CREATE INDEX ipinterface_ifaddr_idx ON ipinterface (ifaddr);
CREATE INDEX ipinterface_ifaddrnet_idx ON ipinterface (inet2net(ifaddr));
CREATE TABLE ipnet (
netid INTEGER NOT NULL PRIMARY KEY,
netaddr INET NOT NULL,
CONSTRAINT uniq_netaddr UNIQUE (netaddr)
);
CREATE INDEX ipnet_netaddr_idx ON ipnet (netaddr);
After populating the tables, I ran VACUUM ANALYZE on both of them,
so the planner's statistics should be current.
Here's a query that illustrates the problem:
SELECT ifid, ifaddr, netid, netaddr
FROM ipinterface AS i
JOIN ipnet AS n ON (inet2net(i.ifaddr) = n.netaddr)
WHERE netid IN (10, 20);
Home |
Main Index |
Thread Index