Questions tagged [gin-index]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
3
votes
3answers
81 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 ...
0
votes
2answers
73 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 ...
0
votes
1answer
241 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 ...
1
vote
1answer
373 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 ...
1
vote
1answer
408 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 ...
1
vote
1answer
703 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 ...
1
vote
2answers
452 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 -------------...
0
votes
1answer
85 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, ...
3
votes
3answers
1k 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 ...
2
votes
1answer
567 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 ...
4
votes
1answer
6k 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 ...
2
votes
1answer
1k 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' ...
2
votes
0answers
796 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 ...
3
votes
1answer
682 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 ...
4
votes
2answers
6k 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 ...