Horton

Hashed IN only applied to first encountered IN

David Geier <geidav.pg@gmail.com>

View thread View on postgr.es
From: David Geier <geidav.pg@gmail.com>
To: pgsql-hackers@lists.postgresql.org
Date: 2025-04-01T11:51:18Z
Lists: pgsql-hackers

Attachments

Hi hackers,

The hashed IN optimization is only applied to the first encountered 
ScalarArrayOpExpr during the expression tree traversal in 
convert_saop_to_hashed_saop_walker(). Reason being that the walker 
returns true which aborts the traversal.

This can be exhibited by running a query with two IN statements vs the 
same query with just a single IN statement. The IN statements are 
combined via OR and both statements return no rows to prevent any kind 
of lazy evaluation optimizations. The query with two IN statements is 6x 
slower than the the query with the single IN statement. See the attached 
example.

I've also attached a patch with a fix.

-- 
David Geier
(ServiceNow)

Commits

  1. Fix planner's failure to identify multiple hashable ScalarArrayOpExprs