587 questions
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
1
vote
2
answers
101
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
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
1
vote
1
answer
108
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,766
1
vote
1
answer
88
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,766
0
votes
3
answers
86
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,094
0
votes
2
answers
139
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,276
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
0
votes
1
answer
147
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,591
-3
votes
2
answers
81
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
118
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
50
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 ...
- 94.7k
0
votes
3
answers
501
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
83
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