Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qoPoM-001AsF-OG for pgsql-hackers@arkaria.postgresql.org; Thu, 05 Oct 2023 14:58:47 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qoPoK-00EWyN-Jr for pgsql-hackers@arkaria.postgresql.org; Thu, 05 Oct 2023 14:58:45 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qoMAR-00CFTL-RW for pgsql-hackers@lists.postgresql.org; Thu, 05 Oct 2023 11:05:21 +0000 Received: from sender4-zc7.zoho.com ([136.143.188.7] helo=sender4.zoho.com) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qoMAO-0009tf-Qc for pgsql-hackers@postgresql.org; Thu, 05 Oct 2023 11:05:19 +0000 ARC-Seal: i=1; a=rsa-sha256; t=1696503910; cv=none; d=zohomail.com; s=zohoarc; b=ajQdSkzP1O0rhe6OZIfGluzBFSDIpye7lYlPd6cQcR0X4A0wsKxjqD4JzFgp97psT/dCgeJU6ISN+JX2F0JHHsqzpdqKGVAHfOeu/OHacr16vhCECQsm0mJyDZkhqbNiQrFgwEXOoa+rRPCtdjVDMq5JZ5wfc5o9CUPZY3EGye4= ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=zohomail.com; s=zohoarc; t=1696503910; h=Content-Type:Date:Date:From:From:MIME-Version:Message-ID:Subject:Subject:To:To:Message-Id:Reply-To:Cc; bh=4fXr8/wZSVDzgz/90IwVeswxnnD8RP6Z07CA8flpMN8=; b=CYCksNmClc34xSblwOJdRKslM+Lmddu0yq74OLVjba0x3jDcLVFkjvR/uOdTubmv7BCDFZebwmgrFumzuSQfw62vQFn/74PjtAGdqBzo76/MROdYAGVCbF/zsJ544e5UM8MN3EZDPyAHNtuJhvCB3b3DJSMS0uiC23zKe36WBaI= ARC-Authentication-Results: i=1; mx.zohomail.com; dkim=pass header.i=zohocorp.com; spf=pass smtp.mailfrom=anitha.sg@zohocorp.com; dmarc=pass header.from= DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; t=1696503910; s=admin; d=zohocorp.com; i=anitha.sg@zohocorp.com; h=Date:Date:From:From:To:To:Message-Id:Message-Id:Subject:Subject:MIME-Version:Content-Type:Reply-To:Cc; bh=4fXr8/wZSVDzgz/90IwVeswxnnD8RP6Z07CA8flpMN8=; b=QvSAtJvslqqSGWiNJJvw203biT5BmhKGmA+vZVixzprJudfA2dgzxdDDSS+I5ceW Dih7tgyap6EtCDZkb/T6Zdbm8RHpby65HgxTV0HIUkAgjyP/1NxCgzrJyYLuFzXhXzu t86psyB1gabU/fruwKqjKvB5oi/UuKkYLmCNMhv0= Received: from mail.zoho.com by mx.zohomail.com with SMTP id 1696503908190980.6852614445377; Thu, 5 Oct 2023 04:05:08 -0700 (PDT) Date: Thu, 05 Oct 2023 16:35:08 +0530 From: "\"Anitha S\"" To: ""pgsql-hackers"" Message-Id: <18aff834722.c87c2c5e30477.8947587148037482209@zohocorp.com> Subject: Two Window aggregate node for logically same over clause MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_104798_1615160293.1696503908131" Importance: Medium User-Agent: Zoho Mail X-Mailer: Zoho Mail List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_104798_1615160293.1696503908131 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi team, =C2=A0 =C2=A0 =C2=A0 We have observed that for logically same over clause t= wo different window aggregate nodes are created in plan.=C2=A0=C2=A0 The below query contains two window functions. Both Over clause contain the= same partition & order clause in it. But in one over clause ordering optio= n is mentioned as ascending but not in another over clause which represents= the default option "ascending".=20 =C2=A0 Sample Query: =C2=A0 CREATE TEMPORARY TABLE empsalary ( depname varchar, empno bigint, salary int, enroll_date date ); INSERT INTO empsalary VALUES ('develop', 10, 5200, '2007-08-01'), ('sales'= , 1, 5000, '2006-10-01'), ('personnel', 5, 3500, '2007-12-10'),('sales', 4, 4800, '2007-08-08'),('pe= rsonnel', 2, 3900, '2006-12-23'), ('develop', 7, 4200, '2008-01-01'),('develop', 9, 4500, '2008-01-01'),('sa= les', 3, 4800, '2007-08-01'), ('develop', 8, 6000, '2006-10-01'),('develop', 11, 5200, '2007-08-15'); explain verbose select rank() over (partition by depname order by salary),= percent_rank() over(partition by depname order by salary asc) from empsal= ary; =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0QUERY PLAN --------------------------------------------------------------------------= ---------------- =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0WindowAgg (cost=3D10000000074.54..1000000011= 4.66 rows=3D1070 width=3D52) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Output: (rank() OVER (?)), percent_rank() OVE= R (?), salary, depname =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-> WindowAgg (cost=3D10000000074.54..= 10000000095.94 rows=3D1070 width=3D44) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Output: salary,= depname, rank() OVER (?) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-> = Sort (cost=3D10000000074.54..10000000077.21 rows=3D1070 width=3D36) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0Output: salary, depname =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0Sort Key: empsalary.depname, empsalary.salary =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-> Seq Scan on pg_temp_7.empsalary = (cost=3D0.00..20.70 rows=3D1070 width=3D36) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Output: sala= ry, depname=20 Ordering option of Sort is represented by enum SortByDir (parsenodes.h).=20 List of SortBy is present in WindowDef structure which has info of order by= clause in Over clause /* Sort ordering options for ORDER BY and CREATE INDEX */=20 typedef enum SortByDir { SORTBY_DEFAULT, SORTBY_ASC, SORTBY_DESC, SORTBY_USING /* not allowed in CREATE INDEX ... */ } SortByDir; =20 typedef struct SortBy { NodeTag type; Node *node; /* expression to sort on */ SortByDir sortby_dir; /* ASC/DESC/USING/default */ SortByNulls sortby_nulls; /* NULLS FIRST/LAST */ List *useOp; /* name of op to use, if SORTBY_USING */ int location; /* operator location, or -1 if none/unknown */ } SortBy; =20 In transformWindowFuncCall API, Equality check of order clause in window de= finition failed while comparing SortByDir enum of both over clause i.e SORT= _DEFAULT=C2=A0 is not equal to SORT_ASC. Hence two window clause are create= d in parse tree resulting in the creation of two different window aggregate= node. This check can be modified to form a single window aggregate node for the a= bove results in faster query execution. Is there any reason for creating tw= o different window aggregate node? Thanks=C2=A0 Anitha S =C2=A0 ------=_Part_104798_1615160293.1696503908131 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable =


Hi team,

      W= e have observed that for logically same over clause two different window ag= gregate nodes are created in plan.  
The below query contains = two window functions. Both Over clause contain the same partition & ord= er clause in it. But in one over clause ordering option is mentioned as asc= ending but not in another over clause which represents the default option "= ascending".

 
Sample Query:
 
CREATE = TEMPORARY TABLE e= mpsalary (
depname varchar,
empno bigint,
salary int,
enroll_date date
);
<= br>
I= NSERT INTOVALUES ('dev= elop', <= /span>10= , 5200, '2007-08-01'), ('sales', 1, 5000, '2006-10-01'),
('personnel', 5, 3500, '20= 07-12-10'),('sal= es', 4, 4800, '2007-08-08'),('personnel', 2, 3900, '2006-12-23'),
('develop', 7, 4200, '2008-01-01'),(, 9, <= span class=3D"colour" style=3D"color: rgb(181, 206, 168);">4500, '2008-01-01'),('sales', 3, 4800, '2007-08-01'),
('develop', 8, 6000, '2006-10-01'),(, 11<= /span>, = 5200, '2007-08-15');

explain verboseselect= = rank() over (partition by depname order by salary), percent_rank() over(partition byasc) from= empsalary;
            =       QUERY PLAN
----------------------= --------------------------------------------------------------------=
=      WindowAgg (cost=3D10000000074.54..10000000114.66 rows=3D1= 070 widt= h=3D52)
&nb= sp;    Output: (rank() OVER (?)), percent_rank() OVER = (?), salary, depname
      -> Windo= wAgg (cost=3D10000000074...1000000009= 5.94 rows=3D1070 width=3D44)
      =     Output: salary, depname, rank() OVER (?)
       &= nbsp;    -> Sort (cost=3D10000000074.54..10000000077.21 rows=3D1= 070 widt= h=3D36)
&nb= sp;            =    Output: salary, depname
      = ;          Sort Key: empsalary.depname, em= psalary.salary
         =           -> Seq Scan= on pg_temp_7.emp= salary (cost=3D0.00..20.70 rows=3D1070 width=3D36)
         &= nbsp;           &nbs= p; Output: salary, depname=20
=


Ordering option of Sort is represented by enum So= rtByDir (parsenodes.h).

List of SortBy is present in WindowDef stru= cture which has info of order by clause in Over clause

<= /div>
/* Sort ordering options for ORDER BY and CREATE INDEX */
typedef enum SortByDir
{
<= /div>
SORT= BY_DEFAULT SORTBY_ASC,
SORTBY_DESC,
SORTBY_USING /* not allowed in CREATE INDEX ... */=
= } SortBy= Dir; =20
typedef = struct <= /span>SortBy
{
NodeTag type;
Node *= node; /* expressi= on to sort on */
SortByDir sortby_dir; /* ASC/DESC/USING/default */
SortByNulls sortby_nulls; /* NULLS FIRST/LAST */
Li= st *useOp= ; /* name of op to use, = if SORTBY_USING */
int location; /* operator location, or -1 if none/unknown */
= } SortBy; =20


I= n transformWindowFuncCall API, Equality check of order clause in window def= inition failed while comparing SortByDir enum of both over clause i.e SORT_= DEFAULT  is not equal to SORT_ASC. Hence two window clause are created= in parse tree resulting in the creation of two different window aggregate = node.

This check can be modified to form a single window aggregate n= ode for the above results in faster query execution. Is there any reason fo= r creating two different window aggregate node?

Thanks 
Anit= ha S

 



<= br>





------=_Part_104798_1615160293.1696503908131--