About PostgreSQL performance optimization. It is essential to include sufficient information in your question as outlined in the tag info. For simple cases and beginners questions, please ask on http://stackoverflow.com/ instead.
28
votes
6answers
42k views
How do I efficiently get “the most recent corresponding row”?
I have a query pattern that must be very common, but I don't know how to write an efficient query for it. I want to look up the rows of a table that correspond to "the most recent date not after" the ...
12
votes
1answer
7k views
Index optimization with dates
I have a large table of objects (15M+ row) in PostgreSQL 9.0.8, for which I want to query for outdated field.
I want to divide the query by millions, for scalability & concurrency purposes, and I ...
3
votes
1answer
294 views
Unused index in range of dates query
I have a query that is not using existing indices and I do not understand why.
The table:
mustang=# \d+ bss.amplifier_saturation
Table ...
4
votes
2answers
136 views
Postgres 9.4.4 query takes forever
We're running Postgres 9.4.4 on CentOS 6.5 and have a SELECT query that has worked for years, but stopped working and hangs after we upgraded from 9.2 (it took a while to notice it, so I don't know if ...
2
votes
2answers
205 views
Optimizing a Postgres query with a large IN
This query gets a list of posts created by people you follow. You can follow an unlimited number of people, but most people follow < 1000 others.
With this style of query, the obvious optimization ...
2
votes
1answer
833 views
Use GIN to index bit strings
I'm trying to extend PostgreSQL to index bit strings up to 1000 bits. (These bit strings are created by quantization of high-dimensional vectors, so for each dimension up to 4 bits are assigned). ...
3
votes
1answer
53 views
Why does it take so long for Postgres to return a sequence number?
I have an application that does bulk loads into a large table (100 million rows). I am using Postgres' COPY FROM functionality to load data from a flat file. The target table has a primary key of id.
...
2
votes
1answer
30 views
How to Choose Between VALUES and SELECT for INSERT?
This answer raised the question for me how to choose between VALUES and SELECT in such a function . Using PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit:
...
0
votes
1answer
355 views
Slow Postgres updates if OTHER columns are indexed?
Certain updates take far too long on large Postgres tables. Given these conditions:
only one column is being updated, and it is non-indexed
the column already has data in it for every row due to a ...
7
votes
5answers
6k views
SELECT DISTINCT on multiple columns
Supposing we have a table with four columns (a,b,c,d) of the same data type.
Is it possible to select all distinct values within the data in the columns and return them as a single column or do I ...
4
votes
3answers
88 views
Speed up creation of Postgres partial index
I am trying to create partial indexes for a large (1.2TB), static table in Postgres 9.4.
My data is completely static, so I am able to insert all data, then create all indexes.
In this 1.2TB table, ...
3
votes
1answer
68 views
Performance issues with inherited tables and indices
I have a PostgreSQL database with a master table and 2 child tables.
My master table:
CREATE TABLE test (
id serial PRIMARY KEY,
date timestamp without time zone
);
CREATE INDEX ON ...
3
votes
1answer
150 views
How do completely empty columns in a large table affect performance?
I have 400 million rows in a Postgres db, and the table has 18 columns:
id serial NOT NULL,
a integer,
b integer,
c integer,
d smallint,
e timestamp without time zone,
f smallint,
g timestamp without ...
3
votes
1answer
2k views
log_min_duration_statement setting is ignored
I am running Postgresql 9.1 on Ubuntu. Exact Postgresql version is 9.1+129ubuntu1 as my package manager shows.
I have 2 databases that are actively in use and they are used from a remote server.
I ...
2
votes
2answers
109 views
Why seq-scan can be much faster than index-scan and index-only-scan in this simple query?
I am using PostgreSQL 9.4.4. I have a query like this:
SELECT COUNT(*) FROM A,B WHERE A.a = B.b
a and b are the Primary Keys of tables A and B, so there are B-indexes on a & b
By default, ...
2
votes
1answer
331 views
Postgres Performance Over Group by with MAX and MIN
I just want to retrieve the data for specific location of customers with their ever first and last purchases made in the system.
Table details :
CREATE TABLE customer_location ( id UUID, location_id ...
2
votes
1answer
4k views
Efficient way to insert/update/delete table records from complex query in Postgres 9.x
I have this function which returns a set a records and I need to persist those records into a table. I have to do it hundred times a day.
My initial approach was just clear data from my table and ...
2
votes
1answer
2k views
Very slow simple PostgreSQL query on RDS
I seem to be getting very slow queries on a medium sized RDS box (db.m3.medium, 3.7gb ram).
This is across a table of 4,152,928 rows..
select sum(some_field) c
from pages
where pages.some_id=123
and ...
1
vote
3answers
193 views
Slow query when adding additional where clause
Here's my setup (Postgres 9.3)
Posts
project_id
Messages
post_id
kind
updated_at
I'm trying to get the most recently updated 100 messages that belong to posts (on a specific project) that are ...
1
vote
1answer
106 views
EXECUTE within function not using index?
I have an accounts table with ~200k rows and an index for these columns:
account_type_id BIGINT
, member_id BIGINT
, external_id VARCHAR(64)
CREATE INDEX account_full_qualifiers_idx
ON ...
1
vote
1answer
158 views
Optimizing Postgres query
I've got a one to one relation from users to addresses table.
Where one user can have one search address and one verified address.
I've got two indexes on addresses table :
Index on state field
...
