Questions tagged [gin-index]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
1 answer
38 views

Should I consider GIN index for several columns?

In our application we have a field that can be used to match with several columns in our database. So if the user searches for somestring we will return all the rows WHERE column1 = 'somestring' OR ...
zefciu's user avatar
  • 101
0 votes
1 answer
36 views

Missing GIN support function (4 or 6) for attribute 1 of index

I have a problem creating GIN indexes in postgres 14.9 and GIN 1.3 The following is the query: CREATE TABLE assert.test_gin(col1 func.doc_sub_type[]); CREATE INDEX ON assert.test_gin USING gin(col1); ...
Ali Azimi's user avatar
0 votes
1 answer
55 views

Postgresql strange behavior with non-ascii characters with triagram index present

I see some strange behavior when using either gin_trgm_ops or gist_trgm_ops indexes. There seems to be quite a difference in plans when using say ILIKE or ~ and search for ascii phrases vs multi-byte ...
Pawel Zieminski's user avatar
0 votes
1 answer
196 views

How to search for partial matches from a GIN index in a multi-word PostgreSQL query?

I have a GIN index in a Postgres database that I'm currently using to do full text search. So if a tsvector field contains the vectorized form of "mary had 4 little lambs" then I could ...
Cerin's user avatar
  • 1,345
0 votes
0 answers
333 views

What should I choose between jsonb array and jsonb map for faster querying?

I have a jsonb column which will consist of some keys and the rows will be searchable using those keys. Considering that I will be applying GIN index on the jsonb column what should be the ideal ...
user2375245's user avatar
1 vote
0 answers
599 views

Optimize select query with OR and ILIKE

I have the following query for my PostgreSQL 13 database: SELECT * FROM "user" INNER JOIN "comment" on "comment"."user_id" = "user"."id" ...
Kamal Karain's user avatar
0 votes
2 answers
952 views

PostgreSQL btree index for int with character varying (ILIKE) not working

Hello I have about 50 million forums and each forum has over 30 million topics which this is my topic structure in PostgreSQL CREATE TABLE public.forum_topic ( "forum" integer NOT NULL, ...
HelloMachine's user avatar
0 votes
0 answers
408 views

Convert jsonb column into view for Grafana

I have a partially dynamic data that I have to save into Postgres 13 and then visualize it in Grafana. Example for one document insert (partial) { "Timestamp": { "$date": { ...
ivans's user avatar
  • 11
0 votes
1 answer
146 views

How can I make an index covering a ts_vector expression and also an integer?

I am using the ruby pg_search gem, where code like this: Food.search("orange") produces a query like this. SELECT "foods".* FROM "foods" INNER JOIN (SELECT "foods&...
John Bachir's user avatar
0 votes
1 answer
127 views

Composite multicolumn index for geopoint range and numeric range query

I am building an app where the server needs to select rows based on some criteria/filters. One of them is the location of the user and the radius at which the user want's to see posts and other ...
Najib's user avatar
  • 221
2 votes
2 answers
2k views

Does PostgreSQL btree_gin extension use btree or gin data structure?

In order to define a GIN index on an array and scalar field (for example) you need to enable the btree_gin extension in PostgreSQL. Then you can define your indexes as a GIN index. However I don't ...
collimarco's user avatar
4 votes
1 answer
2k views

Understanding composite BTREE + GIN_TRGM_OPS index prioritization & odd lower() behavior

hoping someone can try to help me decrypt some index behavior. I'm working on enabling some simple contains type lookups on various user-data columns (~varchar < 255) and trying to understand the ...
Jeff B.'s user avatar
  • 141
4 votes
3 answers
3k views

Any drawbacks of using GIN PostgreSQL index for an integer foreign key?

I have a large table (600 millions rows) with a foreign key other_id of type integer. A single value of the foreign key is repeated about 100 times on average. I need to have an index on that FK ...
Artur Siekielski's user avatar
0 votes
2 answers
2k views

Gin indexing in postgres

I have four columns project_id, custom_event(boolean), page_view(boolean), event_url(text). I usually do like a regex operation in event_url column and my query will be all the four columns in that ...
NAREN PUSHPARAJU's user avatar
0 votes
1 answer
985 views

Queries using GIN index on f_unaccent() seem slow?

I have almost 20M rows in one table that contains product, including their names. I wish to search fulltext and fast by the name so I created this index: CREATE INDEX ...
Michal's user avatar
  • 3
2 votes
1 answer
1k views

Occasional/intermittent, slow (10+-second) UPDATE queries on PostgreSQL table with GIN index

The Setup I am running PostgreSQL 9.4.15 on an SSD-based, quad-core Virtual Private Server (VPS) with Debian Linux (8). The relevant table has approximately 2-million records. Records are frequently ...
Chris W.'s user avatar
  • 181
2 votes
1 answer
1k views

How to create some GIN index concurrently in Postgresql

I have a large table: CREATE TABLE hh(h int8[] not null, file int8 not null), and GIN-index over h field (CREATE INDEX ON hh USING gin(h)). Index was created is about 8 minutes, so I decide to split ...
Dmitry Krylov's user avatar
1 vote
1 answer
2k views

How to create pg_trgm compound indexes with date columns

SELECT col1, max(date1) as max_date FROM table WHERE col1 ILIKE 'name' GROUP BY col1 TYPES:Here col1 is varchar and date1 is timestamp with time zone data type. So created extension CREATE EXTENSION ...
Atihska's user avatar
  • 131
2 votes
2 answers
2k views

What's the best way to use a gist index on tsrange to check if a time occurs after the range?

table (simplified) Table "public.events" Column | Type | Modifiers -------------...
John Bachir's user avatar
1 vote
1 answer
263 views

PostgreSQL: Index Trigger / Hook? For Synonyms

Is it possible, in PostgreSQL, to add a trigger to modify data prior to it going into a full text index? PostgreSQL requires using files to add custom dictionaries for things like stop words, ...
Jim's user avatar
  • 113
7 votes
4 answers
5k views

PostgreSQL GIN index not used when ts_query language is fetched from a column

I've got a table that stores some multilingual content: CREATE TABLE search ( content text NOT NULL, language regconfig NOT NULL, fulltext tsvector ); CREATE INDEX search_fulltext ON search ...
jaap3's user avatar
  • 183
2 votes
1 answer
1k views

Forcing postgres to use a GIN index on a varchar[]

In a postgres 9.4 database, I have a table with a field identifiers: varchar(512)[] that contains a list of identifiers for each row. When adding a new row, I want to make sure its identifiers list ...
pintoch's user avatar
  • 123
13 votes
1 answer
16k views

Postgresql multi-column GIN index [duplicate]

Suppose a postgresql table items like this. (keywords column is of type text[]) name account_id keywords ------------------------------ foo1 1 ['k1', 'k2'] foo2 1 ...
Taha Jahangir's user avatar
2 votes
1 answer
2k views

Postgresql not using GIN trigram index when performing non-ASCII LIKE query?

Steps to reproduce Create database CREATE DATABASE citiesdb WITH OWNER = citiesowner ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'C' LC_CTYPE = 'C' ...
user44's user avatar
  • 123
2 votes
0 answers
964 views

PostgreSQL index array of int4range using GIN / GIST - custom operator class

Here is my table: CREATE TABLE mytable ( id INT NOT NULL PRIMARY KEY, val int4range[] ); I want to index the val column: CREATE ...
IamIC's user avatar
  • 576
3 votes
1 answer
1k views

Index method for very few updates and many inserts

I am using Postgresql 9.1 with *pg_trgm* extension. I need to create an index on a text-based field. I do not need full-text searches, I use ILIKE queries to make my searches. I will use pg_trgm but ...
Mp0int's user avatar
  • 459
4 votes
2 answers
10k views

Inner join using an array column

Having trouble indexing and executing a query in O (log n) time. The query includes an INNER JOIN, an ORDER BY, and an equality operation. If I understand the laws of databases correctly, a query can ...
Chris Dutrow's user avatar