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 1q4MUa-0001kD-QW for pgsql-general@arkaria.postgresql.org; Wed, 31 May 2023 14:08:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q4MUZ-00006j-Ih for pgsql-general@arkaria.postgresql.org; Wed, 31 May 2023 14:07:59 +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 1q4MUZ-00006a-7z for pgsql-general@lists.postgresql.org; Wed, 31 May 2023 14:07:59 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1q4MUT-002u64-KL for pgsql-general@lists.postgresql.org; Wed, 31 May 2023 14:07:57 +0000 Received: from pro.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 34VE7o5G3004445; Wed, 31 May 2023 10:07:50 -0400 From: Tom Lane To: "peter.borissow@kartographia.com" cc: pgsql-general@lists.postgresql.org Subject: Re: Hash Index on Partitioned Table In-reply-to: <1685540675.65414951@apps.rackspace.com> References: <1685540675.65414951@apps.rackspace.com> Comments: In-reply-to "peter.borissow@kartographia.com" message dated "Wed, 31 May 2023 09:44:35 -0400" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <2656.1685542070.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Wed, 31 May 2023 10:07:50 -0400 Message-ID: <2657.1685542070@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk "peter.borissow@kartographia.com" writes= : > I have a rather large database with ~250 billion records in a partitione= d table. The database has been running and ingesting data continuously for= about 3 years. = > I had a "regular" BTree index on one of the fields (a unique bigint colu= mn) but it was getting too big for the disk it was on. The index was consu= ming 6.4 TB of disk space. That's ... really about par for the course. Each row requires an 8-byte index entry, plus 12 bytes index overhead. If I'm doing the math right then the index is physically about 78% full which is typical to good for a b-tree. Reindexing would remove most of the extra space, but only temporarily. > After doing some research I decided to try to create a hash index instea= d of a BTree. For my purposes, the index is only used to find specific num= bers ("=3D" and "IN" queries). From what I read, the hash index should run= a little faster than btree for my use case and should use less disk space= . I'm skeptical. The thing to bear in mind is that btree is the mainstream use-case and has been refined and optimized far more than the hash index logic. > (1) Why is the hash index consuming more disk space than the btree index= ? Is it because the hash of the bigint values larger than the storing the = bigints in the btree? =46rom memory, the index entries will be the same size in this case, but hash might have more wasted space. > (4) Is there any way to estimate when the index process will complete? An index on a partitioned table isn't a single object, it's one index per partition. So you should be able to look at how many partitions have indexes so far. You might have to drill down to the point of counting how many files in the database's directory, if the individual indexes aren't showing up as committed catalog entries yet. regards, tom lane