Questions tagged [postgresql-performance]

Performance issues with PostgreSQL queries

Filter by
Sorted by
Tagged with
-1
votes
1answer
19 views

Postgres_log table import not captuirng SQL

While importing postgres log,sql is not inserting into table query column:
0
votes
1answer
15 views

Query problem with array_to_json()

Considering my basic knowledge of PostgreSQL I am unable to find out why my second statement for where clause (record->'details'->>'amount')::numeric(19,2) = 2.99 is not working with current ...
1
vote
1answer
47 views

Is there anything wrong with splitting a complicated view into many small ones?

I have a view where each row is associated with a customer, and the columns are various computed values such as life_time_value and purchases_per_week, as well as more complicated statistical values ...
1
vote
1answer
24 views

Improve PostgreSQL SELECT performance in a parent table (inheritance)

I have a PostgreSQL 9.2 database where each account has a schema, like below: My_Database |-> Schemas |-> AccountA |-> AccountB |-> AccountC |-> AccountD |-> ...
0
votes
0answers
11 views

Is duplicating “short cut” foreign keys a good idea to avoid multiple joins?

I have 3 tables A, B and C. C belongs to B, and B belongs to A. 1 entity of A can have 0 or various entities of B, 1 entity of B can have 0 or various entities of C. 1 entity of C has 1 entity of B, ...
0
votes
0answers
12 views

Is there a way to decrease the size of some indexes by trading off some precision?

I have a table that has a number of 32-bit int IDs that are used as foreign keys. The actual cardinality is under a million unique values. I want to reduce the size of these indexes so that they can ...
1
vote
1answer
41 views

How to prevent seek pagination from doing sequential scan over entire table

I have 3 tables and a materialized view: resource_categories contains all category names and metadata create table if not exists resource_categories ( category_id INT, title VARCHAR(255), ...
1
vote
1answer
23 views

Create 7 day rolling average in PostGIS for each feature

I have a timeseries dataset that looks like this: uid | geom | date | count | ------------------------------------- 1 | FeatureA | 2016-02-01 | 1 | 2 | FeatureA | 2016-02-02 | 2 ...
0
votes
1answer
32 views

Feasible to have thousands of foreign data wrappers in Postgres?

We are creating enterprise-grade SaaS where we will have many 1,000s of customers. We are considering creating a database in the Postgres server for every customer to ensure enterprise-grade security/...
1
vote
1answer
10 views

Can PostgresSQL cluster have synchronus and asyncronus standbys at the same time for the same dbs?

In this question, Write concern in PostgreSQL Laurenz Albe explained how to control the set of standby servers when data is committed to PostgresSQL, and that gave birth to another question. What if I ...
2
votes
2answers
40 views

How to determine or predict space on disk per table row?

I'm very new to Postgres so my math could be off here ... This is my table: CREATE TABLE audit ( id BIGSERIAL PRIMARY KEY, content_id VARCHAR (50) NULL, type VARCHAR (100) NOT NULL, ...
2
votes
1answer
37 views

Why is my indexed DISTINCT ON so much slower than my INNER JOIN?

I have two tables, customers and purchases. There are a lot (thousands) of purchases per customer. I usually only need the most recent purchase for each customer, which is why I have the ...
3
votes
1answer
27 views

Postgres 12: Are there performance differences between PRIMARY KEY(A, B) and PRIMARY KEY (B, A) on a table PARTITION BY (A)?

Given a table this table: CREATE TABLE tbl ( a int not null, b int not null, created timestamp not null default now(), primary key(a, b) ) PARTITION BY LIST(a) The query plan for a lookup on ...
0
votes
0answers
13 views

Can we partition or split json file while insert

I have a table with a JSON column, large files coming from the source. The issue is here while flattening data from that file is taking a long time some and sometimes DB server crashing and going to ...
1
vote
0answers
49 views

Table recent performance degradation

So on my current project we have an ETL process that on its final stage ends up writing about 35 million of records on table on a PostgreSQL database (PostGIS actually). The version we are currently ...
1
vote
0answers
28 views

Performance issues after PostgreSQL v12 upgrade

Just upgraded our RDS instance to v12.2 from v11.6, and we are seeing some of the queries are performing much slower than v11.6 instance. After the upgrade, we rebuilt the indexes using - REINDEX ...
0
votes
1answer
32 views

Load test with databases. Best practices , tools [closed]

I need to design a way to stress a clon of my production databases. At the moment, I'm using mysql percona xtradb with tree nodes running in google cloud. I'm using haproxy as load database balancer. ...
1
vote
1answer
34 views

How to select subset JSON with PostgresQL?

I'm trying to deal with a JSON column that does not have an explicit JSON schema or convention. There are varying keys within the JSON. Some keys do not exist, some keys do exist but empty string ...
0
votes
0answers
64 views

How to efficiently do partial updates on huge dataset in Postgresql

Disclaimer: I am a total "newb" in regards to PgSql, but due to some unfortunate aligment of circumstances I am in charge of this project which I have to rewrite/migrate away from SqlServer (alone), ...
1
vote
2answers
113 views

Indexed varchar column very slow (40M+ rows)

I've got an events table with over 40 million rows. It has a btree index on type: "index_events_on_type" btree (type). Any equality look up takes ages! I tried tuning up the work_mem to get rid of ...
0
votes
0answers
43 views

PostgreSQL errors and big database

Let's start from point that I have been introduced to PostgreSQL only a couple days ago after I received alert from monitoring system that one of local discs on one of VMs if almost full. After ...
0
votes
1answer
23 views

How to optimize fetching top N rows over sum w/ group by?

I have a big table with these fields: CREATE UNLOGGED TABLE mytable ( ts NUMERIC(16,6) NOT NULL, value INTEGER NOT NULL, fieldA INTEGER NOT NULL, fieldB INTEGER NOT NULL, ... ); I am ...
0
votes
0answers
14 views

Monitoring alert logs of Aurora and RDS postgres instance

I need to have a automation to grep the errors and receive a notification email around those errors in RDS and Aurora instances. What is the best way to approach this?
1
vote
2answers
58 views

Retrieving data from PostgresQL database with millions of rows takes very long

I've been working on a system where users can register as an user, create a book club and invite other people (members) to join. The user and member can all add books to the club, and also vote for ...
0
votes
1answer
35 views

Postgres query performance - hash aggregate costly operation

I'm running Postgres 9.6.6 and I have a relatively simple query, however, I'm finding that it is slow with the hash aggregation function which is the most costly operation: select ...
1
vote
0answers
28 views

Difficulty Optimizing Postgres Write Throughput

I'm trying to index a collection of roughly 127K files using a PostgreSQL 9.6 server on RDS. I had expected the process of writing these documents to the DB to take about 8 hours, but over time I ...
1
vote
0answers
29 views

How to monitor query load, counts, and durations in PostgreSQL

I'd like to understand load (average active), count, and, average time of various queries. This is an AWS RDS Performance Insights dashboard. This is the sort of information that I want, though as ...
0
votes
1answer
62 views

Optimize PostgreSQL query to avoid two time DB call for same operation

I am running following PostgreSQL query to support my service call for same filter criteria, any thoughts on how we can avoid these two DB calls for the same query and return JSON data in a single ...
2
votes
1answer
28 views

Index for a complex similarity query Postgres

I have a materialized view that has a few columns that I need to run a complex OR query on with similarity matching and the structure is like this mymatview: column a, column b, column c, column d, ...
0
votes
1answer
26 views

script to find all bigint that can be converted to int fields in postgresql performance

I want to compact a postgresql 11 db and my idea is to convert lots of bigint to int data types if and only if there is a reasonable lag to the limit of int data type considering the data inside. So, ...
2
votes
2answers
54 views

Is PostgreSQL function parallel safety intended for functions which update data?

I am having some trouble understanding function parallel safety. I can understand what it does - which is to specify whether the function will be executed by the parallel master node or whether the ...
0
votes
1answer
23 views

What can cause low cost and high runtime in EXPLAIN ANALYZE?

I have a database that pretty consistently runs queries in a magnitude of cost/10 ms. There are a couple queries where EXPLAIN ANALYZE reports a cost of 2000 (which I'd expect to be somewhere in the ...
1
vote
2answers
23 views

PostgreSQL 12 - Using both Similarity an ts_query at the same time, better performance?

Running a query with similarity or ts_query only or running them AND'ded together, the performance is good but the results aren't optimal for real time search. We use both similarity and ts_query in ...
1
vote
1answer
26 views

How can I improve a simple join on a very small table? (postgres)

I have a large table with a foreign key to a smaller table: CREATE TABLE cars ( make_model_id INTEGER INDEXED REFERENCES models (make_model_id), color TEXT INDEXED, year INTEGER INDEXED, ...
0
votes
1answer
21 views

setup table level Autovacuuming on postgres database

I am trying to setup table level Autovacuuming on postgres database. What all metrices I need to collect and analyze specific to that particular table? I have to enable autovacuuming on all the ...
0
votes
1answer
35 views

Postgresql Lateral Left Join Query Performance Issues - Takes a Long Time

I am trying to run the query below on a database with hundreds of thousands of lines but it is taking over 12 hours so far. It is the lateral left join that is taking so long. The reason I need the ...
0
votes
1answer
53 views

Offset vs Where performance for pagination with index

Suppose that you have a list of millions of items, each one with a timestamp (created_at). You also have an index on created_at DESC. Sometimes you need to navigate through all items, ...
0
votes
1answer
39 views

How to replace (SIMILAR TO + regular expression) with LIKE or ~ in PostgreSQL?

I have the following PostgreSQL function which checks if p_text1 contains the word/phrase p_text2 within it: CREATE OR REPLACE FUNCTION public."Contains"( p_text1 character varying, p_text2 character ...
1
vote
0answers
58 views

Postgres - slow timestamp query with <1 mln rows index

I have a table "ads" which has 300k rows and timestamptz field created_dttm. I also created an index CREATE INDEX idx_ads_created_dttm ON public.ads USING btree (created_dttm DESC) Postgres 11.5 ...
0
votes
3answers
260 views

Fastest way to choose distinct rows and a different order by without using a subquery

PostgresSQL - Join and get distinct left table rows only without gigantic subquery sort We have a midsized database that has 50k rows of products and 33m rows of product prices at various store ...
3
votes
0answers
91 views

PostgreSQL: filtering on array and ordering produces wrong plan / bad index choice

This is the story if a nightmare... I have this table: Table "subscriptions" Column | Type | Collation | Nullable | ...
0
votes
1answer
41 views

PostgreSQL bad plan: the frequency of a tag depends on the project!

I try to simplify a problem that I've been investigating for weeks. Suppose that you have a large table like this: SUBSCRIBERS ---------------------------- id | project_id | tags | ... --------------...
0
votes
1answer
17 views

Performance issues on json field update

I have a postgreSQL database on which I am experiencing huge latency issues when trying to update a json field using the following query: UPDATE collections SET "layout" = jsonb_set(layout, '{actions}...
3
votes
1answer
161 views

Aurora PostgreSQL database using a slower query plan than a normal PostgreSQL for an identical query?

Following the migration of an application and its database from a classical PostgreSQL database to an Amazon Aurora RDS PostgreSQL database (both using 9.6 version), we have found that a specific ...
0
votes
2answers
33 views

What happens at low level when I add a column to a table in PostgreSQL?

I have a table with number of rows in the order of billions. Let's say I want to add a column to that table. I would like to know what happens behind the scene to know the cost, expected time of this ...
1
vote
0answers
26 views

Slow query plan with small LIMIT [duplicate]

A query generated by our web framework leads to different query plans, depending on the number of rows to be returned. The problem lies within the combination of a low LIMIT and the ORDER BY clause: ...
0
votes
0answers
28 views

DB scheme for the home exchange site: store date ranges or individual days for availability

I am creating a db schema for a home exchange site. Host can set his home as exposed for exchange on certain dates. Guest must be able to view available dates. If exposed date is free (not reserved ...
1
vote
5answers
598 views

Get a truly RANDOM row from a PostgreSQL table quickly

I always used to do: SELECT column FROM table ORDER BY random() LIMIT 1; For large tables, this was unbearably, impossibly slow, to the point of being useless in practice. That's why I started ...
0
votes
2answers
64 views

Slow update performance

There is Postgres 10 (10.5 (Ubuntu 10.5-1.pgdg16.04+1)). The server has 256 GB RAM. shared_buffers = 64GB effective_cache_size = 192GB maintenance_work_mem = 2GB checkpoint_completion_target = 0.9 ...
0
votes
2answers
57 views

Postgres: SELECT with ORDER BY is 4x slower when using index

I have a table with 4 columns: id SERIAL PRIMARY KEY dt DATE val bigint -- Already has a btree index txt VARCHAR(64) I am running a query: EXPLAIN ANALYSE SELECT dt, val FROM temp_test ORDER BY dt;...

1
2 3 4 5
10