Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [postgresql-performance]

PostgreSQL performance optimization. Essential to include sufficient information in your question as outlined in the full tag wiki.

1
vote
1answer
50 views

Optimizing read query in PostgreSQL

I'm having a tough time optimizing a read query from PostgreSQL 10. The query is as follows SELECT mp.id_person, ( COUNT(CASE WHEN mp.id_g_type = mc.id_g_type (and mc.g1 ...
0
votes
0answers
15 views

Are multiple simultaneous direct loads a better strategy than individual inserts in PostgreSQL?

In our application we run a background processor called Sidekiq which allows us to run hundreds of idempotent jobs against the database in parallel. We have one job which can add up to 3,000 inserts ...
1
vote
1answer
27 views

Manually setting most_common_vals in Postgres

I am currently building a system for exploration purposes on top of Postgres. Essentially, this results in queries on a table of form (key, value) pairs, representing a graph model: edge_id | term_id ...
0
votes
1answer
24 views

Why does a count obtain an ExclusiveLock in Postgresql?

And what does this mean for other CRUD operations going against that table? I have this query (actually I'm trying to write it 9 ways from Sunday to get it to return in less than an hour): SELECT ...
0
votes
0answers
18 views

Can't get counts back from large table in Postgres

I have a table with 1.2 billion rows in it. The table has 60-70 columns, the vast majority of which are empty. The table is indexed on subscription_id, among other indexes. My query is: SELECT ...
0
votes
1answer
58 views

Huge DB (> 600M records/table) times out on query or hits memory limits?

Dearest DBA friends of Stackexchange, I am trying to write data from multiple sourcetables into a new table containing compound information that i wish to use as source for a later project. The ...
0
votes
1answer
24 views

Add previous column alias to calculate sum/total of last column

I have a query which I need to optimize. It is using 3 subqueries 2 times each. Below is the query. select i.itemid, b.itemname, i.batchno, (select sum(qty) from inventtrans ...
0
votes
0answers
32 views

Steps to unlock a locked select in postgres

I have tried to update a table in Postgresql 9.6 (AWS) based in two inner joins. Since it was taking too long, I cancelled it. Now I'm trying to run a query to count records from the inner join but ...
1
vote
0answers
39 views

Validation within a transaction with all violations in response

I'm trying to solve a problem with some sort of offline integration where data occasionally (once in several days) comes in a form of csv files. It can be new rows or updates for existing ones. Total ...
1
vote
0answers
46 views

Postgres stuck on IO/LWLock

I have an instance running Postgres v PostgreSQL 10.3 (Debian 10.3-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit. We have several indexes, GIN ...
0
votes
0answers
9 views

Have anybody configured PostgreSQL BDR replication? how useful it is?

We have a requirement to configure multimaster replication in postgreSQL. we have 2nd Quadrant BDR replication but I wanted to make sure is it error prone or any disadvantages are there configuring ...
2
votes
0answers
30 views

Postgresql slow remote execution when using alternative clients

I noticed a strange perfomance issue when running sql queries on a remote Postgresql Server. Everything good when using psql: Connect via ssh and run things locally $ ssh user@example.org user@...
4
votes
1answer
63 views

Slow left join lateral in subquery

I have three tables: units (apartment units), leases, and line_items (a better name would have been recurring rents). A unit has many leases and lease has many line items (associated with each rent ...
0
votes
0answers
16 views

2 postgresql identical rds instances and one is significantly slower

I have 2 instances of free tier rds postgresql db.t2.micro on same zone us-west2 region on 2 different accounts, using defaults at creation, Original and Copy. Original is significantly slower than ...
0
votes
1answer
28 views

How to store LOB-image or PDF in Postgresql and what is the impact of having in Postgresql?

Please guide me to store LOB-image or PDF in Postgresql and let me know what is the impact of storing these types of objects on Postgresql database. Any help will be appreciated.
0
votes
1answer
23 views

Conditional insert vs UNIQUE constraint

https://stackoverflow.com/questions/4069718/postgres-insert-if-does-not-exist-already I would like to check if something exists before inserting it, what should I rely on? UNIQUE constraint or ...
0
votes
1answer
25 views

group by like string

I have a table employee in this table i have id company_name employee_id 1 abc 2015_acb_1 2 abc 2015_acb_2 3 abc 2015_acb_3 4 abc 2015_acb_4 5 xyz 2016_xyz_9 5 xyz 2017_xyz_2 5 xyz ...
2
votes
1answer
36 views

SELECT very slow when JOIN and LIMIT are specified but no/few results found

I am running into an issue where the query planner does not take into account how a JOIN effects the frequency of particular values. For some context: I am storing sessions in an append only log ...
2
votes
1answer
110 views

Postgres partial index on IS NULL not working

Postgres version Using PostgreSQL 10.3. Table definition CREATE TABLE tickets ( id bigserial primary key, state character varying, closed timestamp ); CREATE INDEX "state_index" ON "tickets"...
1
vote
1answer
66 views

How to Prevent heavy queries on one database, affecting the performance of other databases on same server?

I have a linux server running just Postgres with PostGIS Extension. There are several databases on this instance of the Postrges Server. One of the databases has some computationally intensive ...
0
votes
1answer
29 views

SELECT vs COPY (SELECT) TO STDOUT

I need to read more rows than fit in memory, resonably fast. I see two options: SELECT ... where my bindings use a cursor to stream the rows; COPY (SELECT ...) TO STDOUT (FORMAT binary) where my ...
0
votes
0answers
23 views

Adding LIMIT slows down query

SQL CREATE TABLE actions ( action_id bigint GENERATED ALWAYS AS IDENTITY, block_time timestamptz NOT NULL DEFAULT now(), block_num int NOT NULL, trx_id text NOT NULL, ...
1
vote
3answers
82 views

How to apply outer limit offset and filters in the subquery to avoid grouping over the complete table used in subquery in Postgresql

I have legacy tables similar to the following: employee ------------------------------------ | employee_id | name ------------------------------------ | 1 | David | 2 ...
0
votes
0answers
27 views

Very high memory usage by Postgres 10.1 on Windows 2008 R2

On Windows start up, ram usage is around 5% but it gradually keeps increasing and will eventually balloon to where it is using almost all the ram.The DB size is only 4941MB. The server has 8gb total ...
1
vote
1answer
103 views

JSONB Array of Strings (with GIN index) versus Split Rows (B-Tree Index)

I have a database which stores receiver to indicate which account the data relates to. This has led to tons of duplication of data, as one set of data may create 3 separate rows, where all column data ...
0
votes
0answers
14 views

Performance issue of scheduled Java app after migration from Oracle to Postgresql 9.5

I have a strange issue, that I hope I can get some help on. I'm not too advanced in Postgresql so maybe I'm missing obvious things as well. Our company is planning to move from Oracle to Postgresql, ...
0
votes
0answers
34 views

What determine how much time the query will cost?

I'm developer a system, and the database was not made for me. In the beginning all query of the database are super fast, because i don't have any data. Along the project the database grown, but the ...
0
votes
1answer
28 views

Analysing and improving the performance of a query generated by Django's ORM (multiple JOINs, missing indexes?)

I have a not-so-complex (imho) filtering logic based on several conditions in my Django models. There is one particular query which takes an inusual amount of time to finish. The query is built ...
0
votes
1answer
22 views

Modest sized DB with 320 second SELECT

I noticed that one of my Hibernate JPA SELECTs against my Heroku PG 10.4 instance is taking a loooog time to complete as this EXPLAIN (ANALYZE, BUFFERS) shows. The database is 591MB running in PG 10....
1
vote
0answers
60 views

pgbench - 20-30% variation in benchmark results (non-repeatable benchmarks)

Problem I'm trying to use pgbench to help me tune Postgres for my hardware, but I'm seeing a very strange behaviour. I cannot seem to get a stable number for TPS in consecutive runs of pgbench. Since ...
0
votes
2answers
51 views

Slow Sum/Group By over large amount of rows

Please can you help with a slow query that we have with a large amount of data (~500K rows). We're on Postgres 10. SELECT mike_romeo, SUM(hotel) as counts FROM whiskey_lima WHERE yankee = 'kilo' ...
0
votes
0answers
62 views

postgresql: live tuples not matching table row count

We're experiencing poor performance with a table. The table is regularly updated with insert/updates, and is a critical table in an application. In looking into the problem I've noticed that the row ...
0
votes
0answers
16 views

Postgres - Storing Attributes in 1:0 related object, performance improvements?

In my DB's central table, "Profiles," I want to collect activity statistics that will be updated often, although not all profiles will have statistics. The "Profiles" table is already heavily updated ...
2
votes
1answer
68 views

PostgreSQL performance with (col = value or col is NULL)

This question deals with PostgreSQL 9.5 query performance. The table is: CREATE TABLE big_table ( id integer NOT NULL, flag bigint NOT NULL, time timestamp with timezone NOT NULL, val ...
1
vote
1answer
74 views

Adding LEFT JOIN to a query has increased execution time 10 times

On a CentOS 7.6 server (Intel Core i7-6700, 64 GB DDR4 RAM, RAID1 SSD) I run a backend written in PL/pgSQL and Java for a mobile and desktop word game with the following Linux packages: postgresql10-...
2
votes
1answer
115 views

Can I use hypertables in timescaledb merely to get better insert rates?

I have a PostgreSQL db that I have a large bulk load running into. I wish this load to be as fast as possible. I'm already using the copy command etc. I have been reading about timescaledb and how ...
1
vote
1answer
64 views

Equivalent Join Conditions Yield Vastly Different Results

The following query aligns test data with the closest truth data equal to or earlier than the test data for all possible truth_id's. There are very few truth_ids and very few test ids, but each id has ...
2
votes
1answer
99 views

PostgreSQL most efficient way to reference multiple tables

I am looking for the most efficient way to reference multiple tables in one table, when there is only one reference possible at a time. Which means tables A and B are referenced by table C, but both A ...
0
votes
0answers
16 views

Compare PostgreSQL Execution Plans

PostgreSQL newbie here. I'm trying to optimize a function that returns a table. Essentially it boils down to either creating a temp table and then selecting/joining on that table to return results, or ...
0
votes
1answer
26 views

Should we favor multiple INSERTs or multiple UPDATEs

I always have an impression that INSERT would be beneficial for performance (let's forget storage for now) rather than UPDATES, in all the DB that I know. Especially in Postgres, UPDATES involves ...
0
votes
1answer
21 views

Update column value divisible by 100

Hello experts I need to update next column value with divisible by 100
2
votes
0answers
37 views

Poor query planning in Postgres 10

The query planner in Postgres10 seems to be ignoring the fact that my tables are clustered. I have to explicitly put "hints" in my query to have it perform the correct type of join. Here's my ...
2
votes
1answer
71 views

How can it be possible that sum operation is so much faster than count?

When I make a simple benchmark test in a table that has 11.065.763 records. I get the results below: select sum(t.amount * t.exchange) from table t; Finalize Aggregate (cost=658027.39..658027.40 ...
1
vote
1answer
53 views

Poor postgres performance when deleting rows with large BLOBs

I have a PostgreSQL table like CREATE TABLE stuff (id BIGSERIAL, data bytea, info TEXT) the data blob is typically in the 1 .. 10 MB range per row. The info text is just a few characters. The table ...
3
votes
0answers
85 views

PostgreSQL Enum : Search on ENUM type

Defined an ENUM CREATE TYPE currency AS ENUM('GBP', 'EUR', 'USD'); Using this as a type in a table CREATE TABLE if not exists transaction( id BIGSERIAL NOT NULL PRIMARY KEY , amount NUMERIC(35,...
2
votes
1answer
74 views

System configuration to address poor PostgreSQL INSERT performance

Problem Symptom High CPU usage by postmaster child process associated with client connections attempting to INSERT low-volume rows (results in rows INSERTed 25x slower than using COPY ... FROM STDIN ...
1
vote
1answer
63 views

Adding a CHECK constraint doesn't improve performance of ATTACH PARTITION on postgres table

I'm attempting to merge a table into a partition, the merge works but the performance isn't good. I've read this link, in particular this quote.... Before running the ATTACH PARTITION command, it ...
2
votes
1answer
182 views

Performance impact of updating target columns with same values ON CONFLICT

This is a question about the inner-workings of Postgres (v10) and performance. Given a table, github_repos, with a multi-column unique index on org_id and github_id columns, is there any performance ...
1
vote
0answers
34 views

How to VACUUM FULL a single partition without stalling the parent?

I have a table called TABLE_A that's partitioned by month. Each partition is named according to it's month, TABLE_A_201810, TABLE_A_201811 etc I've noticed that if I attempt to VACUUM an old ...
2
votes
3answers
116 views

Postgres - how much space is required to perform a VACUUM

I know that doing a VACUUM FULL on a table will free up disk space and return it to the OS. There's been a couple of occasions where my OS (windows) has warned me that it's running low on disc space. ...