Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [gin-index]

27 questions
15
votes
1 answer

Postgresql multi-column GIN index

Suppose a postgresql table items like this. (keywords column is of type text[]) name account_id keywords ------------------------------ foo1 1 ['k1', 'k2'] foo2 1 ['k1', 'k3'] foo3 2 ['k4',…
Taha Jahangir
  • 385
  • 1
  • 3
  • 12
7
votes
4 answers

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 USING GIN(fulltext); INSERT INTO search (language,…
jaap3
  • 183
  • 1
  • 8
5
votes
2 answers

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 be indexed and executed in O (log n) time (or…
Chris Dutrow
  • 275
  • 1
  • 3
  • 7
4
votes
1 answer

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 index behavior, as well as as maybe get some insight…
Jeff B.
  • 141
  • 1
  • 3
4
votes
3 answers

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 column because the data is frequently selected by…
Artur
  • 41
  • 1
  • 4
3
votes
1 answer

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 do not have much experience with gin and gist…
Mp0int
  • 459
  • 1
  • 6
  • 14
3
votes
1 answer

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 being inserted and even more frequently…
Chris W.
  • 201
  • 1
  • 7
3
votes
1 answer

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 this big table into two or more tables, and create…
Dmitry Krylov
  • 31
  • 2
2
votes
1 answer

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' CONNECTION LIMIT = -1; After creating database you…
user44
  • 123
  • 4
2
votes
0 answers

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 INDEX ix_mytable_val ON mytable USING GIN…
IamIC
  • 576
  • 4
  • 12
2
votes
2 answers

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 understand if, under the hood, PostgreSQL is using a…
collimarco
  • 653
  • 2
  • 9
  • 20
2
votes
2 answers

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
  • 867
  • 2
  • 14
  • 29
2
votes
1 answer

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 does not overlap with any existing row, so I want…
pintoch
  • 123
  • 6
1
vote
1 answer

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 char phrases. As if there is higher cost when the…
Pawel Zieminski
  • 125
  • 6
1
vote
1 answer

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 search for "4 little lambs" with: SELECT * FROM mytable WHERE…
Cerin
  • 1,425
  • 6
  • 29
  • 38
1
2