Questions tagged [postgresql-performance]
Performance issues with PostgreSQL queries
1,076
questions
0
votes
0
answers
11
views
AWS RDS Postgres: How to see which query/process is using memory
Running a RDS Aurora Postgres cluster with two r6g.2xlarge instances that started fluctuating in memory usage around 6:50pm EST yesterday. Typically we are running with ~16gb headroom but it dips to ~...
- 101
1
vote
0
answers
15
views
Why does Postgres sometimes choose to use parallel workers, and other times not?
The same query generates two different plans, which I've visualized here:
https://explain.dalibo.com/plan/7076b8f12gd7h63d (uses parallel workers, 30s execution time)
https://explain.dalibo.com/plan/...
- 111
0
votes
0
answers
9
views
How to create sequence for two columns on pgsql
I have a table
CREATE TABLE public.mytable (
sequence integer NOT NULL,
caller_address TEXT,
contract_address text,
contract_sequence integer,
value integer,
CONSTRAINT ...
- 1
1
vote
2
answers
50
views
Simple query with two WHERE clauses uses inferior index scan
I have a table named "db_log" with a size of 11GB and approximately 10 million rows in PostgreSQL 13.7 version. The issue is that a simple query is not performing an full index scan for ...
- 21
0
votes
1
answer
40
views
postgreSQL update queries running longer
I have a query where processing status code for staging records are updated at the end of the processing, we have created daily partition on this table based on row_created_timestamp. On an average, ...
0
votes
0
answers
17
views
How to debug this tuple await events in RDS PostgreSQL
I have been noticing that sometimes we get a lot of open connections for a long time. Enabling enhanced metrics in RDS PostgreSQL I saw the following:
It seems this tuple wait event is making a lot ...
0
votes
0
answers
14
views
ERROR: invalid string in message
I'm getting this error every time I run airflow scheduler.
(Background on this error at: http://sqlalche.me/e/4xp6)
Traceback (most recent call last):
File "/home/ubuntu/anaconda3/envs/alphavu/...
- 101
2
votes
2
answers
218
views
Postgres query planning on a per-statement basis
When executing a statement in Postgres you can set specific query planner configuration parameters as described in the documentation.
These can be set on a system level by the administrator or on a ...
- 511
0
votes
1
answer
72
views
Why the second query is so much faster than the first one?
I have a query that look like this:
SELECT
DISTINCT "articles"."id",
"articles"."company_uuid",
"articles"."status",
"articles&...
- 591
0
votes
1
answer
37
views
Can sorting in postgresql EXPLAIN plan be prevented?
I have a large table join on the keys. The explain plan shows that most of the time is spent on sorting.
Is there anything I can do to prevent the large sort steps? Can I pre-sort the table?
Query and ...
- 269
0
votes
1
answer
33
views
PostgreSQL declarative partition using hash value of natural key
For creating partitioned table where we are expecting more than 800 million rows, I am trying to create partition based on hash value of natural key. Please find below more details on this:
CREATE ...
1
vote
0
answers
27
views
How to structure postgreSQL db for multiple different variations of a form
I am working on an app where users fill in various reports and submit them. They're currently stored in a table called "Reports". Each field in the report is stored in its own column. Some ...
- 11
1
vote
0
answers
69
views
AWS RDS Postgres configuration for database disk size >1TB and almost 1B records?
For our applications, we opted for Amazon Aurora Serverless v2 (with PostgreSQL 13.8) because of its ability to automatically scale up and down. The ACU configurations are 16 - 128 (the highest it can ...
- 11
1
vote
0
answers
19
views
postgresql Large Join is not using indexes
I have a large table join on the keys that does not use the index.
Query and plan here : https://explain.dalibo.com/plan/15a6a91492g8g935#plan/node/5
Both tables have an index on the key, like this:
...
- 269
1
vote
1
answer
51
views
Drop unique index of a unique constraint
I'm currently working on a postgresql 15 database.
I've created a data table named "measures" with following schema
CREATE TABLE measures (
timestamp TIMESTAMPTZ NOT NULL,
value double ...
- 31
-2
votes
1
answer
48
views
Problem :simple query takes too long to complete (over 7 minutes) [closed]
CREATE OR REPLACE FUNCTION public.fn_question_3_3()
RETURNS TABLE(name character varying, surname character varying, person_t person_type)
AS
$$
DECLARE
pt person_type;
BEGIN
RETURN QUERY
...
1
vote
1
answer
32
views
Postgres 14 WAL FILE cannot change min wal size(in WAL DIRECTORY) after changing propperties min_wal_size max_wal_size
I'm completely new in Postgres optimization. I was reading a lot about checkpoint and WAL files. I found a query to examine the ratio of checkpoints time to requested (exceeded WAL max size)
SELECT ...
- 11
2
votes
1
answer
63
views
SQL query not returning all results when too much data
I am a software engineer with a little knowledge about what's going on under the hood of a dbms.
The query presented below finds mutually followed people by two people:
SELECT followed.id
FROM person ...
- 23
0
votes
1
answer
36
views
Indexing on ANY queries
I have a table that I am trying to query. Here is the create statement for the table
CREATE TABLE IF NOT EXISTS public.TableName
(
i integer NOT NULL,
t integer NOT NULL,
v ...
- 101
1
vote
0
answers
33
views
Why does an aggregate query perform a sort?
I have three tables that will be joined with sum/count data in the third table. Here's the query:
SELECT a.company_id,
c.asal_id,
c.tujuan_id,
c.barang_id,
sum(c.berat) AS berat,
...
1
vote
0
answers
37
views
Postgresql - work_mem not working?
my query plan is:
QUERY PLAN |
-------------------------------...
-1
votes
2
answers
35
views
Slow Delete Operation in PostgreSQL, no trigger, no foreign key
i had a table that contains 51Mils rows, 19GB disk (according to statistics).It does not have any trigger or foreign key. Let's called it table A with index (col1,col2).
My problem is , i had to ...
- 1
0
votes
0
answers
23
views
How do you eliminate a second join on the same table if the condition is similiar?
I have a PostgreSQL database with a table consisting of UserIDs (id_u) and parent UserIDs (id_p). All users have some attributes stored in other tables which are accessible through a view (v_attr). ...
- 1
4
votes
1
answer
50
views
How can I avoid this poor Postgres query plan for a sparse aggregate query?
I have the following database schema:
CREATE TABLE foo(
id SERIAL PRIMARY KEY,
tag INTEGER NOT NULL,
val INTEGER NOT NULL
);
CREATE INDEX foo_tag ON foo(tag);
CREATE INDEX foo_val ON foo(val);
...
- 140
0
votes
1
answer
22
views
Multikey index in Postgres
I'm struggling to optimize on correct index creation for my data structure. I'm trying to index massive amount of data (~100B records) of json data type:
{
"email": "some@email.com&...
- 103
0
votes
1
answer
28
views
Creating a partial index on non-default values and searching all rows in that index in PSQL
If I have a table where 99% of rows with column x has a default value and want to search for a specific value in that column, will creating a partial index on x provide any improvements?
For example,
...
0
votes
1
answer
53
views
Can these LATERAL subqueries be optimized and can it still pick data if other tables are empty?
I have got the schema below and some queries as well:
Schema (PostgreSQL v15)
CREATE TABLE IF NOT EXISTS users(
id SERIAL PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
password TEXT NOT NULL,
...
- 65
1
vote
0
answers
36
views
Seeking clarification and recommendations on unexpected patterns in Postgresql index usage statistics
I am trying to prune unused and rarely used indexes based on the following stats:
pg_stat_user_indexes.idx_scan: Number of index scans initiated on this index
pg_stat_user_indexes.idx_tup_read: ...
- 111
0
votes
0
answers
18
views
Postgresql 15- Pgadmin 6.16 shows red warning in logical replication , is this normal?
I have a logical replication master slave in postgresql 15. Actually there is no workload over the server just replication.
However, while monitoring my dashboard(pgadmin) appeared an strange red ...
-1
votes
2
answers
120
views
Postgresql Aggregate Query With Sort Very Slow
I have a simple aggregate query that is running very slowly (198s) on a table with ~4.6mil rows, can anyone suggest how to optimise?
SELECT u.user_id,u.firstname,u.surname,u.image_url,p.pool_name, ...
- 289
0
votes
1
answer
47
views
Large table with too many indexes stalls my app
PostgreSQL version: 13.10
My app has a notification feature, where users have a list of events that happened in relation to them (much like the Facebook notifications). Each notification is a row in ...
- 101
1
vote
0
answers
18
views
What circumstances will cause a postgres 'unlogged' table to lose data?
For performance reasons I have created a Postgres UNLOGGED table. Everything works fine, performance is blisteringly fast. But very occasionally I lose all the data in it. According to the ...
- 1,027
1
vote
1
answer
66
views
Query with smaller subset to scan taking exponentially longer?
I have a large table, extrinsics, almost 90GB in size, containing data from multiple blockchains.
I have a query which takes almost 17 minutes to run:
select * from public.extrinsics
where chain_id = ...
- 125
0
votes
1
answer
31
views
Avoid nested loops
I've the following query:
Select GAD.OWNREF, GAD.ADCODE, GAD.CORNAM, GAD.NOMCUR1, GAD.OPNCUR1,
GAD.OPNAMT1, GAD.EXPDAT, GAD.CONFLG, GAD.GACTYP, GAD.OWNNAM, GAD.GACRUL,
GAD.CORREF, GAD.GACFRM, ...
0
votes
0
answers
57
views
Create index on big table increase IO:XactSync wait event for other sessions
AWS Aurora (postgres 11.9) after downgrading instance from db.r6g.8xlarge to db.r6g.4xlarge instance and enabling logical_replication
I'm trying to rebuild indexes (cause of bloat) for table size 30GB ...
1
vote
3
answers
44
views
Seq scan on OR condition
I'm using PostgreSQL 11. I have a table with ~500к records and its size is around 2GB. If I'm running this query:
SELECT DISTINCT point.id
FROM "buildings_point" as point
LEFT OUTER JOIN ...
- 113
2
votes
2
answers
105
views
Index for searching in column where the column starts with the provided value
I have a table, where one column contains a substring of an url. It's always the leftmost part of the url without scheme.
id
domain
column1
column2
1
example.com
value1
value2
2
example.org
value3
...
- 123
3
votes
1
answer
102
views
Return recursive self joins in PostgreSQL with aggregated data
I have this simple table schema:
SQL Fiddle
PostgreSQL 9.6 Schema Setup:
CREATE TABLE IF NOT EXISTS users(
id INT NOT NULL PRIMARY KEY, email TEXT NOT NULL UNIQUE,
password TEXT NOT NULL, ...
- 65
4
votes
1
answer
243
views
Query performance on static, large PostgreSQL table
I tried to have this as detailed as possible. Sorry about the length!
Background
I created the following partitioned table protein_snp_assoc on a PostgreSQL (version 12.13) database:
CREATE TABLE ...
- 43
2
votes
3
answers
283
views
EXPLAIN ANALYZE does not show what takes time with my GIN index scan
Context
I have a table named companies_establishments that holds ~33M rows.
I created a GIN index with trigrams, so I can make LIKE queries much faster.
CREATE INDEX companies_establishments_id_index ...
- 123
0
votes
0
answers
23
views
PostgreSQL and Tools for Analyzing Performance Cost For Frequent Large Inserts
I am designing an Ingest process that inserts many rows into several tables. We are running PostgreSQL on Azure's PostgreSQL Flexible server. It currently appears that write/insertion speed to this ...
0
votes
1
answer
39
views
Should I create another table for expensive query?
I'm creating stock market related app and I've made this atrocity below trying to get current stock indicators. I'm storing some data in jsonb (because normalizing at this point would be rather hard ...
- 3
1
vote
1
answer
127
views
PostgreSQL use upsert with ON CONFLICT, or with separate INSERT and UPDATE statements?
We have a PostgreSQL 14 database where we store products fetched from online platforms. We have scrapers that are run on schedule which fetch products, and this data is then either inserted if the ...
- 13
2
votes
0
answers
158
views
Postgres: One-to-many relationship with WHERE, ORDER BY and LIMIT not scalable?
I'm currently using Postgres 12.
A few years ago, I set up the following model: a lot table with a one-to-many relationship to a line table. My idea was to mutualize data that are common for multiple ...
- 21
0
votes
1
answer
38
views
Reasons for why queue notification percentage greater than one
Was just wondering, for reasons or resources, why PG queue notification percentage, as shown by pg_notification_queue_usage(), would be greater than one. Was wondering if there are other reasons ...
1
vote
1
answer
45
views
Postgresql query takes unnecessary (?) long time
I know the web is full of questions, complaints, etc. like my question. But each of them still does not answer the questions of people finding those posts via Google, ...
I try to post my question ...
- 111
1
vote
2
answers
254
views
How to use LIMIT and OFFSET in Postgres' json_agg() function
With reference to this question and this SQL Fiddle, I currently have a working SQL query, based off of this answer, that gets a tag from the database and aggregates all articles having such a tag. ...
- 65
6
votes
1
answer
531
views
How to reduce query size with many repeated UNION subqueries?
I use Postgres 13 and have a table defined with the following DDL:
CREATE TABLE item_codes (
code bytea NOT NULL,
item_id bytea NOT NULL,
time ...
- 185
0
votes
1
answer
27
views
What is a good test command to cause Postgres to use/fill shared_buffers?
I am doing testing on a replica, trying to diagnose why servers take several minutes to shutdown when fast mode is used. It seems to correlate with high shmem usage, can anyone recommend a good query ...
- 103
2
votes
0
answers
63
views
Does autovacuum to prevent wraparound on a TOAST table lock its associated user table?
My understanding of the difference between autovacuum versus autovacuum to prevent wraparound is that the former can be interrupted by incoming transactions, whereas the latter will temporarily block ...
- 21