Questions tagged [postgresql-performance]

Performance issues with PostgreSQL queries

Filter by
Sorted by
Tagged with
-3 votes
0 answers
16 views

slow postgres performance with simple querys

after alot of reading i found multiple sources saying postgres should be able to handle ~100 million rows i set up a simple table with: CREATE TABLE trade ( id INT PRIMARY KEY NOT NULL, symbol ...
user avatar
2 votes
0 answers
21 views

Postgresql 14 Performance regression versus PG13

I migrated a database from Postgresql 13 to Postgresql 14 recently, and I saw a x2 execution time in a specific case which I can't understand. Here is how to reproduce the setup: -- DB Setup CREATE ...
user avatar
  • 21
0 votes
0 answers
10 views

Optimizing queries that involve rank computing PostgreSQL full-text search

Suppose we have a MATERIALIZED VIEW that has to be searched using PostgreSQL full text search. View contains both fields with information about the object and precompiled (and annotated with weigths) ...
user avatar
  • 1
0 votes
0 answers
22 views

Why is my time range query slow even when I have BRIN index on the timestamp?

I have a table that stores chat messages, like so; id (int8), timestamp (timestamp), author (text), channel(text), content(text) I have BTREE indexes on all of the fields, except for timestamp, which ...
user avatar
  • 101
0 votes
1 answer
39 views

How to incrementally optimize a `COUNT(*) ... GROUPBY` query in PostgreSQL?

I have a PostgreSQL table with ~millions of rows (timestamp, event, distinct_id, properties) where I want to filter based on event (low cardinality) and timestamp, and group by date_trunc('week', ...
user avatar
0 votes
2 answers
27 views

How to enforce only one NULL value per unique combination of some other columns?

here's the situation, I have a table A with like such CREATE TABLE A ( id UUID NOT NULL PRIMARY KEY, B_id bigint NOT NULL, C_id bigint NOT NULL, some_value ...
user avatar
0 votes
1 answer
17 views

Why parallel aggregation is not used for subquery when other subquery using non parallel aggregation?

I defined 2 equivalent aggregates. First supports parallel mode, but second one doesn't. I defined 2 identical tables with index. create table jsfield(j jsonb); insert into jsfield select ...
user avatar
1 vote
1 answer
47 views

Moving from Amazon RDS to own server made queries slow (maybe because heap fetches increased?)

Hope you're doing good. I've recently moved from Amazon RDS to my own PostgreSQL instance in Hetzner (in order to colocate it with the server and reduce the roundtrip). However I think that some ...
user avatar
  • 11
0 votes
0 answers
35 views

Speed up slow Postgres query with many joins

My site has a query that joins several tables, some with many thousands of rows. When the cache misses, this can leave users waiting for 10+ second queries, sometimes over 20 seconds. The largest ...
user avatar
  • 1
1 vote
1 answer
28 views

New Index creation can benefit from existing Index

I was wondering if a new index and consequentially its creation might benefit from an existing index. It is something just out of curiosity but extremely helpful in the case when you have to create a ...
user avatar
0 votes
0 answers
35 views

Group by before join executes faster than group by after join

I was analyzing the time taken for a query containing group by and join conditions. I first, created two sample tables t1 and t2. create table t1( col1 int, col2 int, col3 int ); create ...
user avatar
0 votes
0 answers
13 views

Query running for more time after Aurora postgres minor upgrade from 12.4 to 12.8

I am new to postgres and never dealt with any performance issues. We have a SQL which was performing better in Aurora postgres 12.4. The SQL was running for less than 1ms. Now after a minor upgrade to ...
user avatar
1 vote
1 answer
33 views

Can one dictate the order of generated values when adding a new BIGSERIAL column to a table?

I need to add a new BIGSERIAL column to a huge table (~3 billion records). This question is similar to what I need to do and the accepted answer has helped me somewhat. But I'm still wondering about ...
user avatar
0 votes
1 answer
30 views

Postgres hit ratio: should big history tables be removed from the production database?

A database has these cache hit ratios: table A: 0.006 table B: 0.955 table C: 0.023 Tables A and C are history tables. No relationship, large content and no need of fast queries, only few read ...
user avatar
  • 197
1 vote
1 answer
31 views

How Postgres bloat is dangerous?

A table is containing 300mB of bloat. It's a few less than 20% of the table records. The autovaccum is about to clean it in a few days, when it's maybe 350-400mB. Disk space is not a problem. What is ...
user avatar
  • 197
2 votes
3 answers
143 views

Efficient unique Inner Join

Context I have a query that is something like where I want to query all of the days that each product was on sale at at least 1 store. This query runs as part of a larger query and runs whenever a ...
user avatar
0 votes
1 answer
25 views

I have a GIST index and a regular index, one intended for filtering, one intended for sorting, but Postgres WILL NOT use both

I have a table with three columns: two can be used to filter the table, and the last can be used to sort it: create table thing( id serial primary key, location geometry(Point, 4326) not null, ...
user avatar
1 vote
0 answers
24 views

Query Planner has bad row estimate for second-degree joined link table and uses inefficient nested join

Note: This is a toy database example I came up with and a simplification of a real design, so please disregard from commenting about the way the database is structured. Each Group has precisely one ...
user avatar
  • 197
0 votes
1 answer
13 views

postgres join performance not improving with where condition

i have table A and table B (with below data) 1 million records which have 10 records with person_name 'John Doe' have index on person_name When i join as below , it takes 2seconds. select * from A ...
user avatar
  • 1
2 votes
1 answer
320 views

PostgreSQL 9.6 - ORDER BY timestamp DESC LIMIT 10 is extremely slow even with index

I am facing some curious cases in my postgres 9.6, mainly for queries that follow the format: SELECT a.* FROM a INNER JOIN b ON a.id = b.a_id WHERE a.field1 = '' AND b.field2 > .. ORDER BY a....
user avatar
  • 121
0 votes
0 answers
42 views

How to prevent sequences from getting out of sequence?

I have several sequences for tables (dynamic information and crosswalks) that get out of sync from the table primary keys during testing. While I can fix them, what can I do to prevent the sequences ...
user avatar
2 votes
1 answer
187 views

Why is my nested loop taking so much time?

I have the following request. In a nutshell, the wallets_history represents the transactions that happen in the wallet. And the goal is to get 1 point every in_interval from in_from to in_to. They ...
user avatar
0 votes
1 answer
25 views

Job queue performance - split into pending and done?

My application will use Postgresql as a job queue. There will be 100s-1000s of “pending” job rows at any time, and eventually grow to many millions of “completed” jobs spanning months. The DB will be ...
user avatar
  • 151
0 votes
1 answer
71 views

Parallel merge join with sorting on large temporary tables in PostgreSQL

I have two tables with text column which I want to join: larger has billion rows and smaller has 100M rows. Tables don't fit into memory, so PostgreSQL reasonably uses merge join for joining. The ...
user avatar
1 vote
1 answer
36 views

Filter table lookup by array value, but returning groupby unfiltered

I have multiple lookup tables and each need to be filtered by an array of values: select array_agg(joined_properties.name) as properties, items.name from items left join ( select properties.name, ...
user avatar
0 votes
0 answers
15 views

Postgres execution times increase exponentially with data volume

I have a table that increases by several million record per day. Initially it's partitioned by day, but a couple of days after creation each daily partition is merged into a monthly partition. The ...
user avatar
1 vote
1 answer
29 views

Simplify a jsonb request

I am a newbie in request involving jsonb. Can this one be improved? collections is a huge jsonb field and maybe one cross join is enough. SELECT actions FROM layouts CROSS JOIN ...
user avatar
  • 197
2 votes
1 answer
66 views

Why is count(x.*) slower than count(*)?

explain select count(x.*) from customer x; ... -> Partial Aggregate (cost=27005.45..27005.46 rows=1 width=8) -> Parallel Seq Scan on customer x (cost=0.00..26412.56 rows=237156 width=994) ...
user avatar
  • 123
0 votes
1 answer
229 views

Postgresql DB - insert or update on table violates foreign key constraint + Schema Design Question

I have 1 big table with 9 fields/columns, some of theses columns have a lot of duplicates, so i decided to split them into 2 tables in Postgresql i.e. TEST_A table with columns that have a lot of ...
user avatar
  • 1
0 votes
0 answers
46 views

PostgreSQL Database Performance issue

I have performance issue with PostgreSQL Database. These are excerpted related tables: CREATE TABLE public.patient ( patient_id serial4 NOT NULL, user_id int4 NULL, cid varchar(13) NULL, ...
user avatar
2 votes
2 answers
48 views

does a btree index on an array field actually do anything?

In PG you can create a table like CREATE TABLE foo ( id uuid PRIMARY KEY DEFAULT gen_random_uuid (), name text NOT NULL, things text[] NOT NULL DEFAULT ARRAY[] ::text[] ) and a related index ...
user avatar
  • 131
1 vote
0 answers
48 views

Potential causes for unusually long queries (postgres)

Recently we've noticed some SELECT queries taking an unusually long time to complete and are a bit stumped on what the reason could be. Looking at DB (and server) CPU and IO during the time the ...
user avatar
  • 11
1 vote
1 answer
57 views

How can I read a EXPLAIN ANALYZE in PostgreSQL

I have read a bunch of resources on the internet and the official documentation of PostgreSQL and I still don't know how to interpret the result of a EXPLAIN ANALYZE. I'm using PostgreSQL 12.8 and I ...
user avatar
  • 113
3 votes
2 answers
89 views

Duplicate Conditions in PostgreSQL Query

Can using duplicate conditions in PostgreSQL cause any problems? For example, I have a purchases table with a customer id, and my framework in some cases duplicates a lookup condition, like the ...
user avatar
  • 285
1 vote
1 answer
24 views

Postgres query to update a column with value of another

I have a query that is working: But, I'm not sure if it is the best. update table1 set column1 = column2, column2 = 0 where id = 'X'; As you can see, I set the value of column2 to column1, and in ...
user avatar
0 votes
0 answers
29 views

Performance Problems in Postgres on specific Selects that are only resolved on pg_dump

My Setup: I have a PostgreSQL 12 Server in a Docker container running in a docker swarm stack. It is use d by a FastAPI backend with SQLAlchemy. My Problem: Sometimes a query turns really, really ...
user avatar
  • 101
1 vote
1 answer
45 views

Postgres too Many References to Single Table is good or bad design

I have a Postgres database schema for a web app that needs real-time data and hence a huge volume of select, update, insert queries. Data manipulation is done via Postgres stored procedure mostly I ...
user avatar
1 vote
2 answers
55 views

PostgreSQL replication lag without obvious bottleneck

We have PostgreSQL 14.2 running on Ubuntu 18.04 with physical (asynchronous) replication set up. The standby is on an AWS EC2 instance with an EBS volume. When I test inserting data as fast as ...
user avatar
  • 165
3 votes
1 answer
86 views

Performance penalty for COUNT(1) OVER (PARTITION BY NULL)

In my application server, I would like to paginate a dataset using LIMIT and OFFSET, and additionally return the total count of the dataset to the user. Instead of making two remote calls to the ...
user avatar
0 votes
1 answer
36 views

Do I need to index a column that is used in every query, but rarely deviates from the default?

A very practical example: Assume an User table that has a "deleted" column that stores timestamp. Assume that there is a "deleted IS NOT NULL" filter on practically every query ...
user avatar
  • 197
1 vote
2 answers
30 views

PostgresSQL multiple indexes tuning

I have a table with those columns: id, uuid, customer_id, building, dept, values I have a combined index on those columns: building, dept. If I run this query: select count(*) from my_table where ...
user avatar
1 vote
1 answer
59 views

Optimizing expensive GROUP BY / ORDER BY query on 690,000 row table

I am writing a query to calculate hiscore rankings for a game. It performs a GROUP BY to aggregate hiscore statistics for individual player stats that are stored across 23 different rows (separated by ...
user avatar
2 votes
1 answer
23 views

How to gauge performance of switching to PostgreSQL table partitioning?

I have a table in a PostgreSQL database containing about 100m records that's mostly read-only with a few monthly writes to update new data. It has over 300 columns, and is heavily indexed for specific ...
user avatar
  • 1,028
0 votes
0 answers
24 views

How to improve a PG 14.1 query

I have a shop table that has sales associated with it. So I have the relation shop has many sales. A sale has an order date, and so I want the earliest order. Naively I issue a statement: SELECT "...
user avatar
0 votes
1 answer
101 views

Dynamic CREATE TABLE AS with indexes

I'm in the process of migrating our main db from SQL Server to PostgreSQL (while learning it in the process). One of the things I need to move are a bunch of stored procedures, that generate tables ...
user avatar
0 votes
1 answer
40 views

No speedup using parallel restore with pg_restore

I need a large database like 10TB-15TB in Postgresql for benchmarking. I've created a smaller ~100GB sample databases with pgbench like this: pgbench -i -s 7000 --no-vacuum exampledb However, I ...
user avatar
1 vote
0 answers
511 views

UPSERT multiple rows, do ON CONFLICT DO UPDATE SET conditional check/update row-wise

I'm trying to UPSERT multiple rows into a Postgres table (ie. INSERT ON CONFLICT DO UPDATE SET multiple rows). But I want the ON CONFLICT DO UPDATE SET conditional check/update do be done row-wise (ie....
user avatar
0 votes
1 answer
32 views

is Postgres jsonb scalabe for big documents and -> and --> operators

Today we have a table with a large jsonb (20mb). We are reading/writing the entire documents, but it's nearly fine. (60000 rows) Is it scalable (100mb-200mb, 400000 rows) if we evolve to select ...
user avatar
  • 197
0 votes
0 answers
35 views

Foreign Key update using JdbcTemplate is very slow in Postgres

I am using Spring JdbcTemplate to update the Foreign key relationship, Primary Key of Department table has to be referred to Employee Table. One department can have same but multiple employees working ...
user avatar
  • 101
0 votes
0 answers
43 views

Postgres13 logical replication degrading slowly

We're halfway through a postgres upgrade (from 11 to 13) and have run into a pretty unusual blocker, being that our 13 logical replica can't seem to keep up with changes from our 13 master. For ...
user avatar

1
2 3 4 5
17