Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [gin-index]

21 questions
10
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
  • 305
  • 1
  • 3
  • 11
5
votes
3 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
  • 163
  • 1
  • 7
4
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
  • 265
  • 3
  • 7
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 Siekielski
  • 41
  • 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…
FallenAngel
  • 439
  • 1
  • 5
  • 13
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
  • 11
2
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.
  • 21
  • 1
2
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.
  • 183
  • 7
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
  • 5
1
vote
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
  • 363
  • 4
  • 13
1
vote
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
  • 11
  • 1
1
vote
1 answer

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 pg_trgm Then tried the following indexes and got…
Atihska
  • 131
  • 6
1
vote
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
  • 475
  • 6
  • 19
0
votes
2 answers

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, "user" integer NOT NULL, "submit" integer NOT…
HelloMachine
  • 1
  • 2
1
2