Using GIN Index to see if a nested key exists in JSONB

I have a table of semi-structured json that I am storing in a JSONB column with a GIN index: create table foo ( id text primary key, obj jsonb ); create index foo_obj on foo using gin (obj); I populated the table with 10,000 rows of randomly generated JSON objects, with

Parallel GIN index?

Any plans for $subject? -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com www.visena.com

Re: Efficient rows filter for array inclusion with gin index

which is supported by the gin index, the test for inclusion is fast and the query does not run a sequential scan over the whole "item_paths" table. However, because of the ARRAY[i2.ref_id] construct, it performs a sequential scan on i2. I was under the assumption that the ARRAY[] con

Efficient rows filter for array inclusion with gin index

; (uuid) that references their parent. In order to avoid recursive queries,there is a secondary table "item_paths" populated via triggers that have two columns "ref_id" (uuid that references a row in "items") and "item_path" (uuid[] which contains the path of

Re: GIN INdex is not used with && operator for a text array index

balasubramanian c r writes: > when Operator '@>' is used the index is used and the execution time is 60ms. Yeah ... note that it's predicted to return just one row, and that guess is correct: > Bitmap Heap Scan on public.address18 (cost=261.25..262.52 rows=1 width=4) > (actual time=58.992..58.

GIN INdex is not used with && operator for a text array index

HI Team Sorry for the spam. We have Postgres DB where the list of addresses are stored and for a given complete address trigram of addresses are stored in a column which is a text array. after looking at the list of operators that are available for gin index I decided to use array_ops operator

Re: Language options for GIN index support functions

Hi út 7. 2. 2023 v 3:49 odesílatel Phillip Diffley napsal: > Hello, > > The support functions and operator methods needed to extend a GIN index > are documented in C syntax > <https://www.postgresql.org/docs/current/gin-extensibility.html>. Do > these functions need to

Language options for GIN index support functions

Hello, The support functions and operator methods needed to extend a GIN index are documented in C syntax <https://www.postgresql.org/docs/current/gin-extensibility.html>. Do these functions need to be implemented in C, or is there an interface for other languages like PL/pgSQL? Thanks! Phillip

GIN Index Partial Match?

I create a gin index for a custom type. I want to use partial match, but I find extract query method called twice in a query, when in the first called, I return the minimal value of the type, and I want to set the maximal value of the type in extra data will be used in compare partial function

GIN Index use statistic information?

Hi: I create a new type for myself and create a gin index for its array type. I want to know : 1. how to use statistic info in build index for a table? 2. how to use analyze to generate statistic information?

Re: massive update on gin index

Em qua., 14 de set. de 2022 às 16:55, Tom Lane escreveu: > GIN does have a "pending list" of insertions not yet pushed into the main > index structure, and search performance will suffer if that gets too > bloated. I don't recall much about how to control that, but I think > vacuuming the table

Re: massive update on gin index

Marcos Pegoraro writes: > I know I have other options, and possibly better, but I was trying to > understand what happens with gin indexes, just that. GIN does have a "pending list" of insertions not yet pushed into the main index structure, and search performance will suffer if that gets too blo

Re: massive update on gin index

> > Did you try a simple array of phone numbers? If you really care about > mobile,work,home prepend the number with one of HMW. Easily stripped off > as necessary. I've had decent performance with arrays in the past. > I know I have other options, and possibly better, but I was trying to unders

Re: massive update on gin index

On 9/14/22 13:38, Guyren Howe wrote: You might consider defining a phone type that includes your “type” information, and just having an array of those, if you really want to do something like this. But a related table instead would be the obvious answer. Did you try a simple array of phone n

Re: massive update on gin index

> 2236279878}]'; > explain analyze select * from People where Mobile = 2236279878 or Work = > 2236279878 or Home = 2236279878; > > But then I repeated 2 or 3 times that update which stores those 3 phones on > json and then my gin index became slow, very very slow, why ? > &

massive update on gin index

= 2236279878; But then I repeated 2 or 3 times that update which stores those 3 phones on json and then my gin index became slow, very very slow, why ? select using btree on 3 phone numbers - Execution Time: 0.164 ms select using gin on json on first update - Execution Time: 0.220 ms select using

Re: Creating A GIN index on JSONB column (large database)

800GB/20TB = 4%. You've got a 20TB database, and run out of space when *4% is added*. Either you're running way to close to the edge, or I misunderstand something. Either way, enable auto-scaling. https://aws.amazon.com/about-aws/whats-new/2019/06/rds-storage-auto-scaling/ On 8/9/22 10:29,

Re: Creating A GIN index on JSONB column (large database)

a requirement to make the json searchable using GIN indexes. however when we try to create the GIN index on AWS RDS our temp storage maxes out which crashes the create process resulting in unhealthy created indexes. This might be an AWS issue but we are wondering the reason why creating these

Re: Creating A GIN index on JSONB column (large database)

Yes, the database was always going to be a minimum of 20TB however we build the indexes on the partition which at max is about 1.3TB, which in temp files will use about 800GB temporary files. Note it doesn't have the same effect when doing non-concurrently. I don't think rolling off would help as i

Re: Creating A GIN index on JSONB column (large database)

> On Aug 9, 2022, at 7:45 AM, Taylor Smith wrote: > > Thanks Rob, > > This is what I was thinking. Would you say it presents any risk then having a > database unable to rebuild its own indexes if needed? > > No I would not. The scale-up + rebuild should tell you how much you actually ne

Re: Creating A GIN index on JSONB column (large database)

t; Hi all, > > > > I have a database that is in excess of 20TB in size, partitioned by date > on a month to month basis. > > > > There is a column within that stores text (validated to be json but not > stored as JSONB). I have a requirement to make the json searchable u

Re: Creating A GIN index on JSONB column (large database)

B). I have a requirement to make the json searchable using GIN > indexes. however when we try to create the GIN index on AWS RDS our temp > storage maxes out which crashes the create process resulting in unhealthy > created indexes. > > This might be an AWS issue but we a

Creating A GIN index on JSONB column (large database)

the GIN index on AWS RDS our temp storage maxes out which crashes the create process resulting in unhealthy created indexes. This might be an AWS issue but we are wondering the reason why creating these indexes (concurrently) would cause the temp storage to blow up and crash out and is it possible

Performance issue on GIN index with gin_trgm_ops index column

Hi all, We are investigating a performance issue with searching on a GIN with gin_trgm_ops indexed column. This specific (organization_id,aggregate_type) has in total 19K records and the (organization_id) has in total 30K records. The search record table has in total 38M records. The table and

GIN index operator ?(jsonb,text) not working?

Hi, dbfiddle: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=060af497bbb75ecddad9fd2744f8022b --- create table test101 ( doc_id bigserial, document jsonb); insert into test101(document) values ('{"user_removed" :false}') returning *; insert into test101(document) select '{"user_removed" :false}

Would it be possible to utilize a GIN index to query for distinct values ?

to use the index to query for distinct / count distinct values, at least for some key types ? For instance, say I have a GIN index on a single, highly cardinal but non-unique text column (a-la 'name'); from my very limited understanding, would it be possible to query for distinct / coun

Re: GIN index

Hi, On Tue, Jan 25, 2022 at 02:42:14AM +, huangning...@yahoo.com wrote: > Hi:I created a new variable-length data type, and now I want to create a GIN > index for it. According to the rules of GIN index, I created three functions: > extractValue, extractQuery, and compare. I made

GIN index

Hi:I created a new variable-length data type, and now I want to create a GIN index for it. According to the rules of GIN index, I created three functions: extractValue, extractQuery, and compare. I made sure that the return value of the first two functions is the address of the array, but when

Re: GIN index

Hi, Why exactly are you starting a new thread again, instead of continuing in the thread you started a couple days ago? A couple recommendations: 1) I find it unlikely you'll get a lot of help unless you provide a way to reproduce the issue easily. That is, something people can build and te

Re: create a new GIN index for my own type

On 10/4/21 3:32 PM, huangning...@yahoo.com wrote: I have already debugged the program according to this step, but I found that in the DataCopy function, the variable typlen should be 8, but it is -1, Well, if you have debugged this, it'd be nice if you could share more information (e.g. bac

Re: create a new GIN index for my own type

On 10/4/21 8:30 AM, huangning...@yahoo.com wrote: Hi: I created a new data type, and then I wanted to create a GIN index for it, but when I created the index, the program would crash 。 The version of postgresql is 9.6。 The following is part of the code, and I also refer to the code of

create a new GIN index for my own type

Hi:  I created a new data type, and then I wanted to create a GIN index for it, but when I created the index, the program would crash 。  The version of postgresql is 9.6。 The following is part of the code, and I also refer to the code of intarray. ```sqlCREATE OR REPLACE FUNCTION geomgrid_in

Re: Crashing on insert to GIN index

tes: > > I am defining a new type, FooBar, and trying to create a GIN index for > it. > > Everything is working well without the index. FooBar values are getting > > into a table, and being retrieved and selected correctly. But I'm > getting a > > crash when I add

Re: Crashing on insert to GIN index

Jack Orenstein writes: > I am defining a new type, FooBar, and trying to create a GIN index for it. > Everything is working well without the index. FooBar values are getting > into a table, and being retrieved and selected correctly. But I'm getting a > crash when I add a GIN ind

Crashing on insert to GIN index

I am defining a new type, FooBar, and trying to create a GIN index for it. Everything is working well without the index. FooBar values are getting into a table, and being retrieved and selected correctly. But I'm getting a crash when I add a GIN index on a column of type FooBar. Here i

Re: Regarding creation of gin index on column that has varchar datatype

erator class for access method "gin" > > HINT: You must specify an operator class for the index or define a > > default operator class for the data type ) while i try to create gin > > index on vch_message column of slp01 table (CREATE INDEX ON slp01 > >

Re: Regarding creation of gin index on column that has varchar datatype

ator class for the data type ) while i try to create gin index on vch_message column of slp01 table (CREATE INDEX ON slp01 using gin(vch_message);) vch_message column has lot of this info like {"requestBody":{"firstName":"SALVATORE","lastName":"NAP

Regarding creation of gin index on column that has varchar datatype

Hi Respected to PGDG GLOBAL TEAM I am getting this error( ERROR: data type character varying has no default operator class for access method "gin" HINT: You must specify an operator class for the index or define a default operator class for the data type ) while i try to create gi

Using GIN index to retrieve distinct values

Hi all, My understanding of a GIN index is that it maps each unique key to the set of items containing that key. That suggests to me that enumerating the distinct keys efficiently using the index should be possible in some cases. I understand that given the nature of the GIN framework, the

Re: GIST/GIN index not used with Row Level Security

> > > > I've updated word_similarity_op(text,text) to be leakproof, and > > pg_proc agrees it is. I'm assuming word_similarity_op() is equivalent to > > <%, though I haven't found explicit confirmation. However, using > > word_similarity() instead of <% on a 100k row table, without any RLS > > invo

Re: GIST/GIN index not used with Row Level Security

Greetings, Please don't top-post on these lists. * Derek Hans (derek.h...@gmail.com) wrote: > Thanks for the detailed response, super helpful in understanding what's > happening, in particular understanding the risk of not marking functions as > leakproof. I'll take a look at the underlying code

Re: GIST/GIN index not used with Row Level Security

Thanks for the detailed response, super helpful in understanding what's happening, in particular understanding the risk of not marking functions as leakproof. I'll take a look at the underlying code to understand what's involved in getting a function to be leakproof. That said, it does seem like i

Re: GIST/GIN index not used with Row Level Security

Greetings, * Derek Hans (derek.h...@gmail.com) wrote: > Unfortunately only "alter function" supports "leakproof" - "alter operator" > does not. Is there a function-equivalent for marking operators as > leakproof? Is there any documentation for which operators/functions are > leakproof? Tom's quer

Re: GIST/GIN index not used with Row Level Security

Derek Hans writes: > Unfortunately only "alter function" supports "leakproof" - "alter operator" > does not. Is there a function-equivalent for marking operators as > leakproof? Leakproofness is a property of the underlying function, not the operator, so that's where you change it. > Is there an

Re: GIST/GIN index not used with Row Level Security

Thanks for the pointer for marking functions as leakproof, I was unaware of that whole concept. Unfortunately only "alter function" supports "leakproof" - "alter operator" does not. Is there a function-equivalent for marking operators as leakproof? Is there any documentation for which operators/fu

Re: GIST/GIN index not used with Row Level Security

Derek Hans writes: >> However, wild-guess time: it might be that without access to the >> table statistics, the "search like '%yo'" condition is estimated >> to be too unselective to make an indexscan profitable. And putting >> RLS in the way would disable that access if the ~~ operator is not >>

Re: GIST/GIN index not used with Row Level Security

> > > Your example is obscuring the issue by incorporating a tenant_name > condition (where did that come from, anyway?) in one case and not > the other. Without knowing how selective that is, it's hard to > compare the EXPLAIN results. > > That's RLS kicking in - RLS condition is defined as ((ten

Re: GIST/GIN index not used with Row Level Security

> > > What are the RLS policies on the table? > > From select * from pg_policies: "((tenant_name)::name = CURRENT_USER)" > What is the definition of the GIN index? > > CREATE INDEX search__gist ON public.search USING gist (search COLLATE pg_catalog."

Re: GIST/GIN index not used with Row Level Security

the definition of the GIN index? Best guess is the RLS is preventing access to the field needed by the index. select * from search where search like '%yo' Creates this query plan: "Seq Scan on search  (cost=0.00..245.46 rows=1 width=163)" "  Filter: (((tenant_name):

Re: GIST/GIN index not used with Row Level Security

Derek Hans writes: > When using row level security, GIN and GIST indexes appear to get ignored. > Is this expected behavior? Can I change the query to get PostgreSQL using > the index? For example, with RLS enabled, this query: Your example is obscuring the issue by incorporating a tenant_name co

GIST/GIN index not used with Row Level Security

n search (cost=4.49..96.33 rows=44 width=163)" " Recheck Cond: (search ~~ '%yo'::text)" " -> Bitmap Index Scan on search__gist (cost=0.00..4.48 rows=44 width=0)" "Index Cond: (search ~~ '%yo'::text)" I see the same behavior with more

Re: JSONB Array of Strings (with GIN index) versus Split Rows (B-Tree Index)

On Sun, Feb 3, 2019 at 10:35 PM Syed Jafri wrote: > · Receiver names are of the type (a-z, 1-5, .) > > · 95% of all queries currently look like this: SELECT * FROM table > WHERE Receiver = Alpha, with the new format this would be SELECT * FROM table > WHERE receivers @> '"Alpha"'

Re: JSONB Array of Strings (with GIN index) versus Split Rows (B-Tree Index)

On 2019-02-04 05:34, Syed Jafri wrote: > |-|--|-|--| > | Receiver | Event | Date| > Location| > |-|--|-|--

JSONB Array of Strings (with GIN index) versus Split Rows (B-Tree Index)

| USA | |-|--|-|--| While redesigning the database, I have considered using an array with a GIN index instead of the current B-Tree index on receiver. My proposed new table would look like this

Re: GIN Index for low cardinality

mallish values (which low cardinality columns tend to be) the per-tuple > overhead and the pointer itself is probably much larger than the value, so > hash won't save you much if any space. The GIN index removes not just the > value, but the per-tuple overhead. And also compr

Re: GIN Index for low cardinality

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

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

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

for instance. > For smallish values (which low cardinality columns tend to be) the per-tuple overhead and the pointer itself is probably much larger than the value, so hash won't save you much if any space. The GIN index removes not just the value, but the per-tuple overhead. And also co

Re: GIN Index for low cardinality

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

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

> 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 Index for low cardinality

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 short is perfect for indexing columns where there are lot of re