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)