BUG #4934: regression in IN with joins in subselect

The following bug has been logged online:

Bug reference: 4934
Logged by: Benjamin Reed
Email address: ranger(at)opennms(dot)org
PostgreSQL version: 8.4.0
Operating system: Mac OS X 10.5
Description: regression in IN with joins in subselect
Details:

I've hit a regression vs. PostgreSQL 8.2 and 8.3 (and probably others)
related to subselects. This query:

---(snip!)---
SELECT DISTINCT ipInterface.ipAddr FROM ipInterface
JOIN node ON (ipInterface.nodeID = node.nodeID)
JOIN ifServices ON (ipInterface.id = ifServices.ipInterfaceId)
JOIN service ON (ifServices.serviceID = service.serviceID)
WHERE
(
node.nodeID IN (
SELECT category_node.nodeID FROM category_node, categories
WHERE categories.categoryID = category_node.categoryID
AND categories.categoryName = 'IMP_mid'
)
) AND
(
node.nodeID IN (
SELECT category_node.nodeID FROM category_node, categories
WHERE categories.categoryID = category_node.categoryID
AND categories.categoryName = 'DEV_AC'
)
) AND
(
node.nodeID IN (
SELECT category_node.nodeID FROM category_node, categories
WHERE categories.categoryID = category_node.categoryID
AND categories.categoryName = 'OPS_Online'
)
) AND
(node.nodeId = 1) AND
(ipInterface.ipAddr = '192.168.1.1') AND
(service.serviceName = 'ICMP')
LIMIT 1;
---(snip!)---

...passes in PostgreSQL 8.2 and 8.3 (which I have handy to test), but fail
in 8.4.0, as well as current origin/REL8_4_STABLE in git. I reported it in
IRC, and the original hope was that it was related to bug #4906, but since
testing latest git, it appears that is not the case.

The query plan in 8.3 is this:

---(snip!)---
Limit (cost=4.27..76.68 rows=1 width=50)
-> Unique (cost=4.27..76.68 rows=1 width=50)
-> Nested Loop IN Join (cost=4.27..76.68 rows=1 width=50)
-> Nested Loop IN Join (cost=4.27..60.12 rows=1 width=54)
-> Nested Loop IN Join (cost=4.27..43.56 rows=1
width=54)
-> Nested Loop (cost=4.27..26.99 rows=1
width=54)
-> Nested Loop (cost=4.27..18.72 rows=1
width=54)
-> Nested Loop (cost=4.27..17.90
rows=2 width=58)
-> Index Scan using
ipinterface_nodeid_ipaddr_ismanaged_idx on ipinterface (cost=0.00..8.27
rows=1 width=58)
Index Cond: ((1 = nodeid)
AND ((ipaddr)::text = '192.168.1.1'::text))
-> Bitmap Heap Scan on
ifservices (cost=4.27..9.60 rows=2 width=8)
Recheck Cond:
(ipinterface.id = ifservices.ipinterfaceid)
-> Bitmap Index Scan on
ifservicves_ipinterfaceid_idx (cost=0.00..4.27 rows=2 width=0)
Index Cond:
(ipinterface.id = ifservices.ipinterfaceid)
-> Index Scan using pk_serviceid on
service (cost=0.00..0.40 rows=1 width=4)
Index Cond:
(ifservices.serviceid = service.serviceid)
Filter: ((servicename)::text =
'ICMP'::text)
-> Index Scan using node_id_type_idx on
node (cost=0.00..8.27 rows=1 width=4)
Index Cond: (nodeid = 1)
-> Nested Loop (cost=0.00..16.55 rows=1
width=4)
-> Index Scan using category_idx on
categories (cost=0.00..8.27 rows=1 width=4)
Index Cond: ((categoryname)::text =
'IMP_mid'::text)
-> Index Scan using catenode_unique_idx on
category_node (cost=0.00..8.27 rows=1 width=8)
Index Cond:
((public.categories.categoryid = public.category_node.categoryid) AND (1 =
public.category_node.nodeid))
-> Nested Loop (cost=0.00..16.55 rows=1 width=4)
-> Index Scan using category_idx on categories
(cost=0.00..8.27 rows=1 width=4)
Index Cond: ((categoryname)::text =
'OPS_Online'::text)
-> Index Scan using catenode_unique_idx on
category_node (cost=0.00..8.27 rows=1 width=8)
Index Cond: ((public.categories.categoryid
= public.category_node.categoryid) AND (1 = public.category_node.nodeid))
-> Nested Loop (cost=0.00..16.55 rows=1 width=4)
-> Index Scan using category_idx on categories
(cost=0.00..8.27 rows=1 width=4)
Index Cond: ((categoryname)::text =
'DEV_AC'::text)
-> Index Scan using catenode_unique_idx on
category_node (cost=0.00..8.27 rows=1 width=8)
Index Cond: ((public.categories.categoryid =
public.category_node.categoryid) AND (1 = public.category_node.nodeid))
---(snip!)---

...and in the stable branch, this:

---(snip!)---
Limit (cost=16.56..72.51 rows=1 width=50)
-> Unique (cost=16.56..72.51 rows=1 width=50)
-> Nested Loop Semi Join (cost=16.56..72.51 rows=1 width=50)
-> Nested Loop Semi Join (cost=16.56..55.94 rows=1
width=54)
-> Nested Loop (cost=16.56..39.38 rows=1 width=54)
-> Nested Loop Semi Join (cost=16.56..38.57
rows=2 width=58)
-> Index Scan using node_id_type_idx on
node (cost=0.00..8.27 rows=1 width=4)
Index Cond: (nodeid = 1)
-> Nested Loop (cost=16.56..37.16 rows=2
width=62)
-> Nested Loop (cost=16.56..24.85
rows=1 width=62)
-> Index Scan using
ipinterface_nodeid_ipaddr_ismanaged_idx on ipinterface (cost=0.00..8.27
rows=1 width=58)
Index Cond: ((nodeid = 1)
AND ((ipaddr)::text = '192.168.1.1'::text))
-> HashAggregate
(cost=16.56..16.57 rows=1 width=4)
-> Nested Loop
(cost=0.00..16.55 rows=1 width=4)
-> Index Scan
using category_idx on categories (cost=0.00..8.27 rows=1 width=4)
Index Cond:
((categoryname)::text = 'DEV_AC'::text)
-> Index Scan
using catenode_unique_idx on category_node (cost=0.00..8.27 rows=1
width=8)
Index Cond:
((public.category_node.categoryid = public.categories.categoryid) AND
(public.category_node.nodeid = 1))
-> Index Scan using
ifservicves_ipinterfaceid_idx on ifservices (cost=0.00..12.29 rows=2
width=8)
Index Cond:
(ifservices.ipinterfaceid = ipinterface.id)
-> Index Scan using pk_serviceid on service
(cost=0.00..0.39 rows=1 width=4)
Index Cond: (service.serviceid =
ifservices.serviceid)
Filter: ((service.servicename)::text =
'ICMP'::text)
-> Nested Loop (cost=0.00..16.55 rows=1 width=4)
-> Index Scan using category_idx on categories
(cost=0.00..8.27 rows=1 width=4)
Index Cond: ((categoryname)::text =
'IMP_mid'::text)
-> Index Scan using catenode_unique_idx on
category_node (cost=0.00..8.27 rows=1 width=8)
Index Cond:
((public.category_node.categoryid = public.categories.categoryid) AND
(public.category_node.nodeid = 1))
-> Nested Loop (cost=0.00..16.55 rows=1 width=4)
-> Index Scan using category_idx on categories
(cost=0.00..8.27 rows=1 width=4)
Index Cond: ((categoryname)::text =
'OPS_Online'::text)
-> Index Scan using catenode_unique_idx on
category_node (cost=0.00..8.27 rows=1 width=8)
Index Cond: ((public.category_node.categoryid =
public.categories.categoryid) AND (public.category_node.nodeid = 1))
---(snip!)---

If I cut the select down to:

---(snip!)---
SELECT DISTINCT ipInterface.ipAddr FROM ipInterface
JOIN node ON (ipInterface.nodeID = node.nodeID)
JOIN ifServices ON (ipInterface.id = ifServices.ipInterfaceId)
JOIN service ON (ifServices.serviceID = service.serviceID)
WHERE
(node.nodeId = 1) AND
(ipInterface.ipAddr = '192.168.1.1') AND
(service.serviceName = 'ICMP')
LIMIT 1;
---(snip!)---

...it passes, but as soon as I add one of the node.nodeID IN() bits, it
fails. Also, if I change it to hardcode one of the passing node IDs:

---(snip!)---
SELECT DISTINCT ipInterface.ipAddr FROM ipInterface
JOIN node ON (ipInterface.nodeID = node.nodeID)
JOIN ifServices ON (ipInterface.id = ifServices.ipInterfaceId)
JOIN service ON (ifServices.serviceID = service.serviceID)
WHERE
(
node.nodeID IN ( SELECT 1 )
) AND
(node.nodeId = 1) AND
(ipInterface.ipAddr = '192.168.1.1') AND
(service.serviceName = 'ICMP')
LIMIT 1;
---(snip!)---

...it passes, so it appears to be related specifically to the joined
subselect.

If there's anything else you need (database dumps, whatever) to help debug
this, let me know.

Thanks.