Thread: Hashed IN only applied to first encountered IN

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)

On Wed, 2 Apr 2025 at 00:51, David Geier <geidav.pg@gmail.com> wrote:
> 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.

> I've also attached a patch with a fix.

Thanks for the report and fix.  On first inspection your patch looks
fine.  I'll have a closer look tomorrow.

David

On Wed, 2 Apr 2025 at 01:31, David Rowley <dgrowleyml@gmail.com> wrote:
>
> On Wed, 2 Apr 2025 at 00:51, David Geier <geidav.pg@gmail.com> wrote:
> > 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.
>
> > I've also attached a patch with a fix.
>
> Thanks for the report and fix.  On first inspection your patch looks
> fine.  I'll have a closer look tomorrow.

I've now pushed and backpatched the relevant portion of this back to v14.

Thanks again.

David

Great!

Thank you!

On 4/2/2025 1:00 AM, David Rowley wrote:
> On Wed, 2 Apr 2025 at 01:31, David Rowley <dgrowleyml@gmail.com> wrote:
>> On Wed, 2 Apr 2025 at 00:51, David Geier <geidav.pg@gmail.com> wrote:
>>> 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.
>>> I've also attached a patch with a fix.
>> Thanks for the report and fix.  On first inspection your patch looks
>> fine.  I'll have a closer look tomorrow.
> I've now pushed and backpatched the relevant portion of this back to v14.
>
> Thanks again.
>
> David