Join works in 7.3.6, fails in 7.4.2 - Mailing list pgsql-general

From Michael Fuhr
Subject Join works in 7.3.6, fails in 7.4.2
Date
Msg-id 20040413183539.GA75434@winnie.fuhr.org
Whole thread Raw
Responses Re: Join works in 7.3.6, fails in 7.4.2
List pgsql-general
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=2loops=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.10rows=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.196rows=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=2loops=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.242rows=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/

pgsql-general by date:

Previous
From: Vivek Khera
Date:
Subject: Re: REINDEX slow?
Next
From: Guy Fraser
Date:
Subject: Re: COPY TO order