pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedQuestion on indexes
14+ messages / 10 participants
[nested] [flat]
* Question on indexes
@ 2005-12-19 15:51 Emil Rachovsky <zsevgymko@yahoo.com>
2005-12-19 16:29 ` Re: Question on indexes vishal saberwal <vishalsaberwal@gmail.com>
2005-12-19 16:31 ` Re: [SQL] Question on indexes Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 2 replies; 14+ messages in thread
From: Emil Rachovsky @ 2005-12-19 15:51 UTC (permalink / raw)
To: pgsql-sql@postgresql.org; +Cc: pgsql-general
Hi,
Can anyone show me a simple way of creating an index
in PostGre like that:
create index indName on someTable(someIntColumn DESC)
?
Thanks In Advance,
Emil
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Question on indexes
2005-12-19 15:51 Question on indexes Emil Rachovsky <zsevgymko@yahoo.com>
@ 2005-12-19 16:29 ` vishal saberwal <vishalsaberwal@gmail.com>
1 sibling, 0 replies; 14+ messages in thread
From: vishal saberwal @ 2005-12-19 16:29 UTC (permalink / raw)
To: Emil Rachovsky <zsevgymko@yahoo.com>; +Cc: pgsql-sql@postgresql.org; pgsql-general
CREATE INDEX code_idx ON films(code) TABLESPACE indexspace;
http://www.postgresql.org/docs/8.1/interactive/sql-createindex.html
vish
On 12/19/05, Emil Rachovsky <zsevgymko@yahoo.com> wrote:
>
>
> Hi,
> Can anyone show me a simple way of creating an index
> in PostGre like that:
> create index indName on someTable(someIntColumn DESC)
> ?
>
> Thanks In Advance,
> Emil
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [SQL] Question on indexes
2005-12-19 15:51 Question on indexes Emil Rachovsky <zsevgymko@yahoo.com>
@ 2005-12-19 16:31 ` Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 0 replies; 14+ messages in thread
From: Alvaro Herrera @ 2005-12-19 16:31 UTC (permalink / raw)
To: Emil Rachovsky <zsevgymko@yahoo.com>; +Cc: pgsql-sql@postgresql.org; pgsql-general
Emil Rachovsky wrote:
>
> Hi,
> Can anyone show me a simple way of creating an index
> in PostGre like that:
> create index indName on someTable(someIntColumn DESC)
> ?
Not using that particular syntax, but you can do that if you create the
appropiate operator classes. Note that if you want to use btree index
on a single column, you don't need to have a descending index, because
btree indexes can be scanned in either direction. The opclass trick is
only needed if you want to have a multicolumn index. (I guess in the
other access methods it doesn't make much sense to think of descending
indexes.)
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Question on indexes
@ 2024-10-10 18:49 sud <suds1434@gmail.com>
2024-10-10 18:51 ` Re: Question on indexes Christophe Pettus <xof@thebuild.com>
2024-10-10 19:21 ` Re: Question on indexes Erik Wienhold <ewie@ewie.name>
2024-10-11 06:43 ` Re: Question on indexes Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 3 replies; 14+ messages in thread
From: sud @ 2024-10-10 18:49 UTC (permalink / raw)
To: pgsql-general <pgsql-general@lists.postgresql.org>
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] 14+ messages in thread
* Re: Question on indexes
2024-10-10 18:49 Question on indexes sud <suds1434@gmail.com>
@ 2024-10-10 18:51 ` Christophe Pettus <xof@thebuild.com>
2024-10-11 01:17 ` Re: Question on indexes Efrain J. Berdecia <ejberdecia@yahoo.com>
2 siblings, 1 reply; 14+ messages in thread
From: Christophe Pettus @ 2024-10-10 18:51 UTC (permalink / raw)
To: sud <suds1434@gmail.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>
> On Oct 10, 2024, at 11:49, sud <suds1434@gmail.com> 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] 14+ messages in thread
* Re: Question on indexes
2024-10-10 18:49 Question on indexes sud <suds1434@gmail.com>
2024-10-10 18:51 ` Re: Question on indexes Christophe Pettus <xof@thebuild.com>
@ 2024-10-11 01:17 ` Efrain J. Berdecia <ejberdecia@yahoo.com>
0 siblings, 0 replies; 14+ messages in thread
From: Efrain J. Berdecia @ 2024-10-11 01:17 UTC (permalink / raw)
To: xof@thebuild.com, sud <suds1434@gmail.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>
They are extremely efficient for joins!!!
Yahoo Mail: Search, Organize, Conquer
On Thu, Oct 10, 2024 at 2:52 PM, Christophe Pettus<xof@thebuild.com> wrote:
> On Oct 10, 2024, at 11:49, sud <suds1434@gmail.com> 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] 14+ messages in thread
* Re: Question on indexes
2024-10-10 18:49 Question on indexes sud <suds1434@gmail.com>
@ 2024-10-10 19:21 ` Erik Wienhold <ewie@ewie.name>
2024-10-10 19:44 ` Re: Question on indexes sud <suds1434@gmail.com>
2 siblings, 1 reply; 14+ messages in thread
From: Erik Wienhold @ 2024-10-10 19:21 UTC (permalink / raw)
To: sud <suds1434@gmail.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>
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] 14+ messages in thread
* Re: Question on indexes
2024-10-10 18:49 Question on indexes sud <suds1434@gmail.com>
2024-10-10 19:21 ` Re: Question on indexes Erik Wienhold <ewie@ewie.name>
@ 2024-10-10 19:44 ` sud <suds1434@gmail.com>
2024-10-10 20:36 ` Re: Question on indexes Erik Wienhold <ewie@ewie.name>
2024-10-11 11:29 ` Re: Question on indexes Greg Sabino Mullane <htamfids@gmail.com>
0 siblings, 2 replies; 14+ messages in thread
From: sud @ 2024-10-10 19:44 UTC (permalink / raw)
To: Erik Wienhold <ewie@ewie.name>; xof@thebuild.com; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>
On Fri, Oct 11, 2024 at 12:51 AM Erik Wienhold <ewie@ewie.name> 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] 14+ messages in thread
* Re: Question on indexes
2024-10-10 18:49 Question on indexes sud <suds1434@gmail.com>
2024-10-10 19:21 ` Re: Question on indexes Erik Wienhold <ewie@ewie.name>
2024-10-10 19:44 ` Re: Question on indexes sud <suds1434@gmail.com>
@ 2024-10-10 20:36 ` Erik Wienhold <ewie@ewie.name>
1 sibling, 0 replies; 14+ messages in thread
From: Erik Wienhold @ 2024-10-10 20:36 UTC (permalink / raw)
To: sud <suds1434@gmail.com>; +Cc: xof@thebuild.com, pgsql-general <pgsql-general@lists.postgresql.org>
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] 14+ messages in thread
* Re: Question on indexes
2024-10-10 18:49 Question on indexes sud <suds1434@gmail.com>
2024-10-10 19:21 ` Re: Question on indexes Erik Wienhold <ewie@ewie.name>
2024-10-10 19:44 ` Re: Question on indexes sud <suds1434@gmail.com>
@ 2024-10-11 11:29 ` Greg Sabino Mullane <htamfids@gmail.com>
2024-10-11 12:31 ` Re: Question on indexes Durgamahesh Manne <maheshpostgres9@gmail.com>
1 sibling, 1 reply; 14+ messages in thread
From: Greg Sabino Mullane @ 2024-10-11 11:29 UTC (permalink / raw)
To: sud <suds1434@gmail.com>; +Cc: Erik Wienhold <ewie@ewie.name>; xof@thebuild.com, pgsql-general <pgsql-general@lists.postgresql.org>
>
> 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] 14+ messages in thread
* Re: Question on indexes
2024-10-10 18:49 Question on indexes sud <suds1434@gmail.com>
2024-10-10 19:21 ` Re: Question on indexes Erik Wienhold <ewie@ewie.name>
2024-10-10 19:44 ` Re: Question on indexes sud <suds1434@gmail.com>
2024-10-11 11:29 ` Re: Question on indexes Greg Sabino Mullane <htamfids@gmail.com>
@ 2024-10-11 12:31 ` Durgamahesh Manne <maheshpostgres9@gmail.com>
2024-10-11 12:48 ` Re: Question on indexes Greg Sabino Mullane <htamfids@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Durgamahesh Manne @ 2024-10-11 12:31 UTC (permalink / raw)
To: Greg Sabino Mullane <htamfids@gmail.com>; +Cc: sud <suds1434@gmail.com>; Erik Wienhold <ewie@ewie.name>; xof@thebuild.com, pgsql-general <pgsql-general@lists.postgresql.org>
On Fri, Oct 11, 2024 at 5:00 PM Greg Sabino Mullane <htamfids@gmail.com>
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] 14+ messages in thread
* Re: Question on indexes
2024-10-10 18:49 Question on indexes sud <suds1434@gmail.com>
2024-10-10 19:21 ` Re: Question on indexes Erik Wienhold <ewie@ewie.name>
2024-10-10 19:44 ` Re: Question on indexes sud <suds1434@gmail.com>
2024-10-11 11:29 ` Re: Question on indexes Greg Sabino Mullane <htamfids@gmail.com>
2024-10-11 12:31 ` Re: Question on indexes Durgamahesh Manne <maheshpostgres9@gmail.com>
@ 2024-10-11 12:48 ` Greg Sabino Mullane <htamfids@gmail.com>
2024-10-11 13:37 ` Re: Question on indexes Durgamahesh Manne <maheshpostgres9@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Greg Sabino Mullane @ 2024-10-11 12:48 UTC (permalink / raw)
To: Durgamahesh Manne <maheshpostgres9@gmail.com>; +Cc: sud <suds1434@gmail.com>; Erik Wienhold <ewie@ewie.name>; xof@thebuild.com, pgsql-general <pgsql-general@lists.postgresql.org>
(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] 14+ messages in thread
* Re: Question on indexes
2024-10-10 18:49 Question on indexes sud <suds1434@gmail.com>
2024-10-10 19:21 ` Re: Question on indexes Erik Wienhold <ewie@ewie.name>
2024-10-10 19:44 ` Re: Question on indexes sud <suds1434@gmail.com>
2024-10-11 11:29 ` Re: Question on indexes Greg Sabino Mullane <htamfids@gmail.com>
2024-10-11 12:31 ` Re: Question on indexes Durgamahesh Manne <maheshpostgres9@gmail.com>
2024-10-11 12:48 ` Re: Question on indexes Greg Sabino Mullane <htamfids@gmail.com>
@ 2024-10-11 13:37 ` Durgamahesh Manne <maheshpostgres9@gmail.com>
0 siblings, 0 replies; 14+ messages in thread
From: Durgamahesh Manne @ 2024-10-11 13:37 UTC (permalink / raw)
To: Greg Sabino Mullane <htamfids@gmail.com>; +Cc: sud <suds1434@gmail.com>; Erik Wienhold <ewie@ewie.name>; xof@thebuild.com, pgsql-general <pgsql-general@lists.postgresql.org>
On Fri, Oct 11, 2024 at 6:18 PM Greg Sabino Mullane <htamfids@gmail.com>
wrote:
> (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
>
Hi greg
Mail sent you with a new thread. composite key is on partitioned table
Regards,
Durga Mahesh
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Question on indexes
2024-10-10 18:49 Question on indexes sud <suds1434@gmail.com>
@ 2024-10-11 06:43 ` Laurenz Albe <laurenz.albe@cybertec.at>
2 siblings, 0 replies; 14+ messages in thread
From: Laurenz Albe @ 2024-10-11 06:43 UTC (permalink / raw)
To: sud <suds1434@gmail.com>; pgsql-general <pgsql-general@lists.postgresql.org>
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] 14+ messages in thread
end of thread, other threads:[~2024-10-11 13:37 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-19 15:51 Question on indexes Emil Rachovsky <zsevgymko@yahoo.com>
2005-12-19 16:29 ` vishal saberwal <vishalsaberwal@gmail.com>
2005-12-19 16:31 ` Alvaro Herrera <alvherre@commandprompt.com>
2024-10-10 18:49 Question on indexes sud <suds1434@gmail.com>
2024-10-10 18:51 ` Christophe Pettus <xof@thebuild.com>
2024-10-11 01:17 ` Efrain J. Berdecia <ejberdecia@yahoo.com>
2024-10-10 19:21 ` Erik Wienhold <ewie@ewie.name>
2024-10-10 19:44 ` sud <suds1434@gmail.com>
2024-10-10 20:36 ` Erik Wienhold <ewie@ewie.name>
2024-10-11 11:29 ` Greg Sabino Mullane <htamfids@gmail.com>
2024-10-11 12:31 ` Durgamahesh Manne <maheshpostgres9@gmail.com>
2024-10-11 12:48 ` Greg Sabino Mullane <htamfids@gmail.com>
2024-10-11 13:37 ` Durgamahesh Manne <maheshpostgres9@gmail.com>
2024-10-11 06:43 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox