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.
22
votes
6answers
23k 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 ...
0
votes
2answers
65 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
598 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). ...
0
votes
1answer
71 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 ...
2
votes
1answer
89 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 ...
2
votes
1answer
199 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
794 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 ...
1
vote
3answers
81 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
63 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
2k 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 ...
1
vote
1answer
138 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
...
