Skip site navigation (1) Skip section navigation (2)

Peripheral Links

Header And Logo

PostgreSQL
| The world's most advanced open source database.

Site Navigation

Search archives
  Advanced Search

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

Privacy Policy | PostgreSQL Archives hosted by Command Prompt, Inc. | Designed by tinysofa
Copyright © 1996 – 2008 PostgreSQL Global Development Group