Re: disfavoring unparameterized nested loops - Mailing list pgsql-hackers

From Lepikhov Andrei
Subject Re: disfavoring unparameterized nested loops
Date
Msg-id db387e04-beb5-4454-8ad7-e358d3308e12@app.fastmail.com
Whole thread Raw
In response to Re: disfavoring unparameterized nested loops  (David Rowley <dgrowleyml@gmail.com>)
List pgsql-hackers

On Wed, Sep 20, 2023, at 4:49 PM, David Rowley wrote:
> On Wed, 20 Sept 2023 at 19:56, Andrey Lepikhov
> <a.lepikhov@postgrespro.ru> wrote:
>> What could you say about a different way: hybrid join? In MS SQL Server,
>> they have such a feature [1], and, according to their description, it
>> requires low overhead. They start from HashJoin and switch to NestLoop
>> if the inner input contains too small tuples. It solves the issue, Isn't it?
>
> A complexity which you may not be considering here is that Nested Loop
> joins always preserve the tuple order from the outer side of the join,
> whereas hash joins will not do this when multi-batching.

My idea here is the same as MS SQL guys did: prefetch from the HashJoin inner some predefined number of tuples and, if
theplanner has made a mistake and overestimated it, move hash join inner to NestLoop as an outer.
 
The opposite strategy, "underestimation" - starting with NestLoop and switching to HashJoin looks more difficult, but
themain question is: is it worthwhile to research?
 

> I've no idea how the SQL Server engineers solved that.

>> [1]
>> https://techcommunity.microsoft.com/t5/sql-server-blog/introducing-batch-mode-adaptive-joins/ba-p/385411

-- 
Regards,
Andrei Lepikhov



pgsql-hackers by date:

Previous
From: Alvaro Herrera
Date:
Subject: Re: Dump-restore loosing 'attnotnull' bit for DEFERRABLE PRIMARY KEY column(s).
Next
From: Damir
Date:
Subject: Re: POC PATCH: copy from ... exceptions to: (was Re: VLDB Features)