Linked Questions

35
votes
2answers
32k 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 ...
5
votes
4answers
14k 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 ...
11
votes
1answer
4k 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.
8
votes
1answer
3k 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 ...
8
votes
1answer
3k 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 ...
8
votes
2answers
5k 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 ...
3
votes
1answer
3k 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 ...
3
votes
2answers
5k 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 (...
5
votes
4answers
542 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 ...
4
votes
1answer
2k 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: ...
2
votes
2answers
2k 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:...
5
votes
1answer
985 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 ...
2
votes
2answers
796 views

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

Schema: Column | Type ----------------------+-------------------------- id | integer event_id | integer ...
1
vote
1answer
747 views

Find tables with multiple indexes on same column [closed]

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 ...
1
vote
1answer
510 views

JSONB Array of Strings (with GIN index) versus Split Rows (B-Tree Index)

I have a database which stores receiver to indicate which account the data relates to. This has led to tons of duplication of data, as one set of data may create 3 separate rows, where all column data ...

15 30 50 per page