Questions tagged [postgresql-performance]

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

Filter by
Sorted by
Tagged with
0
votes
0answers
29 views

Select query taking too much time in postgresql

I have the following table in postgresql 9.5 database on ubuntu 16 (4 gb ram, 2vCPU): create table stat_data ( name varchar NOT NULL, the_date date NOT NULL, uopen numeric, uhigh ...
1
vote
1answer
35 views

Optimize Postgres query where a time range as well as IN values

I have a table that gets around 20 million new records a day for positions of vessels. I want to query for a couple of different vessels and get the records for any period. As I'm using this for ...
1
vote
1answer
48 views

How do I get PostgreSQL FDW to push down the LIMIT to the (single) backend server?

I've set up a PostgreSQL FDW server with the following table, sharded by user_id over four servers: CREATE TABLE my_big_table ( user_id bigint NOT NULL, serial bigint NOT NULL, -- ...
0
votes
1answer
51 views

Large bulk insert performance difference PostgreSQL vs MariaDB (InnoDB)

I've got the very same single-table definition in both PostgreSQL 11.4 and MariaDB 10.4.6 (InnoDB) and I'm seeing a significant difference in INSERT performance I cannot explain for PostgreSQL. My ...
0
votes
0answers
30 views

postgresql query performance issue with timestamp and order by filter

I have table definition as following. CREATE TABLE monmgr_pg.order_staged_events ( event_id varchar(64) NOT NULL, order_number varchar(40) NOT NULL, oms_order_key varchar(64) NOT NULL, ...
0
votes
1answer
41 views

`cube` query performance issues with ordering

I have a cube column named embedding in a documents table storing a vectorized (TF-IDF) representation of some text in the field which I converted into dense format. I created a GIST index on the But ...
0
votes
1answer
31 views

Improve time for Select first 10 Rows of a Table with 10 Million Rows Postresql

I am working with a table of 10 million rows and I am selecting the first 10 or the last 10 rows with this query: SELECT * FROM category_member ORDER BY (p::float8/q) DESC LIMIT 10; I am ordering ...
1
vote
1answer
36 views

Postgres choosing a filter instead of index cond when OR is involved

I have a table that daily gets about 20 million records added to it, and I'm trying to paginate through it to let people get access to all the data in it, but the query time has to be "decent" (in my ...
0
votes
1answer
39 views

SELECT optimization in Postgres 10

I want to select last unique rows based on time, so: SELECT DISTINCT ON (track) * FROM eco.tracks WHERE id > (SELECT id FROM eco.tracks WHERE time_track < ((SELECT time_track FROM eco....
0
votes
1answer
38 views

How to query results from a huge postgres database?

I am trying to query a few tables in a 125GB Postgres database. I find a hard time getting the results back quickly! Even to find unique rows of a column it takes more than 10 minutes. Either I use a ...
0
votes
1answer
9 views

Postgres record locking issue - could asynchronous UPDATES be a solution?

Is it possible (and sensible) to issue asynchronous UPDATEs to a postgres table? Here’s the issue. I have two process, both of which access the same table. Process “A” loads vast quantities of ...
0
votes
0answers
22 views

Advantage and Disadvantage of Partitioning by some of values of Primary Key

I'm going to implement/deploy a table like this: CREATE TABLE docref ( namespace VARCHAR(50), key VARCHAR(50), time_order INT, state SMALLINT, hash TEXT, ...
1
vote
1answer
29 views

Optimize a slow running Postgres SQL query

I have a SQL optimization question. Raw SQL SELECT releases.id AS release_id, releases.name AS release_name, releases.label_id AS release_label_id, releases.provider_id AS release_provider_id, ...
1
vote
2answers
55 views

Postgres: why did this trigram index slow down regexp queries?

I have a TEXT keyvalues column in Postgres: select * from test5 limit 5; id | keyvalues ----+------------------------------------------------------ 1 | ^ first 1 | second 3 ...
3
votes
2answers
21 views

Optimizing PostgreSQL where clause with OR

I have a query that uses an or in the where clause. It is slow. It seems that re-writing the query using a union could get the same results much quicker. But I'm in an environment where we generate ...
1
vote
2answers
48 views

Why does a query take so much longer for 1 additional identical join?

I am trying to figure out why with the query below, adding the last join takes so much longer. If I run it with the last join commented out ( level 3) it runs in less than a second. Adding the 3rd ...
1
vote
1answer
56 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
1answer
40 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
47 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
45 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
51 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
29 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
34 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
48 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
40 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
21 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
83 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
65 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
36 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
566 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
11 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
35 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
40 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
68 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
20 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
150 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
107 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
36 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
79 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
60 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
18 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
36 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
36 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
24 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
65 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
73 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
194 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
47 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
425 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 ...