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 1qwIpi-00Axmc-Bg for pgsql-general@arkaria.postgresql.org; Fri, 27 Oct 2023 09:08:46 +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 1qwIph-00DyHG-1i for pgsql-general@arkaria.postgresql.org; Fri, 27 Oct 2023 09:08: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 1qwIpg-00DyH7-L2 for pgsql-general@lists.postgresql.org; Fri, 27 Oct 2023 09:08:44 +0000 Received: from mout.gmx.net ([212.227.15.19]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qwIpd-0034rE-9L for pgsql-general@lists.postgresql.org; Fri, 27 Oct 2023 09:08:44 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=gmx.net; s=s31663417; t=1698397719; x=1699002519; i=shammat@gmx.net; bh=ExzfnfXVUcgHyrHc54u3v6v61KJLr6vNGoqm9tEKmb0=; h=X-UI-Sender-Class:Date:From:Subject:To:References:In-Reply-To; b=SP7m7AaEmVXcl5pJViHLF47wEJ6fi38aZpTn8gjLAMdP06+oVA5xHRAUSBnlOehT ITQHMymumqEOeeRfSjENX38PeM8Pxn7YdNJaxVt8g4vIVaeU9YSDKoqOElQHWOzwR zgrLZVMRKqnw6jTRKc2i5OexMUqHnQlXKSXx6PH7z7Ojdw0UNNuMtCT8UbmYELeb0 fcUMilFjpqr0C0CbyVLE8xuOk/JzU6LqW5X5ahhvYiVNdN07I2gx0v2yeAsQlagmy Kl61gsiV4QJsPbIrvnRU/+1LT8oDbmYX+cqBhPdmszs7x/ns3/JTfSrKvHngPTGdE sjFWUCy3utVPVMEwZQ== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from [10.1.33.136] ([185.40.248.10]) by mail.gmx.net (mrgmx005 [212.227.17.190]) with ESMTPSA (Nemesis) id 1MzQkE-1rikgJ34SG-00vKhU for ; Fri, 27 Oct 2023 11:08:39 +0200 Message-ID: Date: Fri, 27 Oct 2023 11:08:39 +0200 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird From: Thomas Kellerer Subject: Re: Correct way of using complex expressions as partitioning key To: pgsql-general@lists.postgresql.org References: <20D8B5BB-DB0B-400D-8560-2F5765361CB1@rumyantsev.com> Content-Language: de-DE In-Reply-To: <20D8B5BB-DB0B-400D-8560-2F5765361CB1@rumyantsev.com> Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Provags-ID: V03:K1:qfDHt2CqAPpk7brFyXGFxi+Tjh78xDsq//ce9XyDKCKdMC1BvB2 F6ze8SL7RXY17e1aN1A7lf4a8ZCnHBRvvLzCpg4doG2veb7iwRgchu1WqWph+CZjdE3iZvP GEWzAyVf1t9xMqcC1JvjD5vnjsf0e4KZTzHX2v0avqB16gQGAv6jwaHi00Stb7DxB3VnMMw Fy8+fhsiIdbY4SCv09WrA== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:uUYSISHHXF0=;cA1CZYyl00sMzCxQpisZcPMV25c fkcYqg0czW0iNDEjI141JvHSqHOnMG8zXDHY8kKDe2zyN7ZFCIVUnNOnyzl0ky1RPXdXuuDjB VMRrLuQ49mGBRCNZXrw6Ed3oV/kH/N5VtACX4OW21hxhH9GSGH73RHOUukUgrAig70PlMy5tp Zq7tXQZDv1yWro//qSuxcWUKtl2wzrniPqqY18FTLd6wVjPe5BjYGjqzr0kC9ohUl6Nbb5iKR AM6jFSX6NosuTOQZMsCoB2lz90HZR3S6jO7IARYipQ0lSevhK4aLcRit5hCDSsnsAzBykSoy8 NY3wlLUmnRMvzKMxSnHZbu9nOsDqwS4OBE2E/3WQM0fYxkgsEktIegm0IJWIBzdi7NILXlYx+ CU/VHeqJZpgJZr+idFyKGQhpPM3jY0AiWjI9qVRlvFq31X5Yz2g2NqSKMp7jO9e4PPZS9TC05 6G7VjuCPlTwW+eY4IsiQzva7GIv6aeLyD8M5VX5CY7J1Lx40JvnytmkTbIaJe07XFKwjDJ0aW 8ZUe/ApzkNFvEIkLE8a335VRcf6nD0qdFxUG+/D/8O8hBpiDwfS3ZnA1TGc185XlfleaZJtBG HQJP+EMbWjCwOq+E+koeBPqsWX/S8J30Tt6ipXmmRpKaT8p21tonbYpyAoebM+SNTRkmq0qFO JFtkHwZsr6GCNTWBekYEFY3fQ6fuU0lL8s4gw8h7Ndy8yoPs+0TSqzdTSj0x/dJ+t8ATIshXB wyU428moeSMi23va7rFKeA9ZO1IoxfabEK5Ca1j9eYEZnAMCIHWhSSBLyaX7cbmvzkscUPuyB H6 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Alexander Rumyantsev schrieb am 27.10.2023 um 06:27: > Hello! > > Is there some correct way to use complex expressions as a key for partit= ioned table? > Inserting works as expected, but select runs over all partitions until u= se complete partition key expression as predicate > > test=3D# create table test ( > id text, > v1 bigint, > v2 bigint > ) > partition by range (((v1 + v2) % 10)); > CREATE TABLE > > test=3D# create table test_1 partition of test for values from (0) to (1= ); > CREATE TABLE > > test=3D# create table test_2 partition of test for values from (1) to (2= ); > CREATE TABLE > > test=3D# insert into test values (1, 100, 101); > INSERT 0 1 > > test=3D# insert into test values (1, 100, 100); > INSERT 0 1 > > test=3D# select * from test_1; > id | v1 | v2 > ----+-----+----- > 1 | 100 | 100 > (1 row) > > test=3D# select * from test_2; > id | v1 | v2 > ----+-----+----- > 1 | 100 | 101 > (1 row) It seems you are trying to simulate hash partitioning using that expressio= n. Why not use hash partitioning directly then? create table test ( id text, v1 bigint, v2 bigint ) partition by hash (v1, v2); create table test_1 partition of test for values with (modulus 2, remainder 0); create table test_2 partition of test for values with (modulus 2, remainder 1); Then the select will result in: QUERY PLAN =2D-----------------------------------------------------------------------= ----------------------------- Seq Scan on test_1 test (cost=3D0.00..17.49 rows=3D1 width=3D48) (actual = time=3D0.015..0.016 rows=3D1 loops=3D1) Filter: ((v1 =3D 100) AND (v2 =3D 100)) Planning Time: 0.159 ms Execution Time: 0.037 ms Demo: https://dbfiddle.uk/wuopLYeQ