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

learn more… | top users | synonyms

0
votes
0answers
3 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 ...
0
votes
0answers
11 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
29 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
37 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
44 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
37 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
22 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
77 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
54 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
68 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 ...
0
votes
1answer
73 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
106 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
60 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
46 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
73 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
216 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
150 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
39 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
79 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
39 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
49 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
46 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 ...
7
votes
2answers
197 views

Looking for a simpler alternative to a recursive query

The actual query is more involved, but the problem I'm facing can be distilled to this: A query to filter a rowset of monotonically increasing integers so that - in the final result set, row(n+1)....
1
vote
1answer
191 views

Postgres sharding installation with citusdb

We are developing a prototype for a BIG data product. We have almost 2 billion records. We have used PostgresSQL 9.5 as a back-end and Python as front-end. We are using a 16*2.4 GHz processors with ...
2
votes
2answers
99 views

Eliminate duplicates and optimize performance for big count and small LIMIT

I have a big query in my Postgres 9.1 database: SELECT *, count(*) OVER () AS full_count FROM ( SELECT DISTINCT ON(message.messagetime,message.messageid) message.messagetime, ...
0
votes
0answers
77 views

Seq Scan faster than Index Only Scan

How can it happen, that a seq scan is faster than an index only scan, even no heap fetches are needed? I have a table with 100 million rows, selecting all of them with a time range query. ...
1
vote
1answer
19 views

Is there a limit to the number of database connections from a single machine in PostgreSQL?

We are planning on setting up multiple front ends behind a load balancer. I think our database can handle the load because I'm currently measuring a database TPS of about 450 for approximately ...
2
votes
2answers
56 views

Index for OR operator: a=x or b=x

I have table with three integer columns id, a and b. I want to get all records where a or b match specified parameter ordered by id: select id, a, b from t where a=x or b=x order by id Note that x ...
1
vote
0answers
29 views

Postgres query optimization: why could two very similar join tables have vastly different querying times?

I'm struggling with optimizing the performance of a single query, which is sometimes taking up to 180s for certain tenants. When I re-run this later in pgadmin on the production database server (using ...
0
votes
0answers
46 views

Moving slow sub-query to With clause

I am trying to optimize this query : SELECT sum(a-alias.s_count) AS count FROM a-table a-alias WHERE ( a-alias.created_at_minute_id >= (floor(extract(epoch from( select ...
3
votes
1answer
72 views

SELECT very slow when no results and a LIMIT is specified

I am running into an issue where a SELECT query is very slow because it does not use an index when the number of final results is 0 and a LIMIT clause is specified. If the number of results is ...
5
votes
1answer
96 views

Sub-select takes “ages” - EXCEPT much faster

Script to create the tables DROP TABLE IF EXISTS history; CREATE TABLE history ( id integer NOT NULL, ticket_id integer NOT NULL); ALTER TABLE ONLY history ADD CONSTRAINT history_pkey PRIMARY ...
1
vote
0answers
18 views

Very different query plans with and without LIMIT in PostgreSQL.

I have the following query which I am using to create vector tiles for a web map. I'm pulling together just the geometries within the selected tile using a CTE, then joining it to get additional ...
2
votes
1answer
67 views

Why is index not used for IN expression with unnested array

I have a query that joins two tables but refuses to use use index unless I add a limit. The case is a bit complicated so best described by showing you. I have two tables, users and ...
0
votes
0answers
22 views

Performance approach to PostgreSQL queries sequencing worth it?

So I have a job that processes tables for each tenant in our application Loop tenant_1 to tenant_100 fork own process process table A process table B end end so what happens is, there is 100 ...
2
votes
1answer
123 views

How to debug when OS kills postgres for high memory usage

I am running Postgres 9.4 server in a VPS running Ubuntu. It gets killed often (multiple times in a day). This is the message from dmesg Out of memory: Kill process 1020 (postgres) score 64 or ...
6
votes
2answers
130 views

Exists subselect vs inner join?

I'm moving up to the next level of my mystery query. It looks like there's a subselect inside of an exists, but on the same table. I think this could probably be simplified with an INNER JOIN higher ...
1
vote
1answer
37 views

How to optimize view with group by

Have view with query : SELECT min(lbl.id) AS id, lb.order_id, lb.par_id, lbl.lb_id, lb.categ_id, lbl.mt_id, lbl.uom_id, lbl.tracking_id, sum(lbl.orig_qty) AS orig_qty, max(lbl.qty) AS qty, sum(lbl....
3
votes
1answer
148 views

Speeding up a GROUP BY, HAVING COUNT query

I'm trying to speed up this query in Postgres 9.4: SELECT "groupingsFrameHash", COUNT(*) AS nb FROM "public"."zrac_c1e350bb-a7fc-4f6b-9f49-92dfd1873876" GROUP BY "groupingsFrameHash" HAVING COUNT(*) &...
0
votes
1answer
86 views

Query bytea column using prefix

Assuming I have a table with a bytea column called data on a table called t, how can I: Write a query that returns all rows which have a specific prefix on their data column. Index the query. I am ...
3
votes
1answer
95 views

How to read postgres.log output?

I need to improve my database performance, in order to achieve that, I enabled the log in my postgresql.conf: log_statement = 'all' log_connections = on log_disconnections = on Why do I have 3 ...
1
vote
1answer
38 views

How to influence the planner on Postgresql

I have the following: a complex query, 'match', that returns about 200 rows (ids) a huge table, 'usernames', indexed on id that maps them to usernames When I try to left join the query (1) with ...
1
vote
2answers
186 views

For a standby workload of long running analytical queries, is it better to turn on hot_standby_feedback or set max_standby_*_delay settings to -1?

When spinning up a hot standby server specifically for BI/Analytics purposes where long running queries may be common, is it better to turn on hot_standby_feedback or set the max_standby_*_delay ...
14
votes
1answer
456 views

Is it worth it to run VACUUM on a table that only receives INSERTs?

In a 2015 re:Invent talk, AWS mentioned that vacuum should be run not only after updates or deletes but also after inserts. Here is the relevant part of the talk: http://www.youtube.com/watch?v=...
0
votes
1answer
65 views

TEMP table alternatives for pl/pgsql

I am trying to emulate MERGE behavior with pl/pgsql: -- Generate the data from funtion CREATE TEMP TABLE temp_x (id int, id2 int, data text, created_at timestamp, updated_at timestamp) ON COMMIT ...
0
votes
2answers
91 views

Measure CPU and Memory usage while running pgbench

what is the best way to capture the usage of CPU and memory while running pgbench? The only way I can think of is to read the info from top header. what I do now I run top command by print it out ...
0
votes
0answers
69 views

How does the PostgreSQL query planner estimate the aggregated rows count?

During an SQL optimization session I ran into the following problem (I won't post the full query and its plan as they are quite large): The plan: ->__SOME_OTHER_NODES__ -> ...
3
votes
1answer
113 views

Using ILIKE with unaccent and with only right end wildcard

I use Postgresql 9.4 and I have a big table named foo. I want to search on it but I get long execution times if the search text is very short (e.g. "v") or long (e.g. "This is a search example with ...
4
votes
1answer
115 views

Debug query on big table that is sometimes slow

I have a web API that is backed by a Postgres database, and the performance is generally very good. I monitor the performance of both the database and the application as a whole. Most of my queries (...