public inbox for [email protected]
help / color / mirror / Atom feedFrom: "Anitha S" <[email protected]>
To: pgsql-hackers <[email protected]>
Subject: Two Window aggregate node for logically same over clause
Date: Thu, 05 Oct 2023 16:35:08 +0530
Message-ID: <[email protected]> (raw)
Hi team,
We have observed that for logically same over clause two different window aggregate nodes are created in plan.
The below query contains two window functions. Both Over clause contain the same partition & order clause in it. But in one over clause ordering option is mentioned as ascending but not in another over clause which represents the default option "ascending".
Sample Query:
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'),('personnel', 2, 3900, '2006-12-23'),
('develop', 7, 4200, '2008-01-01'),('develop', 9, 4500, '2008-01-01'),('sales', 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 empsalary;
QUERY PLAN
------------------------------------------------------------------------------------------
WindowAgg (cost=10000000074.54..10000000114.66 rows=1070 width=52)
Output: (rank() OVER (?)), percent_rank() OVER (?), salary, depname
-> WindowAgg (cost=10000000074.54..10000000095.94 rows=1070 width=44)
Output: salary, depname, rank() OVER (?)
-> Sort (cost=10000000074.54..10000000077.21 rows=1070 width=36)
Output: salary, depname
Sort Key: empsalary.depname, empsalary.salary
-> Seq Scan on pg_temp_7.empsalary (cost=0.00..20.70 rows=1070 width=36)
Output: salary, depname
Ordering option of Sort is represented by enum SortByDir (parsenodes.h).
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 */
typedef enum SortByDir
{
SORTBY_DEFAULT,
SORTBY_ASC,
SORTBY_DESC,
SORTBY_USING /* not allowed in CREATE INDEX ... */
} SortByDir;
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;
In transformWindowFuncCall API, Equality check of order clause in window definition 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 node for the above results in faster query execution. Is there any reason for creating two different window aggregate node?
Thanks
Anitha S
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]
Subject: Re: Two Window aggregate node for logically same over clause
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