PostgreSQL performance optimization. Essential to include sufficient information in your question as outlined in the full tag wiki.
2
votes
3answers
94 views
Execution Performance Issue with Similar Query
I have been having some performance issues running a simple query on PostgreSQL on a table that has about 3 million rows with a join on a table that has about 120 rows.
If I run a query only on the ...
0
votes
1answer
50 views
I have a query on two big tables that is too slow!
I have a simple query:
SELECT t1."TimeS" as TimeS, t1."JobID" as JobID, t1."TaskIndex" as TaskIndex, t1."MachineID" as MachineID ,t2."EventType" as EventType, t1."UserName" as UserName, t1."CpuReq"...
-1
votes
1answer
41 views
Why parallel seq scan is not chosen by planner for jsonb type of fields? [on hold]
I have a table with jsonb type of column (filter_data) and I set
set force_parallel_mode to true
set max_parallel_workers_per_gather to 8
Then I execute
explain analyze
select
stream_packet_id
from ...
0
votes
0answers
17 views
Postgres: SP have unstable response time [closed]
Iv'e some SP, pretty much simple.
the SP querying a table and view with amount of 11K objects.
the results set of the query is just few rows.
Now, the issue is execution time, sometime the SP ...
3
votes
0answers
39 views
min()/max() on multi-column timestamp index
I'm finding it difficult to understand why there's a bunch of heap fetches going on in this query. To my understanding, when there's no nulls (at either end) in the index, reverse searching the index ...
1
vote
3answers
66 views
How to store short stories for access to individual sentences?
I am building a database for the first time ever (using PostgreSQL), and am very conflicted over the most efficient/logical way of storing a body of text (aka, a story). The conflict stems from the ...
3
votes
2answers
35 views
Postgresql constraints SARG
Are SQL Server style SARGs relevant to PostgreSQL queries in general and more specifically on table constraints?
I have a parent table with many inherited children, partitioned by a date column.
...
3
votes
3answers
45 views
Is there a faster way to count JSONB tags?
I'm trying to squeeze some more performance out of this query in Postgres 9.5. I'm running it over 400,000 rows.
In playing around with it, I've noticed that the CASE statements are adding quite a ...
0
votes
0answers
33 views
Postgres join table with groupby result is done by sequence scan on the table and not index scan
I have large events table i select subset of data (200k lines) and group it by point - > the result is 600 lines.
i want to join the result with location table (2g lines ).
Postgres do sequence scan ...
1
vote
0answers
59 views
Slow query caused by nested loop on simple join?
I've been struggling with the performance on a query. Trying to do a lookup of a large set of ids in a large table (500GB). I create a temporary table of the ids with the ids as a primary key and run ...
2
votes
2answers
64 views
postgres query performance: view vs function
I have a scenario where I need to run a payroll report. The report calculates the payroll amount, grouped by staff member, for a specific date range.
For example, when running the report for 2016-11-...
1
vote
0answers
32 views
Centos 6 / Postgres 9.3 periodic “pauses”
Multiple times a day we experience "pauses" on our Postgres server of about 10-20 seconds where kernel CPU usage is high and everything else grinds to a halt. At the Postgres level this results in ...
1
vote
1answer
76 views
significant performance issue with postgresql 9.5 [closed]
The issue is a significant performance issue after an upgrade to Postgresql 9.5 Fedora Core 24. The levels before the upgrade was Fedora Core 23 and Postgresql 9.4. Prior to the upgrade a query took a ...
3
votes
0answers
26 views
Postgres query changes and degrades radically
I have a hibernate-based query that is not performing well. It is running a query that joins three tables - asset, asset_type, and asset_status - and sorts the results, and running the query on a ...
0
votes
1answer
54 views
Postgres: Database architecture and materialized views
I have a database with a huge table, that gathers the ranking history of mobile applications. The table is quite big, around 120 Go.
In order for my DB queries not to be too slow, I implemented ...
1
vote
0answers
12 views
pg_stat_statements invalid numbers
I observed two things that I cannot really explain.
the number of queries in pg_stat_statements are sometimes less, sometimes more than it was last time(930, 925 and then 931 within 10 seconds). ...
0
votes
0answers
108 views
How to tune Postgres to take advantage of 256GB RAM hardware
I am trying to configure postgres (version 9.5) to take advantage of very large memory environment. Server configuration has 256GB RAM, 12 cores and 2 SSDs on RAID0 and runs Ubuntu. Swap is set at 4GB....
0
votes
0answers
35 views
How to get the maximum amount of memory (RAM) consumed by a single query?
I know this topic is pretty complex and involves a lot of different factors. Let's say I have several similar queries running at the same time. These queries involves only read operation and several ...
6
votes
2answers
160 views
Filter on array text[] and sort on timestamp
Description
PostgreSQL 9.6 on Linux, size of tags_tmp table ~ 30 GB (10 million rows), tags is a text[] and has only 6 values.
tags_tmp(id int, tags text[], maker_date timestamp, value text)
id ...
3
votes
1answer
98 views
How can I improve my query performance?
I'm building a statistic application based on Django & PostgreSQL with focus on performance and maintainability.
The problem is, that data is distributed among multiple relatively large tables.
...
1
vote
1answer
59 views
Using pg_prewarm to load X number of latest rows into cache
We have a large query that is extra slow when customers "run it the first time, early in the morning..."
So, I found pg_prewarm that I would like to use load into PG's buffer cache
a certain amount ...
3
votes
1answer
100 views
Speed up count queries on a couple million rows
Assuming a db full of products. A product can belong to exactly 1 collection and is created by a user. Rough scale of the db:
Products: 52.000.000
Collections: 9.000.000
Users: roughly 9.000.000 as ...
3
votes
1answer
111 views
Index usage on a temporary table
I have two rather simple queries. The first query
UPDATE mp_physical SET periodic_number = '' WHERE periodic_number is NULL;
and it's plan
duration: 0.125 ms plan:
Query Text: UPDATE ...
1
vote
1answer
24 views
Count number of writes to different tables
I have recently setup Continuous Archiving for Point-in-Time Recovery in a postgresql DB, but the archive is growing at an unexpectedly high pace (tens of gigabytes per day). The DB is a couple of GB, ...
0
votes
0answers
35 views
Simple join unusably slow on RDS Postgres
I just setup a new AWS RDS postgres server and queries are painfully slow (> 10 hours) such that most of the time I just end up killing the query. I have done some indexing that cut the EXPLAIN cost ...
0
votes
0answers
84 views
Advice for very large tables
I have a machine with a VM running postgresql 9.5.
I use postgresql for datawarehouse jobs (OLAP) , and it contains very large tables (like 400 millions of rows).
I'm noticing a terrible performance ...
0
votes
0answers
23 views
Grouping by arbitrary permutations of users
We're trying to generate statistics on how our employees perform across various metrics. One set of queries that we're struggling with the performance of looks a bit like:
We have a shifts table ...
3
votes
1answer
42 views
How to deal with seldom used big database and postgresql?
I loaded in PostgreSQL (9.3) OpenStreetMap data for whole europe (400gb). Then I installed a geocoding api (nominatim) that queries the database with gis queries.
My problem is :
This database is ...
4
votes
1answer
224 views
Improve UPDATE performance on big table
I'm using Postgres 9.5 on Amazon RDS (2vCPU, 8 GB RAM).
I use pganalyze to monitor my performance.
I have around 200K records in the database.
In my Dashboard I see the following queries are taking ...
1
vote
0answers
30 views
Amazon RDS Postgresql 9.5 CPU increase
I run a task in my Python application of ~100K jobs which takes around 5 hours, since the beginning my in RDS instance CPU starts growing...
Instance type: Postgresql 9.5 | db.t2.large
I have the ...
1
vote
1answer
97 views
Query with merge join horrendously slow
I'm using a Postgres database and trying to optimize the following query:
SELECT DISTINCT catalogite1_.id
FROM cat_catalogitem catalogite1_
INNER JOIN cat_service service2_
...
1
vote
2answers
33 views
Best way to delete large set of rows knowing field to NOT delete
Coming from a Rails background, I have a large production database that I have a copy of where I only need records from 4 of the 2000+ companies. I'm trying to delete all the rows except the ones ...
0
votes
1answer
27 views
Extract all rows until and including
I would like to extract all the rows from a table until and including check_type = 'schedule' and checked_at < '2016-09-26', in this situation I don't have a lower bound for checked_at, the lower ...
3
votes
1answer
53 views
PostgreSQL - Datetime ranges overlap
I have a table with datetime fields start and end. And I have a list of (start, end) items. I need to check which items from the list overlap with data in the table.
The current query looks like this:
...
4
votes
1answer
230 views
Which queries are faster with Postgres than with MySQL InnoDB [closed]
I've read Performance difference between MySQL and PostgreSQL for the same schema/queries. Here is a brief retelling of the article:
PostgreSQL tables are heap tables (means no clustered index)... ...
3
votes
1answer
109 views
Postgres heap table performance on SSD
I've read Performance difference between MySQL and PostgreSQL for the same schema/queries.. Is it still relevant with appearance of Solid State Drive?
That article said that Postgres is better for ...
0
votes
1answer
24 views
PostgreSQL: Multiple materializations in same query plan?
I have the following query:
Query:
SELECT
a1.id id,
((SELECT SUM(SFF.pnl)
FROM positions SFF
JOIN ...
0
votes
1answer
94 views
Make PostgreSQL perform SELECT queries in memory
I just installed PostgrSQL and realised queries are most times hitting hard disk, which drastically slows down the system.
I would to move them to memory.
In MySQL this is achieved by creating a ...
2
votes
0answers
38 views
Why am I getting drastically different performance results between staging vs. prod for a table?
I have two amazon rds postgres instances, one is staging, one is production...
In staging, a table has 21 million rows, and in production that same table has 29 million rows.
If I do select count (*)...
1
vote
1answer
44 views
How expensive is a connection to pgbouncer?
Is a connection to pgbouncer cheaper than a connection to PostgreSQL (assuming pgbouncer has already established a connection to PostgreSQL) ? How is the connection to pgbouncer made ? I found the ...
0
votes
1answer
40 views
Optimizing a Query
I have to run this query, on Postgres 9.4.8 and it's insanely slow, likely because the page_views table contains about 200mil records.
Here's the query:
EXPLAIN ANALYZE
SELECT COUNT(*) AS ...
1
vote
3answers
101 views
How to speed up string cleanup function?
I need to cleanup a string, so that certain ASCII code characters are left out of the string, and others are replaced.
I am new to Postgres. My function ufn_cie_easy() performs way too slow:
DECLARE
...
0
votes
0answers
183 views
Recommended maximum memory setting for PostgreSQL 9.4+ with Huge Pages
Since PostgreSQL 9.4+ supports Linux Huge Pages, I have configured a Ubuntu 16.04 server with 16GB of RAM and 8 CPU cores to use Huge Pages with a dedicated PostgreSQL 9.5 instance.
The estimate in ...
1
vote
1answer
94 views
Impact of CLUSTER on performance
I'm trying to optimize my Postgres 9.2 database to speed up queries with date restrictions.
I have a timestamp column, but mostly I'm asking for some day, so I have created an index with timestamp to ...
0
votes
0answers
23 views
Understanding PostgreSQL autovacuum cost and duration from logs
I've configured autovacuum with cost-delay based parameters and have entries such as this one in the logs:
Jul 24 03:31:24 srv postgres[23181]: [6-1] 2016-07-24 03:31:24 EDT [23181]: [2-1] user=,db= ...
0
votes
1answer
58 views
Postgres very slow on server
Restoring a single table on the server is very slow
From Fri Jul 22 13:54:48 CEST 2016 to Fri Jul 22 14:17:41 CEST 2016
Server
2016-07-22 13:55:11 CEST [88719-1] tester@test LOG: duration: 22988.122 ...
0
votes
1answer
94 views
PostgreSQL indexing on bit string
What is the best way to create an index on a bit string column? Let's say I had the column of type bit(4) and I wanted to search for all entries that had had a specific bit set. So if I had the ...
0
votes
2answers
54 views
Slow UPDATE FROM Query in Large Table
I'm currently trying to merge two very large (11 million rows) tables, and my query has been running for over two days with no end in sight.
My basic query is:
UPDATE us_demand
SET ...
FROM ...
0
votes
1answer
81 views
Possible to use first CTE as filter for second CTE?
I'm trying to optimize a query on a large database that essentially fetches 50 images for a site and the URL's for the pages they appear on. The current query works okay for smaller sites but larger ...
1
vote
1answer
51 views
Postgres permission for EXPLAIN
I would like to give user a permission to perform EXPLAIN but not to execute the SELECT itself. Is this doable in PostgreSQL?
