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);
>From my sample data set (available upon request), this query returns
24 rows in 7.3.6, which is correct. Here's the 7.3.6 EXPLAIN ANALZYE:
Nested Loop (cost=0.00..533.78 rows=24 width=32) (actual time=0.20..0.37 rows=24 loops=1)
-> Index Scan using ipnet_pkey, ipnet_pkey on ipnet n (cost=0.00..6.03 rows=2 width=16) (actual time=0.11..0.12 rows=2 loops=1)
Index Cond: ((netid = 10) OR (netid = 20))
-> Index Scan using ipinterface_ifaddrnet_idx on ipinterface i (cost=0.00..262.58 rows=92 width=16) (actual time=0.06..0.10 rows=12 loops=2)
Index Cond: (inet2net(i.ifaddr) = "outer".netaddr)
Total runtime: 0.52 msec
(6 rows)
The same query in 7.4.2 returns no results. Here's its plan:
Hash Join (cost=6.04..483.92 rows=24 width=30) (actual time=299.948..299.948 rows=0 loops=1)
Hash Cond: (network(set_masklen("outer".ifaddr, 24)) = "inner".netaddr)
-> Seq Scan on ipinterface i (cost=0.00..293.32 rows=18432 width=15) (actual time=0.039..130.604 rows=18432 loops=1)
-> Hash (cost=6.03..6.03 rows=2 width=15) (actual time=0.257..0.257 rows=0 loops=1)
-> Index Scan using ipnet_pkey, ipnet_pkey on ipnet n (cost=0.00..6.03 rows=2 width=15) (actual time=0.142..0.196 rows=2 loops=1)
Index Cond: ((netid = 10) OR (netid = 20))
Total runtime: 300.775 ms
(7 rows)
If I turn off enable_hashjoin in 7.4.2 I get 24 rows, as expected:
Nested Loop (cost=0.00..534.87 rows=24 width=30) (actual time=0.301..1.094 rows=24 loops=1)
-> Index Scan using ipnet_pkey, ipnet_pkey on ipnet n (cost=0.00..6.03 rows=2 width=15) (actual time=0.132..0.180 rows=2 loops=1)
Index Cond: ((netid = 10) OR (netid = 20))
-> Index Scan using ipinterface_ifaddrnet_idx on ipinterface i (cost=0.00..262.81 rows=92 width=15) (actual time=0.088..0.242 rows=12 loops=2)
Index Cond: (network(set_masklen(i.ifaddr, 24)) = "outer".netaddr)
Total runtime: 1.914 ms
(6 rows)
Am I doing something wrong, or should I report this to the bugs
list?
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
