All Questions
Tagged with indexing postgresql
1,947 questions
0
votes
0
answers
20
views
PostgreSQL Choosing Full Index Over Partial Index
I am using PostgreSQL 17.2 and working with the postgres_air Database.
I have a very simple query:
SELECT status
FROM postgres_air.flight
WHERE status = 'Canceled';
And the following indexes:
CREATE ...
- 1
1
vote
1
answer
42
views
How to use index to search if trim is used in search value
My Postgres 17 database has a large table with 245 columns containing an indexed column ribakood:
CREATE TABLE firma2.toode
(
...
ribakood character(20) COLLATE pg_catalog."default",
...
...
- 28k
3
votes
3
answers
87
views
Which column to index in multicolumn indexing?
UserID
First
Middle
Last
Type
CreatedAt
123
John
Henry
Doe
Mage
03-28-2025
Let's say I have the following table above. I would like to create index to help speed up my queries.
All queries would like ...
- 41
1
vote
2
answers
57
views
Compound index on database Log table: (Status, CreatedTime) or (CreatedTime, Status)?
I usually use a compound key (CreatedTime, Status) for my Log table, but I’m reconsidering this design. Since CreatedTime is typically very unique and Status only has 3-5 possible values, it seems ...
- 20.9k
0
votes
0
answers
49
views
How does one correctly define an index for efficiently querying the reverse relation for ForeignKey fields? (when starting from parent.related_set)
FINAL EDIT: Okay, I've been asked to refine my question to as someting more specific/concrete. So here is the question:
Given the following model definitions:
class ParentModel(models.Model):
# ...
- 48
0
votes
0
answers
21
views
Index is not used when querying multiple values from JSON array [duplicate]
Having a table with a metadata column of the JSON type I am failing to utilize the full power of the index.
Creating a new table
CREATE TABLE IF NOT EXISTS phrases (
phraseId BIGINT,
metadata ...
- 544
1
vote
1
answer
62
views
Index is not used when querying JSON array
Having a table with a metadata column of the JSON type I am failing to utilize the full power of the index.
Creating a new table
CREATE TABLE IF NOT EXISTS phrases (
phraseId BIGINT,
projectId ...
- 544
2
votes
1
answer
75
views
Index for ILIKE + unaccent()
I have a lot of queries using
...
WHERE company_id = 2
AND unaccent(name) ILIKE unaccent('value');
Is possible to create a index to optimize this type of query? I take a look at GIN and pg_trgm but ...
- 1,396
0
votes
3
answers
53
views
How to combine LEFT and LOWER in a multicolumn index for leading text patterns?
I create an index this way:
CREATE INDEX rep_tval_idx ON public.rep USING btree (t, lower(left(val, 127)));
Then I run a SELECT with matching filter:
explain
select * from rep
where t=3 and lower(...
- 11
0
votes
1
answer
125
views
Change the index access method in PostgreSQL?
I have a bunch of PostgreSQL (v14 or 15) indexes over a few dozen tables that use B-Trees for their access method. We want to change them to hashes as we don't do inequality operations on them, and ...
0
votes
2
answers
89
views
How to index a varchar column of unlimited length in Postgres?
I am using a Postgres table to store HTML data. One of the columns is of type varchar, and this is where the HTML data is stored. My understanding is that this column datatype has no maximum length.
I ...
- 17.9k
-1
votes
1
answer
65
views
What is the correct strategy for indexing a postgres database for filtering
I want to create indexes in my postgres DB which contains a massive amount of data. The Usecase is to have a frontend, which should be able to filter a few selected fields and in the backend based on ...
- 225
0
votes
3
answers
104
views
Fastest way to find SQL intersection between 2 subsections of a huge dataset?
I have a gigantic indexed (104 million row) table of docs, pages and words:
CREATE TABLE my_table (
doc_id integer,
page_id integer,
word_id integer
);
CREATE INDEX my_table_word ON ...
- 4,483
1
vote
1
answer
64
views
GIN index not being used with NOT?
I have a table with a column itemsIds containing an array of ids. I have created a gin index for that column. I'm trying to find rows that don't contain an element with the following query:
select * ...
0
votes
1
answer
31
views
PostgreSQL multi-column index including text and text array columns
I have a table containing email data.
The from column is text (character varying).
The to column and the cc column are both arrays of text (also character varying).
These will be searched using entire ...
- 4,537