PostgreSQL performance optimization. Essential to include sufficient information in your question as outlined in the full tag wiki.
0
votes
0answers
7 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 ...
1
vote
1answer
85 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
92 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
67 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
11 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
52 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
23 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
41 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
54 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
86 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
15 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
61 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
21 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
58 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
121 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
34 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, ...
3
votes
1answer
102 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
31 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
63 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
33 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
80 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
383 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:
...
0
votes
1answer
38 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
47 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
47 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
72 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
79 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
77 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
39 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
24 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
50 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
50 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
79 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 ...
2
votes
1answer
108 views
PostgreSQL 9.4 query seems very slow
I'm experiencing a very long response time for a query returning a relatively small number of rows. It takes over three minutes to return ~1.3 million rows. I would assume it was an indexing problem ...
0
votes
0answers
16 views
PostgreSQL 9.4 CTE to count associated records using 2 columns
I need to count child records and cache the count in the parent record using a PostgreSQL 9.4 CTE. I have an Audit table and an Order table. The Audit table will keep a count of how many Orders ...
2
votes
2answers
91 views
Retrieving changed rows only from a table with 10^7 rows
I have a table with about 300 columns and about 107 rows and I have to retrieve changed rows only, ordered by time (not distinct, but changed). The result set may be limited to 100 rows as well.
I've ...
4
votes
1answer
133 views
PostgreSQL seems to create inefficient plans in simple conditional joins
Consider these two queries:
SELECT
t1.id, *
FROM
t1
INNER JOIN
t2 ON t1.id = t2.id
where t1.id > -9223372036513411363;
And:
SELECT
t1.id, *
FROM
t1
INNER JOIN
t2 ON ...
2
votes
1answer
75 views
PostgreSQL not using index during count(*)
I have a COUNT(*) query in PostgreSQL that runs often, and looks like:
SELECT COUNT(*)
FROM customer
WHERE source_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16);
This query ...
1
vote
0answers
22 views
PostgreSQL: Improve system performance, KNN modification with GiST Index
In my PostgreSQL 9.5beta2, Postgis 2.2.0 compiled by Visual C++ build 1800, 64-bit I am running a Nearest-Neighbor-Query with the additional complication of unique assignment. I am running this on ...
1
vote
0answers
51 views
PostgreSQL doesn't use index on inherited tables
I have two tables in PostgreSQL 9.4.5 deployed on Amazon RDS:
an append only, weekly partitioned, All Positions table. These store all position readings from about 100,000 sensors. There are about ...
0
votes
0answers
30 views
How to calculate percentage of multiple values of columns in PostgreSQL?
I've the table named reviews which shows the hotels' services ratings given by guests who stayed at respective hotel as shown below
dtime prop clean staff location amenety travel_type
...
1
vote
2answers
345 views
Complex view becomes slow when adding ORDER BY in outer query with small LIMIT [closed]
I have a pretty large query in a view (let's call it a_sql), that is really fast unless I use ORDER BY in an outer SELECT with a small LIMIT:
SELECT
customs.id AS custom_id, customs.custom_name AS ...
2
votes
1answer
28 views
OFFSET and LIMIT on complex query
I am doing a pretty complex query in Postgres 9.3.4:
SELECT p.*
FROM unnest('{19082, 19075, 20705, 18328, 19110, 24965, 18329, 27600
, 17804, 20717, 27598, 27599}'::int[]) ...
0
votes
0answers
65 views
How to get CPU and memory usage of PostgreSQL server and queries from database system views?
We are looking for the way to get CPU and memory usage data of PostgreSQL 9.x for the monitoring purposes. The requirement is to provide the data on server, database, session and query level. We are ...
2
votes
2answers
192 views
Filesystem tuning for PostgreSQL
I want to install PostgreSQL 9.x version in an Ubuntu 14.04 workstation and I'm looking for a recommendation on how to set it up in order to
get a good read-write (50/50-balance) performance
keep a ...
4
votes
1answer
73 views
Improving sort performance in GROUP BY clause
I have two tables in Postgres 9.4.1 events and event_refs with the following schemas:
events table
CREATE TABLE events (
id serial NOT NULL PRIMARY KEY,
event_type text NOT NULL,
event_path ...
4
votes
1answer
112 views
Index not being used in SELECT query
I have a table of about 3.25M rows with the follow format in Postgres 9.4.1
CREATE TABLE stats
(
id serial NOT NULL,
type character varying(255) NOT NULL,
"references" jsonb NOT NULL,
...
4
votes
2answers
100 views
Optimize a query with small LIMIT, predicate on one column and order by another
I'm using Postgres 9.3.4 and I have 4 queries that have very similar inputs but have vastly different response times:
Query #1
EXPLAIN ANALYZE SELECT posts.* FROM posts
WHERE posts.source_id IN ...
0
votes
1answer
56 views
Postgres datetime sort slow and not using index
Using Postgres 9.4, I'm trying to order a query by an indexed datetime column.
explain
SELECT g.*, array_agg(gp.platform_id) as platform_list,
array_agg(gm.metacritic_id) as ...
3
votes
1answer
53 views
Indices for a join query with datetime range
Given the following query:
SELECT DISTINCT COUNT(DISTINCT "patients"."id")
FROM "patients"
INNER JOIN "transactions" ON "transactions"."patient_data_id" = "patients"."patient_data_id"
WHERE ...
