Questions tagged [gin-index]
The gin-index tag has no usage guidance.
24
questions
0
votes
1
answer
71
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 ...
- 1,176
0
votes
0
answers
115
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 ...
1
vote
0
answers
401
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"
...
- 11
0
votes
2
answers
444
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,
...
- 101
0
votes
0
answers
262
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": {
...
- 11
0
votes
1
answer
68
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&...
- 615
0
votes
1
answer
62
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 ...
- 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 ...
- 513
3
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 ...
- 131
4
votes
3
answers
2k
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
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 ...
0
votes
1
answer
767
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 ...
- 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 ...
- 183
2
votes
1
answer
900
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
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 ...
- 131
1
vote
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
-------------...
- 615
1
vote
1
answer
196
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, ...
- 113
6
votes
4
answers
4k
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 ...
- 173
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 ...
- 123
13
votes
1
answer
13k
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 ...
- 365
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'
...
- 123
2
votes
0
answers
928
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 ...
- 576
3
votes
1
answer
967
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 ...
- 449
4
votes
2
answers
9k
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 ...
- 265