public inbox for [email protected]  

help / color / mirror / Atom feed

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

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

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