Linked Questions

42 votes
2 answers
43k 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 ...
jap1968's user avatar
  • 721
16 votes
1 answer
8k 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.
vfclists's user avatar
  • 1,053
11 votes
1 answer
6k 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 ...
Saurabh Nanda's user avatar
12 votes
1 answer
6k views

Impact of CLUSTER on performance

I'm trying to optimize a database table to speed up queries with date restrictions. Currently using Postgres 9.2. I have a timestamp column, but mostly I'm asking for some day, so I have created an ...
countryroadscat's user avatar
5 votes
4 answers
17k 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 ...
Ian Ringrose's user avatar
4 votes
3 answers
5k 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 ----...
davids's user avatar
  • 143
6 votes
4 answers
2k 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 ...
jpmc26's user avatar
  • 1,602
9 votes
2 answers
8k 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 ...
Evan Appleby's user avatar
  • 1,181
5 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 ...
Rob's user avatar
  • 183
1 vote
2 answers
4k views

How to get the second highest value per group?

Getting the second highest value from a table has been solved many times, but I'm looking for the second highest value in each group. Given this table: +----+-----+ | A | 10 | | A | 20 | | A | ...
RonJohn's user avatar
  • 647
4 votes
2 answers
7k 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:...
sealabr's user avatar
  • 249
3 votes
2 answers
13k 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 (...
Parker's user avatar
  • 439
4 votes
1 answer
4k 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: ...
Anna's user avatar
  • 41
3 votes
2 answers
3k views

Postgres prefers slow Seq Scan over fast Index Scan

Tables I have the following two tables. books, which contains ~5 million rows: Table "public.books" Column | Type | ...
runeks's user avatar
  • 133
3 votes
2 answers
3k views

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

Schema: Column | Type ----------------------+-------------------------- id | integer event_id | integer ...
Gajus's user avatar
  • 1,195

15 30 50 per page