public inbox for [email protected]  

help / color / mirror / Atom feed

From: Lepikhov Andrei <[email protected]>
To: David Rowley <[email protected]>
Cc: Benjamin Coutu <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: Peter Geoghegan <[email protected]>
Subject: Re: disfavoring unparameterized nested loops
Date: Wed, 20 Sep 2023 22:17:46 +0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAApHDvofBE-oG998LnEWO6aOrZxnh11BtSOK9uDpjoFwahZ65A@mail.gmail.com>
References: <[email protected]>
	<[email protected]>
	<CAApHDvofBE-oG998LnEWO6aOrZxnh11BtSOK9uDpjoFwahZ65A@mail.gmail.com>



On Wed, Sep 20, 2023, at 4:49 PM, David Rowley wrote:
> On Wed, 20 Sept 2023 at 19:56, Andrey Lepikhov
> <[email protected]> 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 the planner 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 the main 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/38...

-- 
Regards,
Andrei Lepikhov

view thread (2+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: disfavoring unparameterized nested loops
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox