591 questions
1
vote
1
answer
59
views
Slow TimescaleDB lookup on indexed column (event_key) without a time filter
I'm using a self-hosted TimescaleDB instance to store logs. I have a hypertable logs that is partitioned by the timestamp column.
I need to perform a fast lookup query based on the event_key column, ...
- 11
1
vote
1
answer
55
views
Optimal index for query by multiple fields from a JSONB column
I have a table in PostgreSQL like this:
CREATE TABLE test (
id BIGSERIAL PRIMARY KEY,
data_discriminator VARCHAR NOT NULL,
created_at TIMESTAMPTZ NOT NULL,
data JSONB ...
- 35
3
votes
1
answer
94
views
PostgreSQL Alignment theory not completely accurate?
I was reading this article on the Internet regarding alignment PostgreSQL.
Based on the example of the article and on some work experience I had with the alignment, I did a small experiment with the ...
- 51
2
votes
0
answers
71
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 ...
- 51
0
votes
0
answers
24
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
90
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
1
vote
2
answers
148
views
Query hint for IndexScan shows no effect, table still uses SeqScan
We have two tables in a Postgres DB. Table1 is a small temp table, Table2 is a huge regular table (> 1 million rows):
-- small, temp:
CREATE TEMPORARY TABLE Table1 (
uid VARCHAR(15)
, idx INTEGER
...
- 39
3
votes
3
answers
248
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 ...
- 829
1
vote
1
answer
126
views
How to fetch parent data and all it's children, then aggregate to array?
Postgres: v12
Link to test:
https://www.db-fiddle.com/f/pfnFVhFq1QTxjas6BB4sZv/3
There are transactions table and logs table. logs are linked to transactions by transaction_id. I need to query logs ...
- 1,770
1
vote
1
answer
96
views
How to LIMIT the number of parent values, but return all children for each?
Postgres: v12
Link to test:
https://www.db-fiddle.com/f/7BjKHTP7RGKKDfBDPtzm99/0
There are transactions table and logs table. logs are linked to transactions by transaction_id. I need to query logs ...
- 1,770
0
votes
3
answers
94
views
Filter out rows in 1:N LEFT JOIN where any row in child table fails condition
I have two tables, one which I will call D and one which I will call S, with a 1:N relationship between D and S. I currently have the following query that runs on these two:
SELECT d.*, s.*
FROM d
...
- 8,192
0
votes
2
answers
158
views
How to optimize a query when joining two tables?
My system is using PostgreSQL as the database. I am encountering an issue when using a query to join two large tables together. I'll briefly describe the tables as follows:
Location:
- id: uuid
- name:...
- 1,296
0
votes
2
answers
140
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 ...
- 532
0
votes
1
answer
448
views
How to Efficiently Index and Search Arrays with UNNEST in PostgreSQL
I have a products table with an array column tags that contains up to 700 elements.
I want to efficiently search for specific tag names for a given user to be used in a search bar.
Here's my query:
...
- 3,611
-3
votes
2
answers
97
views
Select count query very slow on cold cache [closed]
Problem
The following query takes 42 seconds when most of the data in not cached:
EXPLAIN (ANALYZE, BUFFERS) select count(*) from packages where company_id = 178381;
...
- 1,737
0
votes
1
answer
125
views
How to improve performance of the following query to get nearby ship location
I have a Postgres table named shipData(total rows 700 millions). Which has ship location with reporting time and location in geometry format(combination of lat & lon). I have a shipID, I want to ...
- 39
1
vote
1
answer
58
views
Efficient self-join on column value being the prefix of join column
How can I efficiently turn a set of strings into a table such that each strings is mapped to all other strings of which it is a prefix?
I use PostgreSQL 14 and deal with a table medical_codes of about ...
- 95.2k
0
votes
3
answers
669
views
Performance loss introducing lateral join in Postgres query
I am dealing with a PostgreSQL (v14) query of this kind
SELECT
...,
EXISTS (
SELECT 1
FROM table2 t2
WHERE t2.fk = t1.id
AND LOWER(t2.table_name) = 't1'
) ...
- 15
0
votes
3
answers
87
views
IN works slow without explicit data
This query:
select id
from user u
where u.account_id = 600
returns:
61,71,68,69,70,118,116,117,248,381,384,325,265,393
and it works really fast. This query:
select *
from logs l
where l.user_id in
(...
- 79
0
votes
1
answer
245
views
How to extract ranges which overlap with another range from PostgreSQL multirange?
I'd like to extract ranges which overlaps with another range from multirange using SQL.
For example, {[5,10), [14,20), [26,28), [29,31)} with {[15,27)} should result {[14,20), [26,28)}.
I can achieve ...
- 1
0
votes
1
answer
61
views
Query not optimized even after partitioned | Postgres
I have a table called tbl_inventory_detail_old as for the following DDL and it currently has 19M records. There are around 500 unique agent ids and around 35 unique product ids.
CREATE TABLE public....
- 461
2
votes
2
answers
655
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
1k
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,828
1
vote
1
answer
259
views
How to make a query with ILIKE filters on multiple columns in two tables?
I have a non-optimized query with ILIKE filters on 6 columns in the first table, and on one more column in the second table.
There are more than 13 million records in the table. This request is ...
- 462
1
vote
1
answer
57
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
2
answers
127
views
Complicated column dependencies in postgreSQL
I have two columns: column1 with two distinct values (0, 1) and column2 with 3 distinct values ('A', 'B', 'C'). Value in column1 is always 0 for 'A' and 'B' in column2, but if the value in column2 is '...
- 33
1
vote
1
answer
76
views
Different comparison column in WHERE clause causes unexpected slowdown
(All identifiers below are anonymized.)
I have the following query in Postgres:
SELECT id, dt
FROM table1 t1
WHERE status is not null
AND (
(NOT EXISTS (SELECT 1 FROM table2 t2 WHERE t2....
- 73
1
vote
1
answer
331
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.6k
3
votes
2
answers
110
views
Optimize query to insert random rows from tableA into tableB, without duplicates
I am still a novice to SQL, using PostgreSQL 16.2.
create table users(
user_id serial primary key
);
create table task_pool(
task_id serial primary key,
order_id integer,
clicker_id ...
- 75
2
votes
1
answer
108
views
Postgres SQL UDF Optimization (Not Inlining?)
I have a problem where I need to round timestamps representing end dates to the end of the current month or end of the prior month depending on where those timestamps fall relative to NOW() assuming ...
- 149
0
votes
2
answers
83
views
De-duplicate part of a table on given condition
This is my message table. Now I am trying to write an query to group records by collection_id (only first instance) if type multi-store else by message.id .
id | ...
- 165
0
votes
1
answer
87
views
How can I tell the SQL planner that a WHERE condition in the main query should be executed in the subqueries?
I am using Postgres 14.x.
I have a complex query that can be observed, next to it's EXPLAIN ANALYZE, here.
select *
from (select * from public.select_version_of_projects('2024-03-08T08:31:08.280Z')) ...
2
votes
1
answer
960
views
PostgreSQL Hash Join vs Nested Loop and hash index
I'm testing how join works with hash index in PostgreSQL 16.2. Here is a test table. Just 2 columns with numbers in text format.
create table join_test (
pk varchar(20),
fk varchar(20));
...
- 370
2
votes
1
answer
101
views
Optimize query for columns with distinct values incl. NULL
ID
Amount
Brand
1
10
NULL
1
20
NULL
2
30
Mazada
2
NULL
BMW
3
40
NULL
3
40
KIA
4
NULL
Honda
4
NULL
Honda
Above is the original table, and my goal is to find any difference within the same ID for each ...
- 87
3
votes
2
answers
191
views
Optimize query for columns with distinct values per ID
Got this question for a while and wondering if there is any faster query.
I have a table with multiple entries per ID, and would like to list all columns with different values for the same ID.
ID
...
- 87
6
votes
2
answers
4k
views
Why is a LATERAL JOIN faster than a correlated subquery in Postgres?
I have rewritten the below query from using a correlated subquery to using a LATERAL JOIN. It is now faster, and I would like to understand why?
This SO answer says that there is no golden rule and ...
- 1,972
2
votes
1
answer
163
views
Indexing strategy for filters including a JSONB property
I have a jsonb column created via sequelize migration this way:
queryInterface.addColumn('Entities', 'summary', {
type: Sequelize.DataTypes.JSONB,
})
My Postgres version:
PostgreSQL 15.2 (Debian ...
- 2,296
1
vote
0
answers
109
views
PostgreSQL query plans when using unnest() in SELECT list vs in subquery
I have two queries that would produce equivalent results (though the subquery one is sorted). However, it seems like the one that uses subquery is faster and I'm not sure why. Could someone explain ...
- 891
0
votes
2
answers
770
views
Terrible performance joining a view to a fast subquery / CTE in PostgreSQL
I have a PostgreSQL (v15) DB view that rolls up a bunch of data per user for a single organization, to display a report of data like fees owed/paid per user, etc. This is executed with an org ID and ...
- 20.5k
0
votes
0
answers
72
views
How to quickly query similar text through postgresql?
I need to query the top few texts that are most similar based on the input content.
The table structure is as follows：
create table documents (
id bigserial primary ...
- 23
0
votes
2
answers
108
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,507
3
votes
2
answers
130
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
0
votes
0
answers
58
views
Outliers produce a bad query plan
I have table document with ~2.5 millions rows and more than 100 columns. I provide only used in query columns:
CREATE TABLE document
(
id serial PRIMARY KEY,
organizationid INTEGER,
status_1 ...
- 33
0
votes
1
answer
298
views
How to improve bulk inserts to Postgres DB
I currently have a service in C# that uses dapper to call a stored procedure that does 2 things: if the customer exists, it grabs the customer GUID and adds it to the CustomerInformations table; if ...
- 2,173
-1
votes
2
answers
70
views
PostgreSQL better query performance
I have customers and visits table, and I would like to know which of these two queries has the better performance:
(I have indexes defined for those columns)
Query 1
SELECT
customers.id as id,
...
- 599
1
vote
2
answers
108
views
Test given IDs for existence in any row of a JSON column
In my Postgres 13.10 database, I have a table menus with a JSON column named dishes. This column contains values in the following format:
{
"desserts": [
{"id": 1, &...
- 61
0
votes
1
answer
35
views
How to select efficiently by list of dateranges?
I have two tables:
create table foo(
id serial primary key,
ts timestamp
);
create table bar(
id serial primary key,
ts_start timestamp,
ts_end timestamp
);
I need to select all rows from ...
2
votes
1
answer
366
views
Using PostgreSQL table as tumbling window persistence layer
Currently, I'm storing a large amount of time-series data into the following PostgreSQL table:
create table view (
user_id uuid,
product_id integer,
banner_id integer,
campaign_id integer,
...
- 7,064
0
votes
1
answer
68
views
Postgres - Select Query Optimization
I want to optimize the following DB SELECT query:
SELECT
ls.caller_id,
ls.caller_path_id,
ls.caller_path_name,
ls.caller_data->>'worker' AS worker,...
- 67
1
vote
1
answer
738
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