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

learn more… | top users | synonyms

1
vote
0answers
12 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
56 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
33 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
34 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
37 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, ...
1
vote
1answer
23 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
173 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, ...
1
vote
1answer
117 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
94 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
70 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
12 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
54 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
25 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
42 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
56 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
88 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
63 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
65 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
125 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
35 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
116 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
40 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
71 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
34 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
96 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
397 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
41 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
54 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
51 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
80 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
86 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
88 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
42 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
26 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
51 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
95 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
127 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
101 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
140 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
77 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
53 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
32 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
347 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
33 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
74 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 ...