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 1nP4ZM-00086S-Ps for pgsql-performance@arkaria.postgresql.org; Tue, 01 Mar 2022 15:37:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nP4ZJ-0000D9-W8 for pgsql-performance@arkaria.postgresql.org; Tue, 01 Mar 2022 15:37:42 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nP4ZJ-0000AM-Dd for pgsql-performance@lists.postgresql.org; Tue, 01 Mar 2022 15:37:41 +0000 Received: from smtp40.i.mail.ru ([94.100.177.100]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nP4ZB-0006d3-95 for pgsql-performance@lists.postgresql.org; Tue, 01 Mar 2022 15:37:40 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=mail.ru; s=mail4; h=Content-Type:MIME-Version:Message-ID:Date:Subject:To:From:From:Subject:Content-Type:Content-Transfer-Encoding:To:Cc; bh=5aSblQHgzU9pLQr0YKA12PcJ8UUNt5yq+vrSOKu788w=; t=1646149053;x=1646754453; b=eHGoLpoHF2E1Fo51WId7h5xv/B+pYQbJNY9pMA/+PApeSfljpiT0dE+SeI2VKBqoE8apk4crc3ykqtYXohywDYD9n5PyplT0tVCnfBO3TC/MgBvm9HwXDn1LmuPgpw2Wif3G35QdHgfnlOElx61yC2QcPNomtGIlQoGukv0U+RCDO0A1fJVWdweGOTetMLzFuVEZpkjtN+NsBqg4lB75HPHMTf4izg1P3Zvw8rhmEUqrRkzzJirHfUVp5OZPs8wMRsYf5WcpGBk9JYLOrCXkUYSJ17TPfjt5G6IpXPguLw4yTMUAocVbRUyYv8hiFCHUJWpGt62bgmWdGhRKjHKHbQ==; Received: by smtp40.i.mail.ru with esmtpa (envelope-from ) id 1nP4Z7-0001H7-Nn for pgsql-performance@lists.postgresql.org; Tue, 01 Mar 2022 18:37:30 +0300 From: "Andrew Zakharov" To: Subject: Simple task with partitioning which I can't realize Date: Tue, 1 Mar 2022 18:37:28 +0300 Message-ID: <011d01d82d82$42d20c50$c87624f0$@mail.ru> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_011E_01D82D9B.681F4450" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AdgtfFHl4ocJ1I4HQQeU1PhbvgCm2w== Content-Language: ru Authentication-Results: smtp40.i.mail.ru; auth=pass smtp.auth=andrew898@mail.ru smtp.mailfrom=andrew898@mail.ru X-7564579A: 646B95376F6C166E X-77F55803: 4F1203BC0FB41BD9A10ECB78D781867AA58720C83F0584729AB6A37225C0E331182A05F5380850407B78E35028994F2E12C1D25F37F6A9CE0B59A939AB4D3A1B0CCCABBB2E5A2CEE X-7FA49CB5: FF5795518A3D127A4AD6D5ED66289B5278DA827A17800CE73C871DD2182510D5EA1F7E6F0F101C67BD4B6F7A4D31EC0BCC500DACC3FED6E28638F802B75D45FF8AA50765F7900637CF19945FAF91394A8638F802B75D45FF36EB9D2243A4F8B5A6FCA7DBDB1FC311F39EFFDF887939037866D6147AF826D8E641EEC6F99F9B563FC7828DBC9F17A26F9789CCF6C18C3F8528715B7D10C86878DA827A17800CE7E4CD18687D5AE3809FA2833FD35BB23D9E625A9149C048EE33AC447995A7AD18C26CFBAC0749D213D2E47CDBA5A96583BD4B6F7A4D31EC0BC014FD901B82EE079FA2833FD35BB23D27C277FBC8AE2E8B2EE5AD8F952D28FBA471835C12D1D977C4224003CC836476EB9C4185024447017B076A6E789B0E975F5C1EE8F4F765FCE9F1E016B4599B323AA81AA40904B5D9CF19DD082D7633A078D18283394535A93AA81AA40904B5D98AA50765F79006378E8259D080D83759EC76A7562686271EEC990983EF5C03292E808ACE2090B5E14AD6D5ED66289B5259CC434672EE63711DD303D21008E298D5E8D9A59859A8B6B372FE9A2E580EFC725E5C173C3A84C34B08FA16E56A400835872C767BF85DA2F004C90652538430E4A6367B16DE6309 X-8FC586DF: 6EFBBC1D9D64D975 X-C1DE0DAB: C20DE7B7AB408E4181F030C43753B8186998911F362727C4C7A0BC55FA0FE5FC6B63930EAA6A6ABF7961396D9E4E5A180832132F3FFAD868B1881A6453793CE9C32612AADDFBE061C61BE10805914D3804EBA3D8E7E5B87ABF8C51168CD8EBDBC7A3838EBC35966ADC48ACC2A39D04F89CDFB48F4795C241BDAD6C7F3747799A X-C8649E89: 4E36BF7865823D7055A7F0CF078B5EC49A30900B95165D34D041FB2E16F174C4F1B83F85E2AC90C27B04931F19BE4948861B309DD8BE13E31BC669E5EE37FF351D7E09C32AA3244CDB81763150349BFFB7DAA63A200192154DBEAD0ED6C55A803EB3F6AD6EA9203E X-D57D3AED: 3ZO7eAau8CL7WIMRKs4sN3D3tLDjz0dLbV79QFUyzQ2Ujvy7cMT6pYYqY16iZVKkSc3dCLJ7zSJH7+u4VD18S7Vl4ZUrpaVfd2+vE6kuoey4m4VkSEu530nj6fImhcD4MUrOEAnl0W826KZ9Q+tr5ycPtXkTV4k65bRjmOUUP8cvGozZ33TWg5HZplvhhXbhDGzqmQDTd6OAevLeAnq3Ra9uf7zvY2zzsIhlcp/Y7m53TZgf2aB4JOg4gkr2biojbL9S8ysBdXiXtErcIoJxG3klitdd0iH+ X-Mailru-Sender: C0867E0E6D7FB6C0AC1DF044B4F6B17015F79FD110B88FD512C1D25F37F6A9CE0C0F1FA48E969E6676CADC040CC768ECC77752E0C033A69E4431F22F943D8E2367DBD6A654610528B4A721A3011E896F X-Mras: Ok List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_011E_01D82D9B.681F4450 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hello all - I have a task which is simple at the first look. I have a table which contains hierarchy of address objects starting with macro region end ends with particular buildings. You can imagine how big is it. Here is short sample of table declaration: create table region_hierarchy( gid uuid not null default uuid_generate_v1mc(), parent_gid uuid null, region_code int2, . constraint pk_region_hierarchy primary key (gid), constraint fk_region_hierarchy_region_hierarchy_parent foreign key (parent_gid) references region_hierarchy(gid) ); Being an Oracle specialist, I planned to using same declarative partitioning by list on the region_code field as I did in Oracle database. I've carefully looked thru docs/faqs/google/communities and found out that I must include "gid" field into partition key because a primary key field. Thus partition method "by list" is not appropriate method in this case and "by range" either. What I have left from partition methods? Hash? How can I create partitions by gid & region_code by hash? Feasible? Will it be working properly (with partition pruning) when search criteria is by region_code only? Same problem appears when there is simple serial "id" used as primary identifier. Removing all constraints is not considered. I understand that such specific PostgreSQL partitioning implementation has done by tons of reasons but how I can implement partitioning for my EASY case? I see the only legacy inheritance is left, right? Very sad if it's true. Your advices are very important. Thanks in advance. Andrew. ------=_NextPart_000_011E_01D82D9B.681F4450 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hello all = –

I = have a task which is simple at the first look. I have a table which = contains hierarchy of address objects starting with macro region end = ends with particular buildings. You can imagine how big is it. =

Here is = short sample of table declaration:

 

create table region_hierarchy(

  gid uuid not null default = uuid_generate_v1mc(),

  parent_gid uuid = null,

  region_code = int2,

  = …

    constraint = pk_region_hierarchy primary key (gid),

    constraint = fk_region_hierarchy_region_hierarchy_parent foreign key (parent_gid) = references region_hierarchy(gid)

);

 

Being an Oracle specialist, I planned to using same = declarative partitioning by list on the region_code field as I did in = Oracle database. I’ve carefully looked thru = docs/faqs/google/communities and found out that I must include = “gid” field into partition key because a primary key field. = Thus partition method “by list” is not appropriate method in = this case and “by range” either. What I have left from = partition methods? Hash? How can I create partitions by gid & = region_code by hash? Feasible? Will it be working properly (with = partition pruning) when search criteria is by region_code only? Same = problem appears when there is simple serial “id” used as = primary identifier. Removing all constraints is not considered. I = understand that such specific PostgreSQL partitioning implementation has = done by tons of reasons but how I can implement partitioning for my EASY = case? I see the only legacy inheritance is left, right? Very sad if = it’s true.

Your advices are very important.

Thanks in = advance.

Andrew.

 

------=_NextPart_000_011E_01D82D9B.681F4450--