Questions tagged [postgresql-performance]

Performance issues with PostgreSQL queries

Filter by
Sorted by
Tagged with
0 votes
1 answer
30 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
19 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
42 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
73 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
38 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
27 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
803 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
  • 42.9k
0 votes
1 answer
46 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
-1 votes
0 answers
30 views

Increase Report Performance using Materialized Views

In our Reconciliation Project, which performs operations on 4 million new transactions daily and stores them in a PostgreSQL database, we created reporting code in Python using materialized views. ...
Purushottam Nawale's user avatar
0 votes
1 answer
154 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
168 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
0 answers
44 views

Performce Problems on Parallel Seq Scan [closed]

I ran a SQL-Statement with the explain prefix on two databases. Testsystem: -> Nested Loop (cost=0.42..4841.28 rows=68 width=271) -> Parallel Seq Scan on table1_aud ...
ANPAS's user avatar
  • 1
0 votes
1 answer
35 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
74 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
67 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
  • 793
0 votes
1 answer
86 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
43 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
  • 793
0 votes
0 answers
58 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
0 votes
0 answers
37 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
  • 111
0 votes
1 answer
36 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
29 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
0 votes
4 answers
91 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
46 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
0 votes
1 answer
26 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
0 answers
28 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
34 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
59 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
153 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
82 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
91 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....
Basil Titus's user avatar
0 votes
0 answers
70 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
67 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
84 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
0 votes
2 answers
83 views

PostgreSQL - 30000 row updates, setting non-indexed boolean column to 'true' takes hours

I am running PostgreSQL 13, and single UPDATE statement that seems like it would be trivial to complete can take hours (at least in one case more than 24 hours). Selecting the rows with a SELECT ...
Justin Lowen's user avatar
1 vote
0 answers
74 views

like_regex vs jsonb_build_object performance

I'm working with Postges 14.8 and using JSONB to store data. The queries that I'm working with perform filtering and JOIN'ing based on stuff in this JSONB column named data. We need to perform JOIN's ...
MRzeczkowski's user avatar
2 votes
2 answers
145 views

Improve query performance of filtered left outer join of big tables

I am trying to optimize a query that joins two large tables (40MM+ rows) in PostgreSQL 15.4. SELECT files.id, ARRAY_AGG(b.status) FROM files LEFT OUTER JOIN processing_tasks b ON (files.id = b....
Jeff G's user avatar
  • 123
0 votes
1 answer
46 views

Efficient PostgreSQL query syntax for duplicating the result

Assuming I have the following PostgreSQL query: SELECT ( SELECT SUM (t.customers) FILTER ( WHERE id = 1 AND date BETWEEN %(start)s AND %(stop)s ...
jvkloc's user avatar
  • 133
0 votes
0 answers
26 views

Insert data in postgres partition

I have table: CREATE TABLE full_data ( id int4 NOT NULL, name varchar NOT NULL, deleted bool NOT NULL ) PARTITION BY LIST (deleted); and its two partitions: active (where deleted = false) ...
user122222's user avatar
1 vote
2 answers
59 views

Postgresql: Performance - SELECT with function in WHERE condition

On Postgrsql 13.5 - I have a SELECT statement with a stored procedure as condition on the PK column (id). This turns out to be very slow: select * from xyz where id=myfunction('static-parameter') -- ...
bboness's user avatar
  • 21
2 votes
1 answer
36 views

Ensuring ascending order of committed event_id in PostgreSQL audit trigger system

I'm using the logged_action audit system in PostgreSQL, and I'm encountering a problem where the event_ids are not guaranteed to be committed to the database in ascending order. This is causing my ...
Gergely's user avatar
  • 21
1 vote
0 answers
30 views

Advice on postgreSQL table logging

I am thinking I can turn off logging for certain tables in my PostgreSQL analytic data warehouse. There are a core set of very large tables (between 50 and 500Gb) that are completely rebuilt at the ...
zambo's user avatar
  • 21
0 votes
2 answers
73 views

Index optimization for generic UI sorting and filtering [duplicate]

We have a table that may contain 1M-5M or more rows. It has an assortment of string, numeric and date columns. The UI presents a table view, with "infinite scroll" paging, and various ...
e.dan's user avatar
  • 101
1 vote
0 answers
53 views

Continious aggregates (keeping aggregated data in a fresh state) for PostgreSQL

I build a PostgreSQL database for movie db platform. This DB is heavy loaded for readings of many related data. Here is simplified schema what I have: create table items ( id ...
Dmitry K.'s user avatar
  • 143
0 votes
0 answers
14 views

Postgres list partitioning

We have a postgres table which is close to 300GB and its growing more. We have a column "name" which is unique to each and every record. We decided to go with list partition based on the ...
user282382's user avatar
-1 votes
1 answer
35 views

Full text search in poatgres using tsvector and tsquery [closed]

I have a query like below Select * from abc where (to_tsvector('english', firstname) @@ to_tsquery('english', 'a:*')); is not working But same query aa is working Select * from abc where (...
Amar's user avatar
  • 7
0 votes
1 answer
48 views

Outliers produce a bad query plan

I have table document with ~2.5 millions rows and more than 100 columns. I provide only used in query columns: CREATE TABLE document ( id serial PRIMARY KEY, organizationid INTEGER, status_1 ...
Bunny Boss's user avatar
0 votes
1 answer
40 views

Postgresql - optimizing multicolumn search with "prefix" search pattern as one of the filters

I'm now working on the project that automates "contracts" creation and signing. Recently I received a requirements to implement search functionality for "contracts", so I struggle ...
EPe's user avatar
  • 1

1
2 3 4 5
24