All Questions
113 questions
0
votes
0
answers
21
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
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
3
votes
3
answers
216
views
Slow query on big table - with many different filters possible
I have a table:
create table accounts_service.operation_history (
history_id bigint generated always as identity primary key,
operation_id varchar(36) not null unique,
operation_type ...
- 821
0
votes
2
answers
93
views
How to search for the acronym of a string column in an indexable way
I'm working on an express.js project, using Sequelize as the orm, and postgres as the engine.
I'm working on improving the performance of a slow select query that does many things, and one of them is ...
- 534
2
votes
2
answers
447
views
Optimize query on partitioned table without partitioning key in the WHERE clause
We are trying to optimize a query to a partitioned table, the query looks something like this:
SELECT col1, col2
FROM partitioned_table
WHERE profile_id = '00000000-0000-0000-0000-000000000000'
AND ...
- 23
1
vote
2
answers
847
views
How to make Postgres use an index for a set of values?
I have a table with ~35M rows, and trying to find "processed" records to remove from time to time. There are 14 valid statuses, and 10 of them are processed.
id uuid default uuid_generate_v4(...
- 6,718
1
vote
1
answer
51
views
Optimizing query to filter many-to-many relationship on row-count
My model:
class RecipeTag(models.Model):
tag = models.CharField(max_length=300, blank=True, null=True, default=None, db_index=True)
recipes = models.ManyToManyField(Recipe, blank=True)
...
- 13
0
votes
1
answer
241
views
How to use date_trunc() with timestamptz in an index to support a join?
I have 2 tables in my database as defined below:
CREATE TABLE metric_events (
id uuid PRIMARY KEY,
metric_id integer NOT NULL,
event_at timestamp with time zone NOT NULL,
code text NOT ...
- 10.3k
0
votes
2
answers
101
views
Optimize a query using multicolumn indices
Say you want to optimize a query in a postgres database like:
SELECT DISTINCT ON (first)
first,
second,
third
FROM my_table
WHERE
second > 100
AND fourth = 3
ORDER BY
first,
...
- 3,497
3
votes
2
answers
109
views
Compound index with date function doesn't allow index-only scans?
I'm trying to use a compound (multicolumn) index on a table to assist in creating daily report counts. I am using Postgres 13, and my table looks like this:
CREATE TABLE inquiries (
id bigint NOT ...
- 180
1
vote
1
answer
640
views
Postgres using two indexes for WHERE clause in parallel
I have table which has a few billion rows. There are two columns in the table:
match_id uuid,
group_id integer
And there are indexes created on both of the above columns:
create index if not exists ...
- 5,606
1
vote
1
answer
900
views
In Postgres partial index is taking more time and cost to execute than normal index
I was comparing the performance of a normal index and partial index in Postgres and to my surprise, the partial index performance seems sub-optimal compared to the normal index.
The explain statement ...
- 33
0
votes
1
answer
86
views
Unable to use index in specific situations
I am facing a strange and infuriating problem of my PostgreSQL 10 database not using indexes in specific situations. I have created an index of an md5 hash of one of the columns, and this index seems ...
1
vote
2
answers
409
views
Covering index to improve performance of a SELECT query?
I have following Postgres query that runs over a large table and whenever it does, I observe a high CPU load that reaches 100% :
SELECT id, user_id, type
FROM table ss
WHERE end_timestamp > ...
- 105