MIME-Version: 1.0
References: <CAD=mzVWLN35FHPxN0Ajh2vnDkFLyg9f0vT6EKF9uPm7CYkHS5g@mail.gmail.com>
 <88e5c335-c70e-4973-9b71-6c12e251e5b7@ewie.name>
In-Reply-To: <88e5c335-c70e-4973-9b71-6c12e251e5b7@ewie.name>
From: sud <suds1434@gmail.com>
Date: Fri, 11 Oct 2024 01:14:28 +0530
Message-ID: <CAD=mzVWHC8O+fLksgc-uKAcq19xQSVvHz1AsButFmL+xh0_btg@mail.gmail.com>
Subject: Re: Question on indexes
To: Erik Wienhold <ewie@ewie.name>, xof@thebuild.com
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000fa4d520624249956"
Archived-At: <https://www.postgresql.org/message-id/CAD%3DmzVWHC8O%2BfLksgc-uKAcq19xQSVvHz1AsButFmL%2Bxh0_btg%40mail.gmail.com>
Precedence: bulk

--000000000000fa4d520624249956
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Fri, Oct 11, 2024 at 12:51=E2=80=AFAM Erik Wienhold <ewie@ewie.name> wro=
te:

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

--000000000000fa4d520624249956
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><div class=3D"gmail_quote"><div=
 dir=3D"ltr" class=3D"gmail_attr">On Fri, Oct 11, 2024 at 12:51=E2=80=AFAM =
Erik Wienhold &lt;<a href=3D"mailto:ewie@ewie.name">ewie@ewie.name</a>&gt; =
wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0=
px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">On 2024-1=
0-10 20:49 +0200, sud wrote:<br>
&gt; However, we are seeing that one of the databases has multiple hash ind=
exes<br>
&gt; created. So I wanted to understand from experts here, if it&#39;s advi=
sable in<br>
&gt; any specific scenarios over B-tre despite such downsides?<br>
<br>
Two things come to my mind:<br>
<br>
1. Btree puts a limit on the size of indexed values, whereas hash<br>
=C2=A0 =C2=A0indexes only store the 32-bit hash code.<br>
<br>
2. Of the core index types, only btree supports unique indexes.<br>
<br>
Example of btree&#39;s size limit:<br>
<br>
=C2=A0 =C2=A0 CREATE TABLE b (s text);<br>
=C2=A0 =C2=A0 CREATE INDEX ON b USING btree (s);<br>
=C2=A0 =C2=A0 INSERT INTO b (s) VALUES (repeat(&#39;x&#39;, 1000000));<br>
=C2=A0 =C2=A0 ERROR:=C2=A0 index row requires 11464 bytes, maximum size is =
8191<br>
<br>
The docs have more details:<br>
<a href=3D"https://www.postgresql.org/docs/current/btree.html" rel=3D"noref=
errer" target=3D"_blank">https://www.postgresql.org/docs/current/btree.html=
</a><br>
<a href=3D"https://www.postgresql.org/docs/current/hash-index.html" rel=3D"=
noreferrer" target=3D"_blank">https://www.postgresql.org/docs/current/hash-=
index.html</a><br>
<br><br></blockquote><div>Thank you.</div><div><br></div><div>Not yet confi=
rmed, but actually somehow we see the DB crashed repetitively a few times a=
nd teammates=C2=A0suspecting=C2=A0the cause while it tried extending this h=
ash index. Did you experience any such thing with hash index? However, as y=
ou mentioned ,if we have any column with large string/text values and we wa=
nt it to be indexed then there is no choice but to go for a hash index. Ple=
ase correct me if I&#39;m wrong.</div></div></div>

--000000000000fa4d520624249956--