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.

0
votes
0answers
9 views

Is this database fragmentation or something else? What can be done?

I have the table below in PostgreSQL (automagically generated with insertions using Marten): create table mt_doc_order ( id uuid ...
0
votes
0answers
13 views

postgres 9.4 : process takes 100% CPU

when i run a request the potmaster process takes 100% CPU. Linux configuration, 8 GB RAM, 8 processors number and 4 core. Thank you
0
votes
1answer
26 views

Speed up slow postgres query with indices (~5 minutes)

I have roughly 100 million rows in a table called 'bets' and a couple million in 'games'. The problem is that even with indices particular queries run very slow. The tables are structured as so: ...
3
votes
2answers
38 views

Bad performance on recursive query in PostgreSQL

I have bad performance with recursive query in PosgreSQL. psql --version psql (PostgreSQL) 10.7 (Ubuntu 10.7-1.pgdg16.04+1) The idea is to have tree-like structure of comments. So when parent_id is ...
0
votes
0answers
28 views

Does keyset/cursor pagination scale query performance better than offset pagination?

I am seeing behavior where the query that powers my infinite scrolling list seems to be slowing down as the number of rows grow. Naturally, I used offset pagination because the library I am using ...
2
votes
1answer
29 views

Slow performance on postgres query with count/sort

I've inherited an Django/postgres app from another developer and I'm finding many of the queries are very slow. I've spent a lot of time correctly organising/normalising the data which was not done ...
0
votes
1answer
27 views

Progressive child tables scan in PostgreSQL?

We have append-only events table where events from different devices are being collected. We use PostgreSQL 9.6 and pg_partman to split the table into monthly partitions. Data is partitioned using ...
1
vote
0answers
26 views

Postgres configuration for high-latency storage

I'm trying to setup a postgres server using Ceph as storage. After taking benchmarks, it was obvious that even though the storage has a really good throughput, the higher latency brought by network ...
1
vote
0answers
45 views

How can I optimize my user/password table?

For a uni project I'm looking at leaked usernames and passwords. I have roughly 275 million rows in my table. My table: CREATE TABLE table ( username varchar(300), password varchar(300) ); ...
0
votes
1answer
25 views

Select only last row from table for list of values

As example we have a table: CREATE TABLE t1 ( a BYTEA NOT NULL, r_pointer BIGINT, s_pointer BIGINT, data BYTEA, PRIMARY KEY(a, r_pointer) ) PARTITION BY HASH (a); In case we I want ...
1
vote
0answers
20 views

PostgreSQL - SELECT query with ORDER BY and LIMIT 4 very slow, same query much faster with LIMIT >= 40

I've got following sample query from our Postgres DB: SELECT * FROM advertised_car a0_ LEFT JOIN car_check c1_ ON a0_.car_check_id = c1_.id WHERE c1_.status = 'ACCEPTED' ORDER BY a0_.id LIMIT 4; ...
2
votes
1answer
79 views

Improving performance of count where only result > 0 matters

I have following query: select count(*) -- rp.tag from submission inner join post rp on submission.id = rp.submission_id where user_name = ?1 and lower(rp.tag) like ('%verified%') and not lower(rp....
1
vote
1answer
50 views

Why are some count queries so slow?

I have a table users with 2.6 millions of rows and SELECT COUNT(*) FROM users takes 2 seconds to succeed, but request for table stories with 5.2 millions of rows takes more than an hour. Explains are ...
0
votes
0answers
34 views

Optimize query migrated from SQL Server

I have been working on SQL Server for more than 10 years, now I have a database which I want to confirm if it is viable to change it to PostgreSQL, this (simplified) query runs in about 100ms in SQL ...
1
vote
0answers
157 views

Postgres bulk upsert performance

I am doing bulk UPSERT on a table with 30 million rows. The table has just two columns (varchar as a primary key and integer). The input data is imported to the temporary table first, and then bulk ...
0
votes
0answers
10 views

How to optimise PostgreSQL planning time

I have a small query using postgresql 9.6.11 and postgis 2.3.1+dfsg-2 select ST_Transform(ST_SetSRID(ST_Point(671699.9999988,6585300.5001232),32633),3006); that is slower than I like for where it is ...
1
vote
1answer
29 views

PostgreSQL Index Only Scan can NOT return ctid

I have this table in PostgreSQL 11: CREATE TABLE A (id bigint PRIMARY KEY, text text) Now I want to fetch the ctid of rows meeting some condition like id = 123. However, even using pg_hint_plan to ...
0
votes
0answers
37 views

Cleaner approach for Recursive CTE query

I have a table called entry_changes defined as follows: Table "public.entry_changes" Column | Type | ...
1
vote
1answer
38 views

Recommended postgres parameters for index creation with 244gb RAM and 32 CPUs?

I have a 200 GB database with 26 tables that I need to create indexes on. Currently index creation takes a long time on the biggest tables, as they have ~12gb and 400,000,000 rows in them (>4 hours)....
0
votes
1answer
16 views

Postgresql table function: get where condition

I have defined a function that returns a table. In this function, I use multiple temporary table to pre-elaborate data. Here, there is a very simple example: create or replace function public....
0
votes
1answer
68 views

In Postgres 9.6, why is the GIN index not used for a JSONB column with text/int array?

In Postgres 9.6, why is the GIN index not used on my SELECT query below for a JSONB column with text/int array? How do I force Postgres to use the GIN index? SET enable_seqscan to FALSE; EXPLAIN ...
1
vote
1answer
42 views

Index partitioned table to prevent Postgres from doing a sequential scan

I have a Postgres database with a partitioned table that will contain ~2,000,000,000 entries. I have defined a partitioned database based on the first letter of the "identifier" - this is split into ...
0
votes
1answer
34 views

PostgreSQL poor performing order_by ASC

I have a situation where I prepare a list of buffers to process by a task queue. Currently my buffer table contains over 350.000 rows. Every estimated 30 seconds the following query is ran to check ...
1
vote
1answer
33 views

PostgreSQL doesn't generate index-only plan when using NOT-IN but does when using IN with multicolumn covering index

I have two tables: A(id, x, cid) and B(cid). I need to fetch some records from A and excluding records with same cid in B. I have btree index on A (cid, id). Query-1 (NOT-IN): select id from a where ...
2
votes
0answers
75 views

What's the more performant database design for query speed and write overhead when storing many boolean columns?

Setting the scene I have a PostgreSQL 11 database running on a very fast 64core, 10 SSD HW RAID 10 server. In the database I need to create a table with a couple of million rows, let's call it cars. ...
1
vote
1answer
59 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
16 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
30 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
30 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
20 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
62 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
40 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
77 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
43 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
284 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
12 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
149 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
98 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
19 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
83 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
33 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 ...
3
votes
1answer
40 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
144 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
78 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
52 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
25 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
104 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
64 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
132 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 ...