All Questions
Tagged with postgresql-performance query-optimization
80 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
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
1
answer
55
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
1
vote
1
answer
71
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....
- 63
0
votes
1
answer
75
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
743
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));
...
- 350
4
votes
2
answers
3k
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,942
0
votes
2
answers
626
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.4k
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
votes
2
answers
65
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
0
votes
1
answer
64
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
372
views
Why Postgres does not use GIN index if I specify a LIMIT clause?
I have a table containing about 5 million records and a GIN index on Barcodes column.
I have this query:
SELECT T1.*
FROM public."Skus" AS T1
WHERE (T1."Barcodes" && '{ &...
- 3,048
0
votes
2
answers
81
views
Looking for a more performant query
I have 2 tables with flex one-to-many relationship, meaning the parent side may not have any child:
Parent_table
id dim1 price
1 "abc" 1.00
2 "abc" 2.00
3 "def&...
- 1,017