Message-ID: <7d5e8038-0417-2d5b-3db7-c96106bd4cb0@gmail.com>
Date: Wed, 11 Jan 2023 11:51:06 +0530
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.4.2
Subject: Re: Todo: Teach planner to evaluate multiple windows in the optimal
 order
To: David Rowley <dgrowleyml@gmail.com>
Cc: pghackers <pgsql-hackers@lists.postgresql.org>,
 Vik Fearing <vik@postgresfriends.org>
References: <83d80853-a45c-d85c-68eb-59acfe7fb5fb@gmail.com>
 <a58a04a9-2dd6-7290-35c5-e3e23f326b85@gmail.com>
 <01248493-182b-0f92-f2f2-ff28dc4b2e83@gmail.com>
 <CAApHDvpEYcQ7d-c6Lje26gLi4dXOM3qQ9nrTe1Z241tT8nBiCg@mail.gmail.com>
 <c1d1aace-ce9f-9ec3-1984-5319920b1594@gmail.com>
 <CAApHDvp8BpUEo_kQdGHWNPCjcmRWCdiy5p26SoQA4R6rinkaLA@mail.gmail.com>
 <2040c902-32b1-46c8-0b26-d3f43a16b9bb@gmail.com>
 <12f6ef19-e06e-8ea9-ad98-3fd92afb7f0b@gmail.com>
 <CAApHDvp=YnmEQ8Ceu5g4wPL-b5YKmhQ7A3Lya6x=6ga=LLHMFA@mail.gmail.com>
 <8d0b24ce-f8ba-cd63-82ae-be853b4067e8@gmail.com>
 <CAApHDvpigtPVJOJ4EmWOAODtPTccz=L_gdPXDVgG2Z=iSPfNmQ@mail.gmail.com>
 <b1df021b-e6c0-f26a-1d5a-bd07f765db62@gmail.com>
 <CAApHDvqVjeHg6UgeCvFOa-uCaEFix9C_3YaT2qxsMtjN4oKbNQ@mail.gmail.com>
 <4ef7f947-526b-c4ec-f7a0-8b4586e693e4@gmail.com>
 <CAApHDvo2y9S2AO-BPYo7gMPYD0XE2Lo-KFLnqX80fcftqBCcyw@mail.gmail.com>
 <2f9f632d-3297-09dd-1d01-af3e0961c446@gmail.com>
 <CAApHDvowcPrNY3kgAPhbFL0nZ1fCvUBcp3y1AjLm0TUP5N3cxw@mail.gmail.com>
Content-Language: en-US
From: Ankit Kumar Pandey <itsankitkp@gmail.com>
In-Reply-To: <CAApHDvowcPrNY3kgAPhbFL0nZ1fCvUBcp3y1AjLm0TUP5N3cxw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/7d5e8038-0417-2d5b-3db7-c96106bd4cb0%40gmail.com>
Precedence: bulk

> On 11/01/23 06:18, David Rowley wrote:

>
> Not sure if we should be trying to improve that in this patch. I just
> wanted to identify it as something else that perhaps could be done.

This could be within reach but still original problem of having hashagg 
removing

any gains from this remains.


eg

set enable_hashagg=0;

explain select distinct relkind, relname, count(*) over (partition by
relkind) from pg_Class;
                                      QUERY PLAN
------------------------------------------------------------------------------------
  Unique  (cost=41.26..65.32 rows=412 width=73)
    ->  Incremental Sort  (cost=41.26..62.23 rows=412 width=73)
          Sort Key: relkind, relname, (count(*) OVER (?))
          Presorted Key: relkind
          ->  WindowAgg  (cost=36.01..43.22 rows=412 width=73)
                ->  Sort  (cost=36.01..37.04 rows=412 width=65)
                      Sort Key: relkind
                      ->  Seq Scan on pg_class  (cost=0.00..18.12 rows=412 width=65)
(8 rows)

reset enable_hashagg;
explain select distinct relkind, relname, count(*) over (partition by
relkind) from pg_Class;
                                   QUERY PLAN
------------------------------------------------------------------------------
  HashAggregate  (cost=46.31..50.43 rows=412 width=73)
    Group Key: relkind, relname, count(*) OVER (?)
    ->  WindowAgg  (cost=36.01..43.22 rows=412 width=73)
          ->  Sort  (cost=36.01..37.04 rows=412 width=65)
                Sort Key: relkind
                ->  Seq Scan on pg_class  (cost=0.00..18.12 rows=412 width=65)
(6 rows)

HashAgg has better cost than Unique even with incremental sort (tried 
with other case

where we have more columns pushed down but still hashAgg wins).

explain select distinct a, b, count(*) over (partition by a order by b) from abcd;
                                       QUERY PLAN
--------------------------------------------------------------------------------------
  Unique  (cost=345712.12..400370.25 rows=1595 width=16)
    ->  Incremental Sort  (cost=345712.12..395456.14 rows=655214 width=16)
          Sort Key: a, b, (count(*) OVER (?))
          Presorted Key: a, b
          ->  WindowAgg  (cost=345686.08..358790.36 rows=655214 width=16)
                ->  Sort  (cost=345686.08..347324.11 rows=655214 width=8)
                      Sort Key: a, b
                      ->  Seq Scan on abcd  (cost=0.00..273427.14 rows=655214 width=8)

explain select distinct a, b, count(*) over (partition by a order by b) from abcd;

                                    QUERY PLAN

--------------------------------------------------------------------------------

  HashAggregate  (cost=363704.46..363720.41 rows=1595 width=16)

    Group Key: a, b, count(*) OVER (?)

    ->  WindowAgg  (cost=345686.08..358790.36 rows=655214 width=16)

          ->  Sort  (cost=345686.08..347324.11 rows=655214 width=8)

                Sort Key: a, b

                ->  Seq Scan on abcd  (cost=0.00..273427.14 rows=655214 width=8)

(6 rows)


> I'm not really all that sure the above query shape makes much sense in
> the real world. Would anyone ever want to use DISTINCT on some results
> containing WindowFuncs?

This could still have been good to have if there were no negative impact

and some benefit in few cases but as mentioned before, if hashagg removes

any sort (which happened due to push down), all gains will be lost

and we will be probably worse off than before.

-- 
Regards,
Ankit Kumar Pandey