Questions tagged [postgresql-performance]

Performance issues with PostgreSQL queries

Filter by
Sorted by
Tagged with
0
votes
1answer
27 views

PostgreSQL: Performance: ORDER BY an indexed column is slowing down the query performance

SELECT s.id, s.name, s.updated_at, COUNT(*) OVER () FROM screens s INNER JOIN theatres t ON s.theatre_id = t.id LEFT JOIN company_screen_mappings AS csm ON csm.screen_id = s.id AND csm.is_deleted = ...
0
votes
1answer
39 views

Aggregate Multiple Instances of Each Row Without Multiple Seq Scans

I am trying to perform some mathematical operations in PostgreSQL that involve calculating multiple values from each row, then aggregating, without requiring multiple Seq Scans over the whole table. ...
0
votes
0answers
57 views

Performance issue in Postgresql 12

It is a Java and Hibernate based application that processes rows in a table. The application when launched loops through rows in a table, executes anywhere from 10-25 queries per row and inserts ...
0
votes
0answers
157 views

Bottleneck when inserting many rows into different tables

TLDR: I want to bulk insert into many initially empty tables at once, without the performance of each insert being affected too badly. I seem to be hitting a performance bottleneck I can't yet explain....
0
votes
1answer
99 views

Reindex or Analyze Tables on PostgreSQL after import

I used PostgreSQL 9.5 on a server and I ended up migrating the data to a new server using PosgreSQL 12. Should I perform a REINDEX for all tables or just ANALYZE for all tables?
0
votes
2answers
51 views

pg_stat_monitor: see placeholders instead of actual query

I have started using pg_stat_monitor extension from : https://www.percona.com/doc/percona-monitoring-and-management/2.x/manage/conf-postgres.html with my postgresql database.I have a problem here. ...
0
votes
1answer
46 views

Does Postgres read blocks sequentially, even if a disk is high-latency?

I'm using Postgres on AWS EBS, when running a query that uses a bitmap heap scan and a bitmap index scan, the execution time seems proportional to the number of "heap blocks" read, as shown ...
1
vote
1answer
399 views

Issues with enabling pg_stat_statements

I am having issues quering pg_stat_statements and puzzled what else I missed? See below what I have done. I still have issues when I query pg_stat_statements! I got error saying the libraries is not ...
5
votes
2answers
1k views

When should we use table partitioning in Postgresql?

Context: we have two fairly large tables in our database, one holding 80 millions records and the other 160 millions. We're seeing performances issues and are thinking about using table partitioning ...
1
vote
0answers
227 views

When partitioning a huge table in postgres , how much is too much?

I have a huge table in Postgres with 10 million inserts per day, Since it's not feasible to keep all these data in a single table, we are splitting it based on the week. we are currently using ...
1
vote
2answers
649 views

Postgres lag() function has huge impact on query

As reported to my previous question (that for completeness is reported here), I've solved my problem using the window function LAG to get the time between the previous and next row, sum them and then ...
0
votes
0answers
68 views

Postgresql UPSERT contention on simple table

Postgresql 12.3: I am finding CPU spikes and wait times from UPSERT contention for what I would hope is a very simple table/use case. The table definition is like so: CREATE TABLE public....
2
votes
2answers
179 views

Simple query performance on 300M row table is very slow even with beefy hardware

Hardware/PostgreSQL version: AWS RDS db.r4.xlarge (4vCPU, 30.5GB RAM, Provisioned IOPS (SSD) storage, 2500 IOPS) PostgreSQL version 11. Background: I have an ever-growing table that I feel isn't ...
0
votes
1answer
68 views

PostgreSQL update column in one table with value from another, based on key from first table

Sorry about the title, I wasn't sure how best to word this. My situation is this: I have two tables: paper_author [paper_id, author_id, author_name] authors [author_id, author_name] Right now, the ...
0
votes
1answer
392 views

How many parallel workers can I have on Postgres?

I working on a server with Ubuntu, 320 GB SSD, 6 cores and 16GB Ram, but Postgres is having issues some queries that take very long time to run, and with parallel queries, that are increasing the ...
0
votes
0answers
24 views

JSONB Performance using a LIKE

I'm using postgresql 11 and I have a JSONB column which looks like { "obj": {"nested_obj": {"firstname": "john", "surname":"smith"} } } ...
4
votes
2answers
879 views

Speeding up index scan backwards query

My app is making the following psql query, and it is running extremely slow: SELECT COUNT(*) FROM ( SELECT 1 AS one FROM "large_table" WHERE "large_table"."user_id&...
1
vote
0answers
49 views

How to store values of different entity types in database?

I created the schema of ref_entity and ref_entity_type: create table ref_entity ( id serial primary key, name varchar(255) NOT NULL, type_id integer, code varchar(255) constraint FK_REF_ENTITY_TYPE ...
0
votes
1answer
2k views

PostgreSQL LWLock: lock_manager issue

In our application we need to insert a lot of historical data that will be consumed later by some mobile devices. These data is organized by a numeric incremental attribute that the device will use to ...
1
vote
2answers
322 views

In Postgres, what should be the autovacuum strategy for big tables (hundreds of millions rows) that get added/removed thousands of rows every day?

I have quite a big table with a lot of daily traffic for reads, inserts and deletions. Currently, it has 392 million of live tuples and 27 million of dead ones. Vacuum settings (...
2
votes
1answer
171 views

Does Postgresql create a new tuple for an no-change update statement?

I need to fix some data issues in a Postgresql database, the primary of which is trimming whitespace from text columns. I'm doing this using a statement like: UPDATE app.products SET "description&...
1
vote
0answers
19 views

Analyzing performance of an insert int postgres

I have a statement which is inserting a bunch of rows (doesn't really matter what or where for this question) into a Postgres DB and it isn't as fast as I'd like. I can run an explain query to see ...
1
vote
0answers
38 views

Update and insert performance with partial indexes

I have different queries for fetching data from a large table (about 100-200M rows). I've created partial indexes for my table with different predicates to fit the query because I know each query. For ...
1
vote
1answer
287 views

Postgres Query not using Index but faster with Index

Sorry if this seems to be a duplicate question. I'm using Postgres 11.6 on AWS RDS. I have 2 tables: CREATE TABLE public.e ( id character varying(32) COLLATE pg_catalog."default" NOT ...
0
votes
1answer
437 views

How can I determine what's a better configuration for my app? [closed]

I'm currently using Postgres 12 on AWS RDS and am using their default configuration for the database. I use a gem called PgHero that advises me to use PGTune to improve the efficiency of the database. ...
1
vote
1answer
43 views

PostgreSQL - trying to speed up query responses from view that has join and group by

I have these two tables: precipitaion: store precipitation data at daily (freq = 'daily') and hourly (freq = 'hourly') frequencies for 7 months so far (I receive regular updates). Column | ...
0
votes
3answers
232 views

Why is this PostgreSQL volatile function faster than an inline subquery?

The following examples have been tested in PostgreSQL versions 11 and 12. A few of the tables involved have around 2M rows, others not more than 250k. I have very big and nested queries like this (not ...
1
vote
1answer
71 views

PostgreSQL Sorting an output of a fulltext search query takes very long time

I have a table with a few millions of rows where I perform a SELECT with a complex fulltext search query in the WHERE clause (over a fulltext search index column) and with ORDER BY id DESC at the end. ...
-1
votes
1answer
84 views

Postgres not using Index for seemingly no reason [closed]

I have a table, items, with about 18 million rows. I want to be able to quickly retrieve where market > 0. This means that the item is on sale, and the market value as its price. To do so, I placed ...
0
votes
1answer
47 views

Why does my Query use a Bitmap Heap Scan

my simple query is not really fast and I don't know why my query is using Bitmap Heap Scan. explain analyze verbose SELECT connections, epoch_time FROM connections WHERE host_name = 'xyz.z' ORDER ...
1
vote
0answers
185 views

PostgreSQL - INSERT if it doesn't exist/RETURN if it does (query optimization)

This query will insert a new IP if there isn't one and return it's ID. And if there already is the same value it only returns the ID. I'm a newb with SQL and am curious if this could be further ...
0
votes
0answers
245 views

Postgres SQL CPU and RAM optimisation to improve decryption performance with pg_crypto

I am currently working on an application that requires column-based decryption for a few thousand of rows on a regular basis. The queries are decrypted using pgp_sym_decrypt, where several columns are ...
0
votes
0answers
35 views

SELECT * FROM small_table executes quickly, returns very slowly in PostgreSQL 11

I have a small table (~2500 rows, 37 col) being served by Postgrest API. After months of <1sec response times, it's now up to over 20seconds despite the table not being much larger. EXPLAIN ANALYSE ...
0
votes
1answer
279 views

Why is this postgres query ignoring the index in the subquery?

I have a couple of tables, companies and datapoints. If I query for datapoints from a given company, it uses the index: sophia=> explain analyze select count(distinct(source_doc)) from public....
0
votes
2answers
1k views

Slow planning time on PostgreSQL 12.4

I have a database with Openstreetmap location data, and in particular there is a polygon table with around 500 million rows. I created indexes on all of my queries consisting of using gist(way) where ...
1
vote
1answer
75 views

How to avoid errors in server log when conflict fails within a transaction before retry?

We have a table such as create table pages ( id uuid primary key not null, parent_id uuid not null references pages(id), shortname text not null, shorturl text not null, constraint ...
2
votes
2answers
198 views

Use BIGINT and twice the storage space or INTEGER but with extra operations?

Suppose I've a table named agency with some columns: internal_id(integer, unique) , external_id(bigint, unique) , name, location, created_at, ... internal_id and external_id are each one unique and ...
3
votes
1answer
211 views

Postgres Query Optimization, high cost functions

Currently I'm working with a postgres table that looks like this (postgres12) create table if not exists asset ( id text, symbol text not null, name text not null primary key (id) ); create ...
0
votes
1answer
130 views

Performance issues when INSERTing in one table, but not another that has many more indexes

I have the following table DDLs (names changed): (note the UUID PK) CREATE TABLE myschema.item ( item_id uuid NOT NULL DEFAULT uuid_generate_v4(), ref_id uuid NOT NULL, type_id uuid NOT ...
0
votes
1answer
37 views

Performance issue with postgres 11 order by and multiple or

Morning everyone We have migrated from postgres 10.6 to postgres 11.7 recently and we're facing some performance issues with some of our queries Specifically with this particular query: SELECT credits....
3
votes
0answers
94 views

Ordering of columns in a GIST index

I am modelling an application where events for factories can be registered. There are usually a small amount of factories (around 500), but each factory can register a large set of events (millions). ...
0
votes
1answer
628 views

postgresql prepared statements with JSONB containment

I'm trying to write a prepared statement using a date held in JSONB. A prepared statement doesn't allow the containment operator @> so I used the function name itself jsonb_contains which executes,...
0
votes
1answer
51 views

PostgreSQL is skipping indexes when using `order by` + `limit`

I have a postgreSQL database with two big tables, connections and sessions select "c"."id" "id", "ch"."title" "channel", "ch".&...
2
votes
0answers
52 views

Postgres NOT IN performance

I'm currently doing some exploratory work with Postgres 12, and have bumped into an issue I want to try and understand more about. My background is primarily Microsoft SQL Server, so I'm looking at ...
1
vote
1answer
62 views

Adding an index without a lag (PostgreSQL)

PostgreSQL: Adding an index to a table with big amount of data causes a lag for both SELECT and INSERT operations on that table, doesn't it? While PostgreSQL does not support live (without lags) ...
1
vote
0answers
543 views

How to increase max_connections in Postgresql-9.2

I need to increase production Postgresql-9.2 max_connections parameter from current 1000 to 3000. Used PGTune service to get the values and updated accordingly to the postgresql.conf file and /etc/...
4
votes
1answer
424 views

Does the fact a table is big can impact the overall performance of a PostgreSQL server?

If I have a table that is getting bigger and bigger (i.e. it is taking more and more storage space - currently 65GB), can this affect the overall performance of the PostgreSQL server, e.g. impact the ...
2
votes
1answer
1k views

Why are my queries in idle state?

I'm new to postgres , I've aws rds instance running postgresql with engine version 11.5. All my queries are clientRead has the wait_event. Why do I have all my queries in idle status.Does this mean ...
0
votes
1answer
1k views

Postgres JSONB date range search

I have a JSONB object with many elements, one of which is a date. The following query is now very slow with millions of rows in my table: SELECT * FROM table WHERE obj->>'dob' BETWEEN '2019-01-...
0
votes
0answers
65 views

How to increase the query performance on my fts query?

I'm pretty new to database world and I have the following table: Table "public.so_rum" Column | Type | Collation | Nullable | Default ---------...

1
3 4
5
6 7
15