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.

"Benjamin Reed" <ranger(at)opennms(dot)org> writes:
> I've hit a regression vs. PostgreSQL 8.2 and 8.3 (and probably others)
> related to subselects. This query:

It's not going to be possible to examine this with just the query.
You need to provide a self-contained test case.

regards, tom lane

On 7/22/09 4:39 PM, Tom Lane wrote:
> "Benjamin Reed" <ranger(at)opennms(dot)org> writes:
>> I've hit a regression vs. PostgreSQL 8.2 and 8.3 (and probably others)
>> related to subselects. This query:
>
> It's not going to be possible to examine this with just the query.
> You need to provide a self-contained test case.

Attached is a test case, including the query that causes the error at
the end. On 8.3, it returns 1 row (192.168.1.1), on 8.4 including the
git 8.4 branch, it returns none.

--
Benjamin Reed
The OpenNMS Group
http://www.opennms.org/

Benjamin Reed <ranger(at)opennms(dot)org> writes:
> Attached is a test case, including the query that causes the error at
> the end. On 8.3, it returns 1 row (192.168.1.1), on 8.4 including the
> git 8.4 branch, it returns none.

Thanks, it seems like the problem is that it's applying *both* its
methods for implementing an IN join: it's unique-ifying the sub-select
output via a HashAggregate, and then using a Semi Join anyway when that
gets joined to the "node" table. And the Semi Join has indeterminate
output for some of the other output columns. (The join order it's
choosing seems a bit odd too, but with so few rows in the tables it may
be that all the join orders seem to have the same cost.) I think this
is probably a small fix, but running out of energy for tonight ...

regards, tom lane

Benjamin Reed <ranger(at)opennms(dot)org> writes:
> Attached is a test case, including the query that causes the error at
> the end. On 8.3, it returns 1 row (192.168.1.1), on 8.4 including the
> git 8.4 branch, it returns none.

Thanks for the test case. This patch should fix it.

regards, tom lane