CODEWITHSUNDEEP
postgresqlindexing
Alexaner Tipugin
Alexaner Tipugin

Reputation: 507

Best way to index two "timestamp" columns

How i should index two timestamp columns (ie starts_at and ends_at), which would be used almost always together in queries (like starts_at >= ? AND ends_at <= ?)? Create composite index for both columns or index each one separatly?

Btw, i'm using Postgres, if it matters :)

Upvotes: 3

Views: 1679

Answers (2)

user1151
user1151

Reputation:

This one's for posterity's sake as I see it was answered a long time ago. This assumes Postgres 9.2+. If you have a range of dates you should use tsrange as your data type:

create table events(
  id serial primary key,
  name text not null unique,
  duration tsrange not null
);

The nice thing about ranges is you can do some amazing queries with them, for your needs, this is the best part:

create index idx_event_duration on events
using GIST(duration);

Now you can query using all kinds of special operators:

select * from events where
duration @> (now() - interval '2 weeks');

You can read more about them here. This barely scratches the surface of what they can do.

Upvotes: 4

Philip Couling
Philip Couling

Reputation: 14883

If these two columns are true timestamps with a low number of rows containing precisely the same timestamp, then there really is no use in a composite key, since the range scan would not be able to pick a single value for the first. This can be different if the fields are dates, as frequently dates can match where timestamps don't. There is a small benefit to it but my experience is that it's not enormous.

If you are using a sufficiently large and dense datase (if a single index scan will need to scan a large portion of the index) the query planner can use multiple indexes, combining them with a bitmap.

http://www.postgresql.org/docs/current/static/indexes-bitmap-scans.html

As always with this type of question, it's worth running tests on your dataset.

Upvotes: 1

Related Questions