Questions tagged [postgresql-performance]

Performance issues with PostgreSQL queries

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

PostgreSQL performance benchmark results of version 9.5 and above [closed]

We are trying to evaluate Postgres version 9.5 and above in terms of performance benchmarks. Could someone please share links/stats of official Postgres performance benchmark results on hardware for ...
1
vote
0answers
24 views

Recursive CTE based on function values significantly slower on Postgres 12 than on 11

Following up on my question about some queries in Postgres 12 being slower than in 11 I think, I was able to narrow down the problem. It seems like one recursive CTE based on function values is the ...
0
votes
0answers
16 views

Improve PostgresSQL aggregation query performance

I am aggregating data from a Postgres table, the query is taking approx 2 seconds which I want to reduce to less than a second. Please find below the execution details: Query select a....
0
votes
0answers
19 views

What could cause inconsistent time spent in database? [closed]

In a request response cycle only sometimes I see more than usual time being spent in the Database. I would like to understand the probable cause for this, I have a pretty consistent traffic on the ...
0
votes
1answer
19 views

paginating over large number of postgresql rows in a sorted order [closed]

The goal is to retrieve many millions of rows ordered by their timestamp. Since there are so many rows, I need to retrieve them in chunks. I've read about paginating over results, but all the ...
1
vote
3answers
29 views

PostgreSQL (big data): search by tags and order by timestamp

We need to add a search feature on a large table (200M+ rows): item_id | tags | created_at | ... ------------------------------------------------------------------- ...
1
vote
2answers
44 views

Determining Whether Query Load is Too High for PostgreSQL Server

I just started experiencing some write-heavy load on a PostgreSQL 11.2 system running inside a Docker container on an Ubuntu 18.04 server. PostgreSQL is receiving about 1-4k INSERT queries/second. ...
1
vote
0answers
114 views

Upgrade from Postgres 11 to Postgres 12 made some queries 300x slower, probably due to new CTE handling

My application currently uses PostgreSQL 11.6. Today, I have tested PostgreSQL 12.1 on a virtual machine and the results were shocking: One important query which takes 100ms on version 11 (same VM) ...
2
votes
1answer
38 views

Strange performance of a top-N query with Postgres

I have a table transfer_event from which I need to find the latest n entries for a set of tokens; both n and the tokens are supplied by the user. I am using Postgres 9.6. The question is that if I ...
0
votes
0answers
24 views

pg_restore idling during second half of restore

We're migrating from PostgreSQL hosted on our VM to Azure Database for PostgreSQL managed database. One of the first problems we hit has been time to restore database dump. It took 20-30 min on our VM ...
0
votes
1answer
26 views

What to expect from a benchmark?

I'm using pgbench to test a cloud SAN (similar to EBS). The problem is, I don't really know what to expect given any system configuration. Meaning, that even before I start tuning PostgreSQL ...
0
votes
1answer
63 views

Very slow query despite index being used

I have this table, created to hold tons of modbus measurements that comes from my devices: CREATE TABLE public.tv_smartdevicemeasurement_modbus ( measurement_id serial NOT NULL, insert_time ...
0
votes
1answer
33 views

Table with huge data takes long time to query

I have a table in Postgres that is having over 50million records in production. It’s a table that I have to query it often for debugging and data extraction because it is heavily used by the ...
2
votes
1answer
30 views

Why EXPLAIN doesn't show heap fetches for index scan

I tried to compare potential perfomance difference between covering b-tree index and simple b-tree index and was confused with EXPLAIN(ANALYZE,BUFFERS) output. Test environment -- function to fill ...
0
votes
1answer
41 views

Postgres 12.1 uses Index Only Scan Backward instead of index when LIMIT is present

I have a medium sized table (~4M rows) of "functionCalls" which consists of 2 columns, input and function (both ids for another table): Column | Type | Collation | Nullable | Default ----------...
0
votes
1answer
40 views

Why does a CTE cause a full table scan?

UPDATE I've posted a newer more focused question How to use index for simple time range join? I'll keep this for reference. ORIGINAL I have a large (~100 million rows) timeseries table t_16 where ...
1
vote
1answer
30 views

Adding condition (Similarity) causes sequence scan

Struggling with this one a little bit, as I would have expected the performance to be better combining the queries that separated. The queries are produced using Django ORM 1.11. With the ...
1
vote
1answer
28 views

Postgresql similar of SQL Server Query Store

Is there any extension available for Postgresql that is similar to SQL Server query store. So that it can help identify performance bottleneck. SQL Server query store provides information on how many ...
1
vote
2answers
34 views

Infix search on large table when majority of rows can be filtered out

We have a Postgres table, which looks like this: +-----------+---------+---------+---------+ | CLIENT_ID | VALUE_1 | VALUE_2 | VALUE_3 | +-----------+---------+---------+---------+ | 11234 | ...
1
vote
1answer
72 views

Optimizing a query with many permutations (using Postgresql 11)

I'm trying to figure out the optimal solution for filtering a result set by every permutation of a given input criteria. We're using Postgresql 11 on AWS RDS I've created a sql fiddle here that ...
1
vote
3answers
41 views

Cost of updating one of several toasted columns

I have a table in Postgresql 12 which has --- among other columns --- two columns a and b of type bytea which are most of the time TOASTed. STORAGE is set to EXTENDED for both of them. Assuming all ...
0
votes
0answers
10 views

pg_stat_user_indexes.idx_scan is zero while pg_statio_user_indexes.idx_blks_hit is not

I am hunting for unused indexes comparing 2 snapshots: CREATE TABLE snap_stat_idx_m10_d29_18_02 AS SELECT * FROM pg_stat_user_indexes; CREATE TABLE snap_statio_idx_m10_d29_18_02 AS SELECT * FROM ...
1
vote
2answers
44 views

Pre-sorting postgres table instead of running CLUSTER

Does pre-sorting a csv by a designated column and then inserting the data into postgres have the same effect as not pre-sorting, importing the data, creating the index and then CLUSTERing the table ...
1
vote
1answer
49 views

Query plan for a dynamically built `query_int` using `format()`

To make things simple here is a statically built version of the query that runs perfectly fast: SELECT * FROM media WHERE tag_ids @@ '123&321'::query_int ORDER BY rating DESC LIMIT 20; The catch ...
-2
votes
1answer
50 views

Calculation showing wrong result [closed]

I am using postgresql to display QA% in my project. Now issue is that, QA% for one of the user is showing wrong data. this is my calculation I am using to show QA% (to_char(( (coalesce(s_qa_num,0) ...
0
votes
1answer
32 views

Full text search over multiple CPU processes?

Is it possible to configure Postgres to split out a full text search over multiple CPU processes in an attempt to complete quicker? I'm running a full text search on 2 million records on a GIN ...
1
vote
2answers
58 views

Optimizing a query by adding an index

Here are my tables: accounts_company: - id ... accounts_companyview: - account_id: FK to company - viewer_id: FK to company shipments_address: - companyview_id: FK to companyview - is_shipper:...
2
votes
1answer
196 views

Find a row by primary key, when you don't know in which table it is?

I'm the (inofficial) DBA responsible for a proprietary OODBMS. Management wants us to move to Postgres, to reduce license costs. The move should be progressive, therefore we should also keep the same ...
1
vote
1answer
104 views

What is the performance cost of enabling “log_lock_waits” on Postgres?

I am trying to understand what is the performance cost of enabling log_lock_waits on Postgres. As per this documentation, while log_lock_waits = on: Controls whether a log message is produced when ...
1
vote
0answers
53 views

Merge join does not use index for sorting on PostgreSQL

I am running PostgreSQL 10.9 and stuck with a strange problem. My schema is a typical EAV with two tables: contacts and contacts_values. There are two relevant indexes: create unique index ...
0
votes
0answers
30 views

Size and performance different on UPDATE with/out intermediate CTE

I've got two tables with an indexes id column in common. I'm trying to add a new column in the target table using the value of a column in the source table. The two tables are rather large with couple ...
1
vote
3answers
54 views

Most efficient way to insert data into archiving table without influencing production?

For example lets say we have table A: create table if not exists T( column1 int, column2 varchar, column3 date ); and archiving table TArchive: create table if not exists TArchive( column1 int, ...
0
votes
1answer
20 views

PostgreSQL - tuning multiple clusters on server

I usually use http://pgtune.leopard.in.ua/ for tuning postgresql parameters depending on hardware resources. But now I want to tune two clusters on same server. How should I tune PostgreSQL ...
1
vote
1answer
51 views

HLL aggregation query is very slow

I have the following query select ts_date, sum(bids_placed), hll_cardinality(hll_union_agg(eligible_uu)) from performancedata where ts_date>='2019-08-01' and ...
0
votes
1answer
46 views

How to find all participants of a chat thread?

In my database I have the following tables: user, post, post_participant, and post_reply, largely as inspiration from this answer. One thing I'm having trouble with figuring out though is how to find ...
1
vote
1answer
137 views

Long running READ queries stuck in “idle in transaction”

I have a JDBC connection pool in a web API that makes frequent requests to the RDS Instance. Each request is for a long running SQL query where PG decides not to use indices because the amount of data ...
0
votes
1answer
101 views

Improve speed of COPY COMMAND in postgres

I would like to understand if increasing work_mem does help improving the speed of the COPY command. Does COPY use work_mem or maint_work_mem extensively?
3
votes
3answers
165 views

Any drawbacks of using GIN PostgreSQL index for an integer foreign key?

I have a large table (600 millions rows) with a foreign key other_id of type integer. A single value of the foreign key is repeated about 100 times on average. I need to have an index on that FK ...
0
votes
3answers
103 views

Array containment queries in Postgres

Question on GIN index on array. I have 2 million rows in a work table.And I need to find work that a user can do, based on the skills he has and also the highest priority work. User always will have ...
1
vote
1answer
393 views

Efficient SELECT for array of tuples in PostgreSQL

Assume I have a table records with the following structure id (unique int) updated (timestamp) I have an input_array with values [[id_1, timestamp_1], [id_4, timestamp_4], ...]. I'll refer to each ...
0
votes
1answer
61 views

Optimize a postgresql database view query

Given the following database schema (using rails-like associations, as this is a ruby-on-rails project, also please forgive me for how I phrase this for I am new to this forum): Client has_many Deals ...
2
votes
1answer
50 views

Postgres memory settings (RAM, work_mem, etc.) for complex text searches on indexed tsvectors

I know very little about Postgres memory settings. I have developed a paid-access database, which mostly stores text (and some metadata) 2 tables have 10 millions rows with little text (1 paragraph ...
1
vote
1answer
167 views

Postgres table with 35m rows slow queries. how can i improve the performance?

SELECT count(c.id) as clickCount FROM clicks c LEFT JOIN links l on c.link_id = l.id LEFT JOIN user_agents ua on c.user_agent_id = ua.id AND ua.robot IS NULL WHERE l....
0
votes
1answer
98 views

How to deal with different badges in Postgres Explain Visualizer?

I've been working with several DBMS during my career, but always Oracle/Sql Server/MySql. Now I have to deal with PostgreSQL and my first task is to tune several queries to speed them, as our DB is ...
1
vote
1answer
48 views

using pg_prewarm after an ETL load has finished - worth it?

I have a database with many small reference data tables and a few large time series tables. The reference data tables can easily fit into memory. I have various ETL jobs that load data, mainly by left ...
0
votes
0answers
12 views

Postgres Database is overflow and Postgres Service not Starting

I am using the Linux file system. There one device which has 4GB emmc and we allocated 1.4 GB for the user. see below the output of df -h. We store the database in mmc1 partition, I Have a Drive of ...
0
votes
3answers
857 views

Postgresql 11 - slow insert performance when table reaches ~5 million records

I have a table with ~6 million records that gets loaded on a postgresql instance in a small machine (with standard PC HDD). It seems to be a I/O problem, since there are free memory and processing ...
1
vote
0answers
114 views

Zabbix on Postgresql - very slow deletion of event

Were using Zabbix for monitoring and I'm having problems understanding why deleting events is so slow. Zabbix: 4.2 (never mind the name of the db - it is 4.2) Postgresql: 10.8 nvps: ~400 hosts: ~600 ...
0
votes
1answer
39 views

Indexes for combined time and location data

I have a multi-TB table with geolocation data for a certain item id and timestamp. It is partitioned by time with a partition for every month (~500GB), and clustered (including the partitions) on the ...
2
votes
1answer
222 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, -- ...