Date: Thu, 10 Oct 2024 21:21:46 +0200
From: Erik Wienhold <ewie@ewie.name>
To: sud <suds1434@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Re: Question on indexes
Message-ID: <88e5c335-c70e-4973-9b71-6c12e251e5b7@ewie.name>
References: <CAD=mzVWLN35FHPxN0Ajh2vnDkFLyg9f0vT6EKF9uPm7CYkHS5g@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <CAD=mzVWLN35FHPxN0Ajh2vnDkFLyg9f0vT6EKF9uPm7CYkHS5g@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/88e5c335-c70e-4973-9b71-6c12e251e5b7%40ewie.name>
Precedence: bulk

On 2024-10-10 20:49 +0200, sud wrote:
> However, we are seeing that one of the databases has multiple hash indexes
> created. So I wanted to understand from experts here, if it's advisable in
> any specific scenarios over B-tre despite such downsides?

Two things come to my mind:

1. Btree puts a limit on the size of indexed values, whereas hash
   indexes only store the 32-bit hash code.

2. Of the core index types, only btree supports unique indexes.

Example of btree's size limit:

    CREATE TABLE b (s text);
    CREATE INDEX ON b USING btree (s);
    INSERT INTO b (s) VALUES (repeat('x', 1000000));
    ERROR:  index row requires 11464 bytes, maximum size is 8191

The docs have more details:
https://www.postgresql.org/docs/current/btree.html
https://www.postgresql.org/docs/current/hash-index.html

-- 
Erik