PostgreSQL performance optimization. Essential to include sufficient information in your question as outlined in the full tag wiki.
1
vote
1answer
18 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
69 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
50 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
67 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
93 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
57 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
45 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
70 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
116 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
34 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
38 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
47 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
44 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
194 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
182 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
97 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
55 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
45 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
70 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
66 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
117 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
36 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
146 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
82 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
92 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
37 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
178 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
441 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
62 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
86 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
68 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
107 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
113 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 (...
4
votes
1answer
130 views
pgAdmin is extremely slow on any remote operation
I run this query from my local pgAdmin connecting remotely to our dev server:
select * from users order by random() limit 1;
it hangs for 17 seconds and shows
Total query runtime: 148 ms.
1 row ...
3
votes
1answer
45 views
Postgres reduce by key if key is not null
I need to select N rows with seq > X which reduce_key is null and for each row group with the same reduce_key select the one with the biggest seq. Result should be strictly sequential (but with ...
0
votes
1answer
28 views
Is it possible to set order in which inherited tables a scanned in postgresql?
I have a postgresql measurement table which is partitioned based on logdate column by month. Suppose I have city_id integer column and want to select latest row with city_id = 125.
When I run explain ...
2
votes
0answers
53 views
Insert performance
I am having trouble with Posgresql insert performance. There are 2 tables, documents and words, where documents is essentially a mapping from an external path to a surrogate id, which is foreign ...
1
vote
1answer
58 views
Why does this PostGIS query take so long to run?
I am new to PostGIS and SQL in general. I am running a query on polygons which, I hope, only selects distinct geometries and then repair (ST_MakeValid()) those distinct geometries. Thereby removing ...
1
vote
1answer
207 views
Postgres 9.5 foreign table inheritance not using indexes
In PostgreSQL 9.5.0 I have a partitioned table that collects data by months. I tried to use the new PostgreSQL feature of foreign table inheritance and pushed one month of data to another PostgreSQL ...
