Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pEYBm-0004an-Ji for pgsql-hackers@arkaria.postgresql.org; Sun, 08 Jan 2023 16:06:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pEYBj-0004Fk-VA for pgsql-hackers@arkaria.postgresql.org; Sun, 08 Jan 2023 16:06:23 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pEYBj-0004Bi-M3 for pgsql-hackers@lists.postgresql.org; Sun, 08 Jan 2023 16:06:23 +0000 Received: from relay2-d.mail.gandi.net ([2001:4b98:dc4:8::222]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pEYBe-00071M-0s for pgsql-hackers@lists.postgresql.org; Sun, 08 Jan 2023 16:06:22 +0000 Received: (Authenticated sender: vik@postgresfriends.org) by mail.gandi.net (Postfix) with ESMTPSA id 3A6D340002; Sun, 8 Jan 2023 16:06:14 +0000 (UTC) Message-ID: <600c351a-ba63-8264-d51a-5d10bf966a52@postgresfriends.org> Date: Sun, 8 Jan 2023 17:06:13 +0100 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: Ankit Kumar Pandey , David Rowley Cc: pghackers References: <83d80853-a45c-d85c-68eb-59acfe7fb5fb@gmail.com> <7f77ee7d-bd04-d8e2-bb34-42395fd1f7c2@gmail.com> <6ebcc137-45ea-6373-e3e1-3c304c452e1f@gmail.com> <441d135e-1941-c3ef-1649-18c3e8811549@postgresfriends.org> <9620d994-b89a-2dcf-fca5-821e19d56858@gmail.com> <01248493-182b-0f92-f2f2-ff28dc4b2e83@gmail.com> <2040c902-32b1-46c8-0b26-d3f43a16b9bb@gmail.com> <12f6ef19-e06e-8ea9-ad98-3fd92afb7f0b@gmail.com> From: Vik Fearing Content-Language: en-US In-Reply-To: <12f6ef19-e06e-8ea9-ad98-3fd92afb7f0b@gmail.com> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 1/8/23 11:21, Ankit Kumar Pandey wrote: > > Please find attached patch with addressed issues mentioned before. I am curious about this plan: +-- ORDER BY's in multiple Window functions can be combined into one +-- if they are subset of QUERY's ORDER BY +EXPLAIN (COSTS OFF) +SELECT empno, + depname, + min(salary) OVER (PARTITION BY depname ORDER BY empno) depminsalary, + sum(salary) OVER (PARTITION BY depname) depsalary, + count(*) OVER (ORDER BY enroll_date DESC) c +FROM empsalary +ORDER BY depname, empno, enroll_date; + QUERY PLAN +------------------------------------------------------ + WindowAgg + -> WindowAgg + -> Sort + Sort Key: depname, empno, enroll_date + -> WindowAgg + -> Sort + Sort Key: enroll_date DESC + -> Seq Scan on empsalary +(8 rows) + Why aren't min() and sum() calculated on the same WindowAgg run? -- Vik Fearing