Questions tagged [postgresql-performance]

Performance issues with PostgreSQL queries

Filter by
Sorted by
Tagged with
3 votes
1 answer
19 views

Optimizing retrieving up to X rows in secondary table per parent row in a primary table

I'm looking to find the best general approach to retrieving up to a given number of records in a secondary table for each record previously retrieved in a primary table. Technical Situation For the ...
Corentin Demachy's user avatar
0 votes
0 answers
11 views

strange behaviour of the postgresql planner on a partitioned table

I have a problem where I don't understand the behaviour of the Postgresql 14.9 database planner on a partitioning table. I'm sorry if my explanation is long, but I wanted to give as many details as ...
Franck's user avatar
  • 23
0 votes
1 answer
25 views

Does long running query cause replication delay in PostgreSQL which is in 'idle in transaction' state?

In my system I found same query called more than 30 times 'idle in transaction' state. Is there any way to to identify the reason of 'idle in transaction'? Should I terminate those query manually ...
Sheikh Wasiu Al Hasib's user avatar
-1 votes
0 answers
20 views

postgreeSQL performance estimation w.r.t reads and writes per second

Here are all the parameters involved:- instance (aws rds db.t3.micro):- vcpu:2,memory:1GiB, 1 core Image :-PostgreeSQL 15 Tables:- TableA:-(aid (PK),a1(FK to B.bid),a2,..a7) TableB:-(bid (PK),b1(...
varun_k_21's user avatar
0 votes
1 answer
48 views

Postgres doesn't use functional index for LIKE queries [closed]

I have a query with a LIKE operator: SELECT "task_v1_taskv1"."id", "serviced_object_v1_servicedobjectv1"."id" FROM "task_v1_taskv1" ...
angryseven's user avatar
1 vote
0 answers
29 views

Inaccurate estimates for geospatial types

This is my first dabble with Postgres and geospatial. Looking at a slow query which includes ST_Intersects(a.geography, b.geography). The optimiser estimates are 2500x out. Before I turn to ...
Mark Storey-Smith's user avatar
0 votes
0 answers
24 views

Performance issue - cant fix with pg_plan_hints

I have a query that is stored in a function. It is called via a prepared statement jdbc app. I have a fix for the query to force an index - but I cant get the "normalized form" required for ...
captaintwocan's user avatar
0 votes
0 answers
47 views

LWLock: BufferMapping - postgres [closed]

good afternoon! I have my database in Azure, it is Postgres in version 14.9, flexible. his ability: General Purpose, D16ds_v4, 16 vCores, 64 GiB RAM, 8192 GiB storage iops: p60 (16000iops) I take care ...
Leandro Macedo's user avatar
0 votes
0 answers
36 views

Postgres usage always using 100 cpu usage

In certain interval (like 60 minutes), postgres always uses 100%cpu. This problem is just starting last Thursday. I had to manually to kill the pid through HTOP. The one that makes me confused is ...
Faris Ponighzwa's user avatar
0 votes
0 answers
31 views

Postgres Bad Planner Estimates on Join

I have four tables in a model hierarchy: customer, subscription, billing_cycle, and product_version. Every object in that hierarchy has a foreign key to its parent object. There are a number of ...
Ben H's user avatar
  • 1
0 votes
0 answers
19 views

Different execution plan for identical query

Below queries result in completely different execution plans in PostgreSQL 14. For Oracle both queries are identical. SELECT * FROM sa JOIN sched ON sa.DATE_STR = sched.DATE_STR AND sa....
Gerhard H.'s user avatar
0 votes
1 answer
43 views

Selecting columns based on time ranges

I have a table which looks like this: Table t1 timestamp (pkey) | A | B | C ---------------------------------- 2024-01-01 12:00:00 | 1 | 2 | 2 2024-01-01 12:00:01 | 1 | 2 | 2 2024-01-01 12:...
Miro's user avatar
  • 113
1 vote
1 answer
53 views

Efficient Pagination In Postgresql (Keyset Pagination)

I'm working on implementing pagination for a PostgreSQL database in which I have a table that stores users for my application. I have a query that is intended to fetch the next page of users based on ...
odyssey's user avatar
  • 13
1 vote
1 answer
55 views

Postgres BRIN index for time-series table

I have multiple time-series tables with Pkey on timestamp column. I'd like to test performance improvement if I change the index to be BRIN instead of BTREE. Yet, I'd also like to have a UNIQUE ...
Miro's user avatar
  • 113
2 votes
2 answers
259 views

Optimize query performance for a filter on col_a > col_b

I have a large table in a PostgreSQL database (~100 columns, millions of rows). My query has no joins but it does have several where clauses. All of the where clauses are covered by appropriate ...
olleicua's user avatar
  • 121
0 votes
0 answers
19 views

ORDER BY index not being used due where clause POSTGRES

I have a SQL query generated by JPA: select * from public.card inner join public.affinity_group affinitygr1_ on affinity_group=affinitygr1_.id inner join public.product product2_ on affinitygr1_....
Alex Roitman's user avatar
0 votes
1 answer
48 views

trivial query very slow on primary key on a PostgreSQL DB (instantaneous on MySQL)

I have a table data: with ~ 400 million rows with data.id as int4, not null, and set as primary key it's an aws RDS server, with ~128G of RAM there are no row with id > 1e9 This query: select ...
Karl Forner's user avatar
0 votes
1 answer
51 views

Missing collation cs_CZ.utf8

I've set up a database on PostgreSQL 14, and now when I try to insert data, I'm getting the following error: ERROR: collation "cs_CZ.utf8" for encoding "UTF8" does not exist ...
Jan Kůst's user avatar
-1 votes
1 answer
54 views

How to make this postgresql query to use composite index only ? Any Suggestion

Query: with firstData as (select rm.display_name as register, br.createdat, br.status, br.id as runid from bad_run br inner join bad_master bm on bm.id = br.badmasterid inner join regis_master rm on ...
ankush chauhan's user avatar
1 vote
1 answer
85 views

How to improve performance for this query ? Any Suggestions

Query: with firstData as ( select r.display_name as counter, r.createdat, r.status, r.id as runid from run r inner join customer c on c.id = r.badmasterid ...
ankush chauhan's user avatar
0 votes
1 answer
55 views

Calculating memory size required for an index

Assume I have the following table: CREATE TABLE t1 ( id int NOT NULL PRIMARY KEY, rank int NOT NULL, phrase varchar(100) NOT NULL ); with 10 billion records in a relational database like ...
Alexey Starinsky's user avatar
0 votes
2 answers
39 views

PostgreSQL plan cache and variable number of arguments

Query: select * from table where col in ( values (?), (?), (?) ) The query text is generated, and number of (?) is variable and up to 10000. It works just fine, just wondering, whether Postgres ...
Mikhail Boyarsky's user avatar
3 votes
1 answer
842 views

Why Isn't Postgres Using My Functional Index?

I have a public copy of the Stack Overflow database here on AWS Aurora Postgres 15.5: Server: query.smartpostgres.com Username: readonly Password: 511e0479-4d35-49ab-98b1-c3a9d69796f4 The users ...
Brent Ozar's user avatar
0 votes
1 answer
54 views

Select query with 90+ parameters intermittently 20x slower on RDS PostgreSQL instance when using connection pool

I'm running a select statement on a postgresql table (on an RDS instance) in order to sum up values across 2 mil rows. The select statement has about 100 parameters (mostly different countries we want ...
Arnfred's user avatar
  • 101
0 votes
1 answer
164 views

Why is restored database faster than original?

I ran this explain analyse in my PostgreSQL 13.7 database: explain(analyze, verbose, buffers, settings) select * from main_table; Seq Scan on public.main_table (cost=0.00..10246.15 rows=220715 width=...
Mapashito's user avatar
1 vote
1 answer
203 views

What are large files in Postgresql base directory

There are too many filenames start with T (T14_xxxxxxxx.1, T14_xxxxxxxx.2 .. T14_xxxxxxxx.18) in postgresql base directory for one database. The size of this database in base directory is 500GB with ...
Mustafa DOGRU's user avatar
0 votes
1 answer
46 views

Strategy for Postgres updates with very high write load, but low consistency requirements

I need to collect statistics on profile thumbnail views for a social media app, backed by Postgres. Every time the thumbnail of a user appears anywhere on a page (in a list of profiles, or next to ...
Luke Hutchison's user avatar
1 vote
1 answer
86 views

Terrible performance joining a view to a fast subquery / CTE

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 ...
Brian Moeskau's user avatar
0 votes
2 answers
83 views

Cheaper query plan takes much longer to execute

I am running a query like explain (analyze, buffers) select col1, col2, count(col3) as c from table1 group by 2, 1 order by 2, 1 When the work_mem is set at 4 MB, the plan looks like this: "...
ahron's user avatar
  • 803
0 votes
1 answer
99 views

Why doesn't Postgres apply limit on groups when retrieving N results per group?

I've come across many questions/answers for Greatest/Top N per group type problems that explain how to solve the problem - generally some variation of row_number() or CROSS JOIN LATERAL, but I'm ...
Seanvm's user avatar
  • 101
0 votes
1 answer
51 views

Postgres is not using CPU cores unsless running from psql or using "explain analyze"

Im running postgres 16.1 with a single user and no other activity on the server. I run this Test for many times and the results are very consistent. The Problem The query below will run with very ...
0xbadc0de's user avatar
  • 103
1 vote
0 answers
29 views

Does aggregation order affect performance

I read on a blog that changing the order of aggregation (using group by 1,2 instead of group by 2,1 can affect the performance of the query. But the costs of both the query plans are identical. Yet, ...
ahron's user avatar
  • 803
0 votes
0 answers
85 views

PostgreSQL 14 is more than 2x slower than PostgreSQL 11

I have a Django web application that is backed by PostgreSQL 11 and I want to upgrade to PostgreSQL 14. But, when running PostgreSQL 14, the test suite runs more than 2x slower. I have compared both ...
Travis's user avatar
  • 113
1 vote
0 answers
59 views

Impact of high TPS on database

I have an application which is very actively read/write database, especially during some specific load bursts moments. Some metrics: CPU user time goes doubles up from 25% to 50% high write rate ~...
frankie's user avatar
  • 121
0 votes
1 answer
64 views

Why is DISCARD ALL called many times in Azure PostgreSQL?

I am debugging a high CPU load issue with Azure PostgreSQL flexible server. In the time window where CPU usage spikes I am seeing a lot of utility queries. They don't take long to process but there ...
DarkLeafyGreen's user avatar
0 votes
1 answer
31 views

Evaluation order (commutativity/associativity) of ANDed terms in Postgres WHERE clause

Is Postgres' query planner able to rearrange terms in a WHERE clause according to the rules of commutativity and associativity? For example, do the following WHERE clauses all result in a query plan ...
Luke Hutchison's user avatar
1 vote
4 answers
118 views

Scalability of Postgres for table with large number of indexed columns

I have a Postgres table with a large number of indexed columns (roughly 100 indexed columns total, and yes, I need them all, and yes, they all need to be separately indexed). Any row update causes all ...
Luke Hutchison's user avatar
0 votes
0 answers
48 views

Designing Tables for Regular Updates in PostgreSQL

I have a PostgreSQL table that contains information about transactions, currently totaling 50 million rows. The table has 16 columns, with 4 of them being indexed. Transactions are frequently inserted,...
Daulet's user avatar
  • 1
1 vote
1 answer
31 views

Does postgres optimizes repeated expression in select query?

I'm using jooq to generate SQL query and I'm a bit lazy to generate nested select from another_select from bottom_select. My generated query contains repeated expressions. Does postgres 13+ applies ...
Capacytron's user avatar
0 votes
1 answer
37 views

Vastly different query performance on GIN indexed table using different search words

Problem Text searches in a Postgres table with a simple GIN index ordered by creation timestamp have wildly different latencies based on the keyword that is being searched. While searches for frequent ...
Stefan FPF's user avatar
6 votes
3 answers
2k views

Is there a performance penalty for unique key constraint violations in Postgres?

In my API, the user can potentially send a request which tries to create a new row when a row with that unique key exists. Currently, I'm catching the unique key error and returning a message to say ...
James Hay's user avatar
  • 285
0 votes
1 answer
37 views

Postgresql slow count with like

I've got a simple flat table with approx. 18 million rows. Now I need to count a field with the ILIKE operator. This query returns approx half a million rows. When the search item is "narrower&...
Sebastian's user avatar
  • 113
0 votes
1 answer
87 views

Postgres filter timestamptz column by date very slow (possibly index not used?)

I have the following table with 462541359 rows. create table "Prices" ( "Id" bigint generated by default as identity constraint "PK_Prices" ...
derteufelqwe's user avatar
0 votes
0 answers
295 views

PostgreSQL query in WAIT event IO - DataFileRead for more than 24 hours

I am facing an issue with some queries which use to take few minutes are now stuck in Wait Event - IO - DataFileRead for more than 24 hours and still did not complete. SELECT rpsa....
Siddharth's user avatar
0 votes
2 answers
100 views

Sensible value for autovacuum_cost_delay

I'm running a data warehouse on an AWS RDS PostgreSQL instance. Most of the heavy lifting is done during the night-batch, and very often we use a TRUNCATE-rebuild strategy, also for large tables (100M ...
Bart Jonk's user avatar
  • 305
0 votes
0 answers
31 views

Why does this index make one query faster but another one slower

I am optimizing my database I found an index that makes one query go from 400ms to 2ms but it also makes another query go from 1000ms to 4000ms. It seems that the planner is choosing a suboptimal path ...
Erik's user avatar
  • 1
0 votes
0 answers
148 views

PostgreSQL: Parallel Workers Error

For the last two days one of our query causing parallel workers related errors as below, 2024-01-04 13:58:45.988 CST,"user_a","testdb",108428,"10.0.0.1:46480",6596490c....
mediocre's user avatar
0 votes
0 answers
96 views

An alternative to PostgreSQL union?

In the following example variables in single quotes '' are arguments passed to the PostgreSQL query. If 'tag_args' is an empty array, the first row of result table is a row of three nulls and I use ...
jvkloc's user avatar
  • 133
4 votes
0 answers
78 views

Row estimates incorrect when using RLS and current_setting in policy

Context Postgres 14.4. I have a table which has RLS enabled, and I am using values pulled from current_setting in order to determine which rows should be returned when querying the table: create table ...
cjheppell's user avatar
  • 303
1 vote
1 answer
98 views

Understanding PostgreSQL bitmap heap scan poor performance

I'm trying to optimize a slow query on PostgreSQL 15. The query is quite complex with many JOINs, a UNION and subqueries but 99% of execution time is in the innermost node that seq scan a 700K records ...
St.Mauro's user avatar

1
2 3 4 5
24