public inbox for [email protected]  

help / color / mirror / Atom feed

From: sud <[email protected]>
To: Erik Wienhold <[email protected]>
To: [email protected]
Cc: pgsql-general <[email protected]>
Subject: Re: Question on indexes
Date: Fri, 11 Oct 2024 01:14:28 +0530
Message-ID: <CAD=mzVWHC8O+fLksgc-uKAcq19xQSVvHz1AsButFmL+xh0_btg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAD=mzVWLN35FHPxN0Ajh2vnDkFLyg9f0vT6EKF9uPm7CYkHS5g@mail.gmail.com>
	<[email protected]>

On Fri, Oct 11, 2024 at 12:51 AM Erik Wienhold <[email protected]> wrote:

> 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
>
>
> Thank you.

Not yet confirmed, but actually somehow we see the DB crashed repetitively
a few times and teammates suspecting the cause while it tried extending
this hash index. Did you experience any such thing with hash index?
However, as you mentioned ,if we have any column with large string/text
values and we want it to be indexed then there is no choice but to go for a
hash index. Please correct me if I'm wrong.

view thread (9+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Question on indexes
  In-Reply-To: <CAD=mzVWHC8O+fLksgc-uKAcq19xQSVvHz1AsButFmL+xh0_btg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox