public inbox for [email protected]  

help / color / mirror / Atom feed

From: Erik Wienhold <[email protected]>
To: sud <[email protected]>
Cc: [email protected]
Cc: pgsql-general <[email protected]>
Subject: Re: Question on indexes
Date: Thu, 10 Oct 2024 22:36:47 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAD=mzVWHC8O+fLksgc-uKAcq19xQSVvHz1AsButFmL+xh0_btg@mail.gmail.com>
References: <CAD=mzVWLN35FHPxN0Ajh2vnDkFLyg9f0vT6EKF9uPm7CYkHS5g@mail.gmail.com>
	<[email protected]>
	<CAD=mzVWHC8O+fLksgc-uKAcq19xQSVvHz1AsButFmL+xh0_btg@mail.gmail.com>

On 2024-10-10 21:44 +0200, sud wrote:
> 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.

Your first mail says that you're using version 15.4.  You should
consider upgrading to 15.8 to get the latest bugfixes.

> Did you experience any such thing with hash index?

No.  But I can't remember ever seeing a hash index in the databases that
I've worked on.

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

Define "large".

What kind of text (natural, JSON, XML, base64, DNA sequences, etc.) is
stored in those columns?  Why do you want/need to index those columns?
Because hash indexes only support the equal operator, one can only use
that index to search for exact matches (i.e. values with identical hash
code) which I find strange for values that are so large that btree
cannot be used.  But maybe you have solid use case for that.

If it's natural text and you're using tsvector for full-text search,
then GiST or GIN indexes are a better choice:
https://www.postgresql.org/docs/current/textsearch-indexes.html

-- 
Erik

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: <[email protected]>

* 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