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 1q4Q0Y-0001Rj-CU for pgsql-general@arkaria.postgresql.org; Wed, 31 May 2023 17:53:14 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q4Q0W-0004Q8-0G for pgsql-general@arkaria.postgresql.org; Wed, 31 May 2023 17:53:12 +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 1q4Q0V-0004Pk-KL for pgsql-general@lists.postgresql.org; Wed, 31 May 2023 17:53:11 +0000 Received: from smtp118.iad3a.emailsrvr.com ([173.203.187.118]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1q4Q0N-003KN5-6y for pgsql-general@lists.postgresql.org; Wed, 31 May 2023 17:53:10 +0000 Received: from app38.wa-webapps.iad3a (relay-webapps.rsapps.net [172.27.255.140]) by smtp31.relay.iad3a.emailsrvr.com (SMTP Server) with ESMTP id DB826246EC; Wed, 31 May 2023 13:53:01 -0400 (EDT) Received: from kartographia.com (localhost.localdomain [127.0.0.1]) by app38.wa-webapps.iad3a (Postfix) with ESMTP id C2AFAE1C22; Wed, 31 May 2023 13:53:01 -0400 (EDT) Received: by apps.rackspace.com (Authenticated sender: peter.borissow@kartographia.com, from: peter.borissow@kartographia.com) with HTTP; Wed, 31 May 2023 13:53:01 -0400 (EDT) X-Auth-ID: peter.borissow@kartographia.com Date: Wed, 31 May 2023 13:53:01 -0400 (EDT) Subject: Re: Hash Index on Partitioned Table From: "peter.borissow@kartographia.com" To: "Tom Lane" Cc: pgsql-general@lists.postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative;boundary="----=_20230531135301000000_45747" Importance: Normal X-Priority: 3 (Normal) X-Type: html In-Reply-To: <2657.1685542070@sss.pgh.pa.us> References: <1685540675.65414951@apps.rackspace.com> <2657.1685542070@sss.pgh.pa.us> X-Client-IP: 170.250.244.158 Message-ID: <1685555581.79621479@apps.rackspace.com> X-Mailer: webmail/19.0.23-RC X-Classification-ID: 09035214-a1de-432c-a11c-87693fcaf9bd-1-1 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_20230531135301000000_45747 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable =0AHi Tom,=0A Thanks so much for your quick response. As luck would have= it, the index FINALLY finished about an hour ago. For a size comparison:= =0A =0ABTree: 6,433 GB=0AHash: 8,647 GB=0A =0AAlthough I don't have a prope= r benchmark to compare performance, I can say the hash is working as good a= s if not faster than the BTree for my use case (web application).=0A =0AI g= uess I was getting a little nervous waiting for the index to complete and s= eeing such a huge difference in file size but I'm ok now :-)=0A =0AThanks a= gain,=0APeter=0A =0A =0A-----Original Message-----=0AFrom: "Tom Lane" =0ASent: Wednesday, May 31, 2023 10:07am=0ATo: "peter.borisso= w@kartographia.com" =0ACc: pgsql-general@l= ists.postgresql.org=0ASubject: Re: Hash Index on Partitioned Table=0A=0A=0A= =0A"peter.borissow@kartographia.com" writ= es:=0A> I have a rather large database with ~250 billion records in a parti= tioned table. The database has been running and ingesting data continuously= for about 3 years.=0A=0A> I had a "regular" BTree index on one of the fiel= ds (a unique bigint column) but it was getting too big for the disk it was = on. The index was consuming 6.4 TB of disk space.=0A=0AThat's ... really ab= out par for the course. Each row requires an 8-byte=0Aindex entry, plus 12 = bytes index overhead. If I'm doing the math right=0Athen the index is physi= cally about 78% full which is typical to good for=0Aa b-tree. Reindexing wo= uld remove most of the extra space, but only=0Atemporarily.=0A=0A> After do= ing some research I decided to try to create a hash index instead of a BTre= e. For my purposes, the index is only used to find specific numbers ("=3D" = and "IN" queries). From what I read, the hash index should run a little fas= ter than btree for my use case and should use less disk space.=0A=0AI'm ske= ptical. The thing to bear in mind is that btree is the mainstream=0Ause-cas= e and has been refined and optimized far more than the hash index=0Alogic.= =0A=0A> (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?=0A=0AFrom memory, the index entries will be the s= ame size in this case,=0Abut hash might have more wasted space.=0A=0A> (4) = Is there any way to estimate when the index process will complete?=0A=0AAn = index on a partitioned table isn't a single object, it's one index per=0Apa= rtition. So you should be able to look at how many partitions have=0Aindexe= s so far. You might have to drill down to the point of counting how=0Amany = files in the database's directory, if the individual indexes aren't=0Ashowi= ng up as committed catalog entries yet.=0A=0A regards, tom lane ------=_20230531135301000000_45747 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Hi Tom,

=0A

    Thanks so much for your quick response. As = luck would have it, the index FINALLY finished about an hour ago. For a siz= e comparison:

=0A

 

=0A

= BTree: 6,433 GB

=0A

Hash: 8,647 GB

=0A

 

=0A

Although I don't have a proper = benchmark to compare performance, I can say the hash is working as good as = if not faster than the BTree for my use case (web application).

=0A

 

=0A

I guess I was getting a l= ittle nervous waiting for the index to complete and seeing such a huge diff= erence in file size but I'm ok now :-)

=0A

 =0A

Thanks again,

=0A

Peter

= =0A

 

=0A

 

=0A

-----Original Message-----
From: "Tom Lane" <tgl@s= ss.pgh.pa.us>
Sent: Wednesday, May 31, 2023 10:07am
To: "peter= .borissow@kartographia.com" <peter.borissow@kartographia.com>
Cc= : pgsql-general@lists.postgresql.org
Subject: Re: Hash Index on Partit= ioned Table

=0A
=0A

"peter.borissow@kartographia.com" <peter.borissow@kartograp= hia.com> writes:
> I have a rather large database with ~250 bill= ion records in a partitioned table. The database has been running and inges= ting data continuously for about 3 years.

> I had a "regular"= BTree index on one of the fields (a unique bigint column) but it was getti= ng too big for the disk it was on. The index was consuming 6.4 TB of disk s= pace.

That's ... really about par for the course. Each row requi= res 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 ty= pical 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 instead of a BTree. For my purpose= s, the index is only used to find specific numbers ("=3D" and "IN" queries)= . From what I read, the hash index should run a little faster than btree fo= r 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 ha= s been refined and optimized far more than the hash index
logic.
=
> (1) Why is the hash index consuming more disk space than the btr= ee index? Is it because the hash of the bigint values larger than the stori= ng the bigints in the btree?

From 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 wi= ll complete?

An index on a partitioned table isn't a single obje= ct, 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 t= he individual indexes aren't
showing up as committed catalog entries y= et.

regards, tom lane

=0A
------=_20230531135301000000_45747--