Linked Questions

38 votes
2 answers
37k views

Multicolumn index and performance

I have a table with a multicolumn index, and I have doubts about the proper sorting of the indexes to get the maximum performance on the queries. The scenario: PostgreSQL 8.4, table with about one ...
user avatar
  • 681
14 votes
1 answer
5k views

Does creating a unique constraint on a Postgres column remove the need to index it?

Does creating a unique constraint on a Postgres column remove the need to index it? I expect that an index is automatically needed to maintain the constraint efficiently.
user avatar
  • 875
9 votes
1 answer
4k views

2 B-tree indices OR 1 GiST index on tsrange -- which will perform better?

I have a table which is storing reservation data using the columns starts_at & ends_at Whenever I'm querying the table to find overlapping reservations, I have an option of using one of the ...
user avatar
5 votes
4 answers
16k views

Finding all date ranges that overlap a target date range

Say I wish to store when staff are on holiday (FromDate, ToDate) and then I wish to find all staff that are on holiday between two given dates (QFromDate, QToDate). Now assume that I have many such ...
user avatar
10 votes
1 answer
4k views

Impact of CLUSTER on performance

I'm trying to optimize my Postgres 9.2 database to speed up queries with date restrictions. I have a timestamp column, but mostly I'm asking for some day, so I have created an index with timestamp to ...
user avatar
4 votes
3 answers
3k views

Extremely slow query on indexed column in Postgres

I'm getting a extremely slow query on an indexed column. Given the query SELECT * FROM orders WHERE shop_id = 3828 ORDER BY updated_at desc LIMIT 1 explain analyze returned: QUERY PLAN ----...
user avatar
  • 143
9 votes
2 answers
7k views

Optimizing queries on a range of timestamps (one column)

I am using Postgres 9.3 through Heroku. I have a table, "traffic", with 1M+ records that has many inserts and updates every day. I need to perform SUM operations across this table over different time ...
user avatar
  • 1,033
4 votes
1 answer
4k views

Maximum sum() of overlapping ranges

Essentially my question is: how does one do aggregate operations involving overlapping ranges in PostgreSQL 9.3 (or 9.4)? The specific problem I have at hand is that given a range, I want to find the ...
user avatar
  • 173
3 votes
2 answers
5k views

postgres - SUM Time of this Interval and Group by 30 and 30 minutes

I need to SUM Time of this Interval and Group by 30 and 30 minutes. CREATE TABLE foo AS SELECT date_start::timestamp, date_end::timestamp, agent FROM ( VALUES ('2017-01-06 06:52','2017-01-06 06:...
user avatar
  • 239
3 votes
2 answers
10k views

Poor query performance over timestamp range by week in PostgreSQL 9.3

I have a slow query that generates a report of account activity per week over the past year. The table currently has nearly 5 million rows and this query currently takes 8 seconds to execute. The (...
user avatar
4 votes
1 answer
3k views

PostgreSQL - Datetime ranges overlap

I have a table with datetime fields start and end. And I have a list of (start, end) items. I need to check which items from the list overlap with data in the table. The current query looks like this: ...
user avatar
  • 41
5 votes
4 answers
1k views

Does inserting in order have the same effect as clustering?

I have a table that will contain some pre-computed data based off other tables. (Computing on the fly is too computationally expensive given the size of data I have to deal with.) I will be generating ...
user avatar
  • 1,513
3 votes
2 answers
2k views

Containment check tstzrange @> timestamptz not using btree or gist index

Schema: Column | Type ----------------------+-------------------------- id | integer event_id | integer ...
user avatar
  • 866
2 votes
1 answer
1k views

Find tables with multiple indexes on same column

I am having an issue with scaffolding PostgreSQL DB because npgsql does not handle tables with two index on the same column well. Here's a link to my project on github. I am trying to find these ...
user avatar
  • 2,323
6 votes
1 answer
1k 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 operations ...
user avatar
  • 1,055

15 30 50 per page