Re: GIN Index for low cardinality

2018-11-14 Thread Олег Самойлов
Eh, I checked, you are right. Something terribly wrong with hash index in PostgreSQL. But there are another hash index gin(jsonb_path_ops), may be correctly say gin+hash index. Looked like it is the best for this purpose. > 26 окт. 2018 г., в 19:27, Jeff Janes написал(а): > > On Thu, Oct 25, 2

Re: GIN Index for low cardinality

2018-10-26 Thread Jeff Janes
On Fri, Oct 26, 2018 at 12:27 PM Jeff Janes wrote: Here is a real-world example from one of my databases where each value is > about 17 characters long, and is present about 20 times: > > gin: 411 MB > btree: 2167 MB > hash: 2159 MB > For what it is worth, that was 9.6 with freshly rebuilt index

RE: GIN Index for low cardinality

2018-10-26 Thread Igor Neyman
From: Ozz Nixon Sent: Friday, October 26, 2018 12:50 PM To: jeff.ja...@gmail.com Cc: spl...@ya.ru; srkrish...@aol.com; pgsql-general@lists.postgresql.org Subject: Re: GIN Index for low cardinality Jeff, Great info! Your example on Mr. Mrs. Miss, etc. is there a good rule of thumb that if

Re: GIN Index for low cardinality

2018-10-26 Thread Ozz Nixon
Jeff, Great info! Your example on Mr. Mrs. Miss, etc. is there a good rule of thumb that if the data is under "x"KB an index is overhead not help? I am not worried about space, more interested in performance.

Re: GIN Index for low cardinality

2018-10-26 Thread Jeff Janes
On Thu, Oct 25, 2018 at 9:36 AM Олег Самойлов wrote: > > 17 окт. 2018 г., в 13:46, Ravi Krishna написал(а): > > In > https://www.cybertec-postgresql.com/en/ideas-for-scaling-postgresql-to-multi-terabyte-and-beyond/ > > it is mentioned: > > "GIN, the most know non-default index type perhaps, has

Re: GIN Index for low cardinality

2018-10-26 Thread Jeff Janes
On Wed, Oct 17, 2018 at 6:47 AM Ravi Krishna wrote: > In > https://www.cybertec-postgresql.com/en/ideas-for-scaling-postgresql-to-multi-terabyte-and-beyond/ > > it is mentioned: > > "GIN, the most know non-default index type perhaps, has been actually > around for ages (full-text search) and in s

Re: GIN Index for low cardinality

2018-10-25 Thread Ravi Krishna
>>Does it mean that GIN is a very good choice for low cardinality columns.   >Not necessary. There is other index which also don’t keep column value in an >every leaf. Hash, for instance.  Well I asked about GIN's usefulness for low cardinality. Good to know that Hash can also be considered. BT

Re: GIN Index for low cardinality

2018-10-25 Thread Олег Самойлов
> 17 окт. 2018 г., в 13:46, Ravi Krishna написал(а): > > In > https://www.cybertec-postgresql.com/en/ideas-for-scaling-postgresql-to-multi-terabyte-and-beyond/ > > > > it is mentioned: > > "