Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fYukl-0005Nb-RG for pgsql-hackers@arkaria.postgresql.org; Fri, 29 Jun 2018 14:52:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fYukj-0005Xp-G6 for pgsql-hackers@arkaria.postgresql.org; Fri, 29 Jun 2018 14:52:01 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fYukj-0005Xh-5w for pgsql-hackers@lists.postgresql.org; Fri, 29 Jun 2018 14:52:01 +0000 Received: from hook.sigaev.ru ([93.180.27.50]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fYukg-0007Hk-FX for pgsql-hackers@postgresql.org; Fri, 29 Jun 2018 14:51:59 +0000 Received: from xor.sai.msu.ru (unknown [93.174.131.141]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (No client certificate requested) (Authenticated sender: teodor) by hook.sigaev.ru (Postfix) with ESMTPSA id 8A606B191BE; Fri, 29 Jun 2018 17:51:56 +0300 (MSK) Subject: Re: POC: GROUP BY optimization To: Tomas Vondra , Pgsql Hackers References: <7c79e6a5-8597-74e8-0671-1c39d124c9d6@sigaev.ru> <97d148b7-f3e7-1c39-07fd-74cbc66f6ec9@sigaev.ru> <6d1e0cdb-dde3-f62a-43e2-e90bbd9b0f42@2ndquadrant.com> <7edddb4f-9ddd-561d-2bde-47340f91d2a0@2ndquadrant.com> <2cb99655-7ae7-ec8d-2c74-3fea58987104@sigaev.ru> <6dddcce6-0d91-1868-794a-4d61dc5516f0@2ndquadrant.com> <523d1f60-c60c-5c8c-8cc5-c6c078970da6@2ndquadrant.com> <24c773b8-3a9a-8fe9-610d-01145758931b@sigaev.ru> <623f5e50-eeb2-1279-d30b-dc43611fb063@2ndquadrant.com> <1913e082-0d8d-5eb8-c8ca-053af828076a@2ndquadrant.com> From: Teodor Sigaev Message-ID: <3bbdd1ee-544d-a549-b0c2-30485d9f6a16@sigaev.ru> Date: Fri, 29 Jun 2018 17:51:56 +0300 User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:52.0) Gecko/20100101 Firefox/52.0 SeaMonkey/2.49.3 MIME-Version: 1.0 In-Reply-To: <1913e082-0d8d-5eb8-c8ca-053af828076a@2ndquadrant.com> Content-Type: text/plain; charset=KOI8-R; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk >> I tried to attack the cost_sort() issues and hope on that basis we can solve >> problems with 0002 patch and improve incremental sort patch. >> > > OK, will do. Thanks for working on this! I hope, now we have a better cost_sort(). The obvious way is a try all combination of pathkeys in get_cheapest_group_keys_order() and choose cheapest one by cost_sort(). But it requires N! operations and potentially could be very expensive in case of large number of pathkeys and doesn't solve the issue with user-knows-what-he-does pathkeys. We could suggest an order of pathkeys as patch suggests now and if cost_sort() estimates cost is less than 80% (arbitrary chosen) cost of user-suggested pathkeys then it use our else user pathkeys. -- Teodor Sigaev E-mail: teodor@sigaev.ru WWW: http://www.sigaev.ru/