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_MAS KLEN($1, 24));
' LANGUAGE SQL IMMUTABLE;
CREATE TABLE ipinterface (
ifid INTEGER NOT NULL PRIMARY KEY,
ifaddr INET NOT NULL
);
CREATE INDEX ipinterface_ifa ddr_idx ON ipinterface (ifaddr);
CREATE INDEX ipinterface_ifa ddrnet_idx ON ipinterface (inet2net(ifadd r));
CREATE TABLE ipnet (
netid INTEGER NOT NULL PRIMARY KEY,
netaddr INET NOT NULL,
CONSTRAINT uniq_netaddr UNIQUE (netaddr)
);
CREATE INDEX ipnet_netaddr_i dx 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.ifa ddr) = 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.0 3 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_ifa ddrnet_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.ifa ddr) = "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..2 99.948 rows=0 loops=1)
Hash Cond: (network(set_ma sklen("outer".i faddr, 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.0 3 rows=2 width=15) (actual time=0.257..0.2 57 rows=0 loops=1)
-> Index Scan using ipnet_pkey, ipnet_pkey on ipnet n (cost=0.00..6.0 3 rows=2 width=15) (actual time=0.142..0.1 96 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.0 94 rows=24 loops=1)
-> Index Scan using ipnet_pkey, ipnet_pkey on ipnet n (cost=0.00..6.0 3 rows=2 width=15) (actual time=0.132..0.1 80 rows=2 loops=1)
Index Cond: ((netid = 10) OR (netid = 20))
-> Index Scan using ipinterface_ifa ddrnet_idx on ipinterface i (cost=0.00..262 .81 rows=92 width=15) (actual time=0.088..0.2 42 rows=12 loops=2)
Index Cond: (network(set_ma sklen(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
