PostgreSQL performance optimization. Essential to include sufficient information in your question as outlined in the full tag wiki.
0
votes
0answers
19 views
upgrading postgresql version to 9.5 leads to high cpu utilization [closed]
Upgrading to Postgres version 9.5 has lead to high CPU utilization.
Is there any specific reason which is causing the high CPU utilization?
6
votes
3answers
102 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
90 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
33 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
70 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 ...
0
votes
0answers
31 views
Postgres and huge tables, help with optimization
I want to create a view using multiple tables, but can figure out how can I optimize my query. It takes too long to load the data:
I use these tables:
products (700k records)
id | caption | price | ...
3
votes
1answer
41 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
19 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
31 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
51 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
21 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
40 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
132 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
21 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
62 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
30 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
26 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
40 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
143 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
76 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
65 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
28 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
79 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
87 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
60 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
16 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
54 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
65 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
53 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
55 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
40 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?
3
votes
1answer
95 views
Optimise a LATERAL JOIN query on a big table
I'm using Postgres 9.5. I have a table that records page hits from several web sites. This table contains about 32 million rows spanning from Jan 1, 2016 to June 30, 2016.
CREATE TABLE event_pg (
...
1
vote
1answer
112 views
SELECT count(*) from a materialized view with unique index is slow
I have a materialized view with a unique index (on the id column)
and both of these queries are slow (they scan the whole table):
SELECT count(DISTINCT a.id) from the_view a;
SELECT count(*) from ...
1
vote
3answers
81 views
How to optimize a join based on a “like” in postgres
Using a pg_trgm index, I have the following query that runs in aprox 20 to 80 milliseconds :
SELECT count(*) from protein_seq p
WHERE p.sequence LIKE '%SMYSFCKASFTQHNFYFFKN%';
the protein_seq table ...
0
votes
1answer
88 views
intel p3600 SSD no increase in read speed - postgres rdbms vs hdd
We run a custom application that produces analyzable data-sets for medical research.
the amount of data we have is about 600GB in form 3Billion rows distributed in 600+ tables it is hosted in ...
0
votes
2answers
641 views
Increasing Postgresql 9.5 max_connections to at least 300
Right now I am using the default postgresql.conf which has maximum connections set to 100. I need to increase max_connections to 300. Postgresql 9.5 is the only thing running on my Linode.
I am ...
3
votes
0answers
150 views
Slow Postgresql query with left join
I have a query on Postgres, and I also have added proper index. Is there anything missing here?
SELECT orders.*, demo.name as d_name
FROM orders
LEFT JOIN users as demo ON demo.id = orders.dr_id
...
0
votes
0answers
49 views
Postgres query taking one minute to fetch one row
I am using Postgres version 9.4 in my application.
I have a partitioned table named sql_sent_sms, which is partitioned on the basis of primary keys.
I have a query which uses join on same table with ...
1
vote
2answers
87 views
Trigram index for for ILIKE patterns not working as expected
I've got a simple but slow query:
SELECT DISTINCT title
FROM ja_jobs
WHERE title ILIKE '%RYAN WER%'
AND clientid = 31239
AND time_job > 1457826264
ORDER BY title
LIMIT 10;
Explain analyze:
...
4
votes
2answers
255 views
Optimize query to find top N users who commented on a post
Problem
Trying to find the most efficient query to retrieve the top N (5 in the examples) users who have commented on a post, where a user is considered 'top' if they have the most followers. The ...
5
votes
1answer
640 views
Indexes: integer vs string performance if the number of nodes in the index is the same
I hope this is the right place to ask!
I am developing an application in Ruby on Rails with the PostgreSQL (9.4) database. For my use case, columns in tables will be looked up very frequently, as the ...
1
vote
0answers
105 views
Profiling postgres with gprof: no data/gprof folder and 'no time accumulated'
I'm attempting to profile Postgres with gprof. A gmon.out file successfully gets created in both the data directory and in the working directory where I launched the client (pgbench) process from.
...
2
votes
2answers
114 views
Filtering UNION ALL result is much slower than filtering each subquery
(edit: see end for a simpler example)
I'm searching in a table named "cases" (135k rows, 29 columns). Some of the rows in this table have a type of parent-child relationship (of different types), ...
1
vote
0answers
46 views
Postgresql with just index storage
I have a PostgreSQL table with a key (bigint) and a value (double). The table has tens of billions of rows. I have a single btree on the (key,value) for aid lookups by key. The table is never updated.
...
1
vote
1answer
88 views
Most efficient way to retrieve data by timestamps
I'm using PostgreSQL 9.2.8.
I have table like:
CREATE TABLE foo
(
foo_date timestamp without time zone NOT NULL,
-- other columns, constraints
)
This table contains about 4.000.000 rows. ...
1
vote
1answer
38 views
Slow Query Postgres 9.2
I've got the following query:
SELECT
j.id,
concat(c.company, ' ', c.name_first, ' ', c.name_last) AS client,
c.email AS client_email,
concat(bc....
1
vote
1answer
100 views
PostgreSQL streaming replication stops updating
I have four PostgreSQL 9.5 instances running on EC2 using m4.8xlarge instances with five PIOPS SSD in a RAID0 setup, and a separate XLOG drive. Until this morning, I never had more than a minute or ...
