Join works in 7.3.6, fails in 7.4.2

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Join works in 7.3.6, fails in 7.4.2
Date: 2004-04-13 18:35:39
Message-ID: 20040413183539.GA75434@winnie.fuhr.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

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/

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Guy Fraser 2004-04-13 18:57:23 Re: COPY TO order
Previous Message Vivek Khera 2004-04-13 17:49:42 Re: REINDEX slow?

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2004-04-13 18:43:00 Re: rotatelogs integration in pg_ctl
Previous Message Tom Lane 2004-04-13 18:27:29 Re: Lexing with different charsets