public inbox for [email protected]
help / color / mirror / Atom feedQuestion on indexes
9+ messages / 6 participants
[nested] [flat]
* Question on indexes
@ 2024-10-10 18:49 sud <[email protected]>
2024-10-10 18:51 ` Re: Question on indexes Christophe Pettus <[email protected]>
2024-10-10 19:21 ` Re: Question on indexes Erik Wienhold <[email protected]>
2024-10-11 06:43 ` Re: Question on indexes Laurenz Albe <[email protected]>
0 siblings, 3 replies; 9+ messages in thread
From: sud @ 2024-10-10 18:49 UTC (permalink / raw)
To: pgsql-general <[email protected]>
Hi,
I have never used any 'hash index' but saw documents in the past suggesting
issues around hash index , like WAL doesnt generate for "hash index" which
means we can't get the hash index back after crash also they are not
applied to replicas etc. And also these indexes can not be used for range
queries , for sorting etc.
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?
Note- Its version 15.4 database.
Regards
Sud
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Question on indexes
2024-10-10 18:49 Question on indexes sud <[email protected]>
@ 2024-10-10 18:51 ` Christophe Pettus <[email protected]>
2 siblings, 0 replies; 9+ messages in thread
From: Christophe Pettus @ 2024-10-10 18:51 UTC (permalink / raw)
To: sud <[email protected]>; +Cc: pgsql-general <[email protected]>
> On Oct 10, 2024, at 11:49, sud <[email protected]> wrote:
>
> Hi,
> I have never used any 'hash index' but saw documents in the past suggesting issues around hash index , like WAL doesnt generate for "hash index" which means we can't get the hash index back after crash also they are not applied to replicas etc.
That's very old information. Hash indexes are correctly WAL-logged since (IIRC) version 10.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Question on indexes
2024-10-10 18:49 Question on indexes sud <[email protected]>
@ 2024-10-10 19:21 ` Erik Wienhold <[email protected]>
2024-10-10 19:44 ` Re: Question on indexes sud <[email protected]>
2 siblings, 1 reply; 9+ messages in thread
From: Erik Wienhold @ 2024-10-10 19:21 UTC (permalink / raw)
To: sud <[email protected]>; +Cc: pgsql-general <[email protected]>
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Question on indexes
2024-10-10 18:49 Question on indexes sud <[email protected]>
2024-10-10 19:21 ` Re: Question on indexes Erik Wienhold <[email protected]>
@ 2024-10-10 19:44 ` sud <[email protected]>
2024-10-10 20:36 ` Re: Question on indexes Erik Wienhold <[email protected]>
2024-10-11 11:29 ` Re: Question on indexes Greg Sabino Mullane <[email protected]>
0 siblings, 2 replies; 9+ messages in thread
From: sud @ 2024-10-10 19:44 UTC (permalink / raw)
To: Erik Wienhold <[email protected]>; [email protected]; +Cc: pgsql-general <[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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Question on indexes
2024-10-10 18:49 Question on indexes sud <[email protected]>
2024-10-10 19:21 ` Re: Question on indexes Erik Wienhold <[email protected]>
2024-10-10 19:44 ` Re: Question on indexes sud <[email protected]>
@ 2024-10-10 20:36 ` Erik Wienhold <[email protected]>
1 sibling, 0 replies; 9+ messages in thread
From: Erik Wienhold @ 2024-10-10 20:36 UTC (permalink / raw)
To: sud <[email protected]>; +Cc: [email protected]; pgsql-general <[email protected]>
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Question on indexes
2024-10-10 18:49 Question on indexes sud <[email protected]>
2024-10-10 19:21 ` Re: Question on indexes Erik Wienhold <[email protected]>
2024-10-10 19:44 ` Re: Question on indexes sud <[email protected]>
@ 2024-10-11 11:29 ` Greg Sabino Mullane <[email protected]>
2024-10-11 12:31 ` Re: Question on indexes Durgamahesh Manne <[email protected]>
1 sibling, 1 reply; 9+ messages in thread
From: Greg Sabino Mullane @ 2024-10-11 11:29 UTC (permalink / raw)
To: sud <[email protected]>; +Cc: Erik Wienhold <[email protected]>; [email protected]; pgsql-general <[email protected]>
>
> 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.
>
There are other strategies / solutions, but we would need to learn more
about your use case.
Cheers,
Greg
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Question on indexes
2024-10-10 18:49 Question on indexes sud <[email protected]>
2024-10-10 19:21 ` Re: Question on indexes Erik Wienhold <[email protected]>
2024-10-10 19:44 ` Re: Question on indexes sud <[email protected]>
2024-10-11 11:29 ` Re: Question on indexes Greg Sabino Mullane <[email protected]>
@ 2024-10-11 12:31 ` Durgamahesh Manne <[email protected]>
2024-10-11 12:48 ` Re: Question on indexes Greg Sabino Mullane <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Durgamahesh Manne @ 2024-10-11 12:31 UTC (permalink / raw)
To: Greg Sabino Mullane <[email protected]>; +Cc: sud <[email protected]>; Erik Wienhold <[email protected]>; [email protected]; pgsql-general <[email protected]>
On Fri, Oct 11, 2024 at 5:00 PM Greg Sabino Mullane <[email protected]>
wrote:
> 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.
>>
>
> There are other strategies / solutions, but we would need to learn more
> about your use case.
>
> Cheers,
> Greg
>
>
Hi Respected Team
How do we enforce the secondary column of composite index to index scan on
concurrent activity in postgres?
Second column of composite index not in use effectively with index scan
when using second column at where clause
I have composite index on (placedon,id) of test
When querying select * from test where id = '4234';
Value of id changes and during concurrent activity and cpu utilization
increased too much that i have observed which means query plan changed why
I could see index scan with explain for it on singal call or double calls
Is there any way to keep an index scan for it during concurrency rather
than a separate index on the second column of the composite index ?
Regards,
Durga Mahesh
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Question on indexes
2024-10-10 18:49 Question on indexes sud <[email protected]>
2024-10-10 19:21 ` Re: Question on indexes Erik Wienhold <[email protected]>
2024-10-10 19:44 ` Re: Question on indexes sud <[email protected]>
2024-10-11 11:29 ` Re: Question on indexes Greg Sabino Mullane <[email protected]>
2024-10-11 12:31 ` Re: Question on indexes Durgamahesh Manne <[email protected]>
@ 2024-10-11 12:48 ` Greg Sabino Mullane <[email protected]>
0 siblings, 0 replies; 9+ messages in thread
From: Greg Sabino Mullane @ 2024-10-11 12:48 UTC (permalink / raw)
To: Durgamahesh Manne <[email protected]>; +Cc: sud <[email protected]>; Erik Wienhold <[email protected]>; [email protected]; pgsql-general <[email protected]>
(please start a new thread in the future rather than replying to an
existing one)
You cannot query on b and use an index on (a,b) as you observed. However,
you can have two indexes:
index1(a)
index2(b)
Postgres will be able to combine those when needed in the case where your
WHERE clause needs to filter by both columns. So then you no longer need
the two-column index.
Cheers,
Greg
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Question on indexes
2024-10-10 18:49 Question on indexes sud <[email protected]>
@ 2024-10-11 06:43 ` Laurenz Albe <[email protected]>
2 siblings, 0 replies; 9+ messages in thread
From: Laurenz Albe @ 2024-10-11 06:43 UTC (permalink / raw)
To: sud <[email protected]>; pgsql-general <[email protected]>
On Fri, 2024-10-11 at 00:19 +0530, sud wrote:
> I have never used any 'hash index' but saw documents in the past suggesting issues
> around hash index , like WAL doesnt generate for "hash index" which means we can't
> get the hash index back after crash also they are not applied to replicas etc.
> And also these indexes can not be used for range queries , for sorting etc.
>
> 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?
> Note- Its version 15.4 database.
It is safe to use them, but in my tests I didn't find a realistic case where the were
better than a B-tree index:
https://www.cybertec-postgresql.com/en/postgresql-hash-index-performance/
Keep them if they do the trick for you, but I'd use B-tree indexes instead.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2024-10-11 12:48 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-10-10 18:49 Question on indexes sud <[email protected]>
2024-10-10 18:51 ` Christophe Pettus <[email protected]>
2024-10-10 19:21 ` Erik Wienhold <[email protected]>
2024-10-10 19:44 ` sud <[email protected]>
2024-10-10 20:36 ` Erik Wienhold <[email protected]>
2024-10-11 11:29 ` Greg Sabino Mullane <[email protected]>
2024-10-11 12:31 ` Durgamahesh Manne <[email protected]>
2024-10-11 12:48 ` Greg Sabino Mullane <[email protected]>
2024-10-11 06:43 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox