public inbox for [email protected]  

help / color / mirror / Atom feed

From: Ankit Kumar Pandey <[email protected]>
To: David Rowley <[email protected]>
Cc: pghackers <[email protected]>
Cc: Vik Fearing <[email protected]>
Subject: Re: Todo: Teach planner to evaluate multiple windows in the optimal order
Date: Fri, 13 Jan 2023 11:06:19 +0530
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAApHDvr7rSCVXzGfVa1L9pLpkKj6-s8NynK8o+98X9sKjejnQQ@mail.gmail.com>
References: <[email protected]>
	<[email protected]>
	<CAApHDvp8BpUEo_kQdGHWNPCjcmRWCdiy5p26SoQA4R6rinkaLA@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<CAApHDvp=YnmEQ8Ceu5g4wPL-b5YKmhQ7A3Lya6x=6ga=LLHMFA@mail.gmail.com>
	<[email protected]>
	<CAApHDvpigtPVJOJ4EmWOAODtPTccz=L_gdPXDVgG2Z=iSPfNmQ@mail.gmail.com>
	<[email protected]>
	<CAApHDvqVjeHg6UgeCvFOa-uCaEFix9C_3YaT2qxsMtjN4oKbNQ@mail.gmail.com>
	<[email protected]>
	<CAApHDvo2y9S2AO-BPYo7gMPYD0XE2Lo-KFLnqX80fcftqBCcyw@mail.gmail.com>
	<[email protected]>
	<CAApHDvowcPrNY3kgAPhbFL0nZ1fCvUBcp3y1AjLm0TUP5N3cxw@mail.gmail.com>
	<[email protected]>
	<CAApHDvr7rSCVXzGfVa1L9pLpkKj6-s8NynK8o+98X9sKjejnQQ@mail.gmail.com>


> On 13/01/23 07:48, David Rowley wrote:

> I don't think you can claim that one so easily.  The two should have
> quite different scaling characteristics which will be more evident
> with a larger number of input rows. Also, Hash Aggregate makes use of
> work_mem * hash_mem_multiplier, whereas sort uses work_mem.  Consider
> a hash_mem_multiplier less than 1.0.

In this case, it would make sense to do push down. I will do testing 
around large data to see it myself.


> We could consider adjusting the create_distinct_paths() so that it
> uses some newly invented and less strict pathkey comparison where the
> order of the pathkeys does not matter. It would just care if the
> pathkeys were present and return a list of pathkeys not contained so
> that an incremental sort could be done only on the returned list and a
> Unique on an empty returned list.  Something like that might be able
> to apply in more cases, for example:

> select distinct b,a from ab where a < 10;

> the distinct pathkeys would be b,a but if there's an index on (a),
> then we might have a path with pathkeys containing "a".

This would be a very good improvement.

> even if they're not costed quite as
> accurately as we might have liked

This is very exciting piece actually. Once current set of optimizations 
gets ahead,

I will be giving this a shot. We need to look at cost models for sorting.


> We might also want to also consider if Pathkey.pk_strategy and
> pk_nulls_first need to be compared too.  That makes the check a bit
> more expensive as Pathkeys are canonical and if those fields vary then
> we need to perform more than just a comparison by the memory address
> of the pathkey.
  
> This very much seems like a separate effort than the
> WindowClause sort reduction work. I think it gives us everything we've
> talked about extra we might want out of reducing WindowClause sorts
> and more.

I will work on this as separate patch (against HEAD). It makes much more

sense to look this as distinct sort related optimizations (which window 
sort optimization

can take benefit). We may take a call to combine them or apply in series.

For unit of work perspective, I would prefer later.


Anyways, the forthcoming patch will contain the following:

1. Modify create_distinct_paths with newly invented and less strict 
pathkey comparison where the

order of the pathkeys does not matter.

2. Handle Pathkey.pk_strategy and pk_nulls comparison.

3. Test cases


Thanks


Regards,

Ankit Kumar Pandey

view thread (60+ messages)  latest in thread

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]
  Subject: Re: Todo: Teach planner to evaluate multiple windows in the optimal order
  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