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
2answers
18 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 ...
3
votes
1answer
52 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
48 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
24 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
21 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
41 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
45 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
40 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
64 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
15 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
66 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
107 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
60 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
16 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
45 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
19 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
333 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
26 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
36 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
91 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
67 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 ...
5
votes
1answer
96 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
90 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
44 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 ...
4
votes
1answer
41 views

Fastest way to find (rare) rows with a null value among millions of rows?

If I have an addresses table with ~ 4 million rows in a Postgres database. Is there a way to optimize the following query so that I can find the few rows where address_id is NULL? Sometimes I need to ...
-1
votes
2answers
91 views

How to delete millions of records from table

I am using postgree 9.4 database and I have a table snapshots with 700+ million rows and I want to delete about 9 million rows. I have tried different queries to delete records, but no success. Query ...
4
votes
2answers
53 views

Can we execute an optimal plan instead of a generic one in the first execution of a PL/pgSQL function?

I have a really busy function that I need to optimize the best way I can. This function is just a nested select statement that is requested several times a sec by a legacy application. The indexes ...
1
vote
1answer
42 views

Postgres 'could not write to hash-join temporary file: No space left on device'

I was getting this error a while ago and I was curious about two things - 1- How do the internals of postgres work that cause this message to occur in the first place? What is the hash-join in ...
1
vote
0answers
69 views

Improve lookup of 1.5 million rows

I have this table with data but we need to get all the (unique) rdx and rdy values: CREATE TABLE public.positions ( datetime timestamp without time zone NOT NULL, unitid bigint NOT NULL, rdx ...
2
votes
1answer
60 views

Using an index on a Postgresql integer range causing trouble

Unfortunately I was not able to create a usable example / fiddle. But I am using a Postgresql 9.4 table that looks somewhat like this: CREATE TABLE table1 (id INT PRIMARY KEY NOT NULL, dtype TEXT, ...
3
votes
1answer
122 views

Bad performance with select on view if check for 'is null'

I have two quite simple tables, and a view to join them on bigint fields: CREATE TABLE foo.ao ( id serial NOT NULL, ao_id bigint NOT NULL, ao_plz text, ao_community text, ao_street text, ...
0
votes
0answers
68 views

Alternative to CASE

What alternatives are there to replace the use of CASE, and will also improve the speed of the query. SELECT created_at, COUNT(CASE WHEN cp > 0 THEN 1 END)::int4 rk, COUNT((CASE WHEN ...
2
votes
2answers
60 views

Performance issue during count Postresql 9.4 - Creating index on pivot table key…?

Disclaimer: I am new to Postgres, I need some advice... I have a huge table, with full of poker hands.(will contain 20M records, right now 2M records in it) aggregated_hands I have a pivot table, ...
1
vote
1answer
48 views

Join external data in SELECT query

I have few (~20) names of products corresponding to an integer id that needs to be joined on products while exporting products data (using COPY (<query>) TO). The burden is that these names do ...
1
vote
0answers
24 views

PostgreSQL: labels vs columns

I have a data structure similar to this: date foo bar baz boo ---------------------------------------- 2015-11-18 1 null 2.5 81.0 2015-11-19 1 78.4 12.8 ...
0
votes
1answer
70 views

Improve performance of query with many joins

From the following explain plan: explain SELECT imei, cliente_avl_equipo.id_cliente AS idcliente, cliente_avl_vehiculo.grupo AS grupo, avl_vehiculo.id, ...
0
votes
0answers
26 views

Sudden drop in update rate on a postgres table

I have a table something like: CREATE TABLE stock{ id serial NOT NULL, (primary key constraint) date date, stock_id integer, (indexed) firm character varying(255), ...
2
votes
0answers
36 views

Adding partial unique index very slow

I attempted to create a partial unique index for a table like example 11-3 here: CREATE TABLE tests ( subject text, target text, success boolean, ... ); CREATE UNIQUE INDEX ...
1
vote
1answer
23 views

enabling fsync in postgresql 9.1

currently fsync option is disabled in postgresql.conf. If I am enable that, From: #fsync = on To: fsync = on (I am enabling fsync to avoid data corruption if any failure since my data ...
2
votes
2answers
82 views

Merge two huge tables keeping only the unique rows

I have two tables: table1 with 61 million rows and table2 with 59 millions rows. The columns in both are the same (name and type). Both are imported from backup files. I want to merge these two ...
0
votes
0answers
19 views

index being used during equality operator but not comparison operator [duplicate]

Using a btree as my index. From what I understand(http://www.postgresql.org/docs/9.0/static/indexes-types.html), btree works for comparison operators. For equality operator it works fine explain ...
4
votes
1answer
66 views

DISTINCT ON vs ROW_NUMBER() for getting a row with a maximum value of a group

I have the following table in PostgreSQL 9.4: CREATE TABLE dpa( id serial NOT NULL, currency_id integer, amount numeric(14,3), date timestamp without time zone, plat_id integer, pl_id ...
5
votes
1answer
72 views

Fastest query for distinct IDs in a many-to-many relationship

I have this table in PostgreSQL 9.4: CREATE TABLE user_operations( id SERIAL PRIMARY KEY, operation_id integer, user_id integer ) The table consists of ~1000-2000 different ...
1
vote
1answer
42 views

Scaling `ntile(N)` over a set where M < N

Env: Postgres 9.4.4 on Mac OS X Env: Postgres 9.3.6 on Heroku I have a "large" dataset (75k) containing multiple groups (30), and for each group I am generating box plot percentiles. In most ...
3
votes
4answers
257 views

Does updating a row with the same value actually update the row?

I have a performance-related question. Let's say I have a user with first name Michael. Take the following query: UPDATE users SET first_name = 'Michael' WHERE users.id = 123 Will the query ...
2
votes
2answers
187 views

Very slow query in PostgreSQL on 10kk records table

I'm having very slow requests to my queries. I created an index on the filter columns and made partitioning but requests is still too slow. How can I make it faster? Here's query and explain: SELECT ...
2
votes
0answers
39 views

Postgresql delete taking lots of CPU and time

I have the following SQL Query for deleting rows from my table. The query takes a long time and puts a high load on my server. delete from message_log where from_id = ? OR to_id = ? My table ...
3
votes
1answer
123 views

Faster query with pattern-matching on multiple text fields

I have one Postgres table with more than 20M tuples: first_name | last_name | email ------------------------------------------- bat | man | batman@wayne.com arya | vidal | ...
1
vote
0answers
20 views

Sharding in PostgreSQL [closed]

I was looking into sharding techniques for PostgreSQL. All that I could find was partitioning and pg_shard. Is there anything else that can help? What are the best practises?