What is the best way to create an index as well as unique constraint on a schema like below?
Most of my queries will filter based on date column. If I create single column index on date and a unique constraint including both (date and key), I end up creating two indices as unique constraint also creates an index.
Is there a better way around this?
| date | key | value |
|---|---|---|
| 12-12-2021 | a | 3 |
| 12-12-2021 | b | 4 |
| 12-13-2021 | a | 3 |
| 12-13-2021 | b | 4 |
It depends.
Most of my queries will filter based on date column.
That's not enough information. For equality filters, a PRIMARY KEY on (date, key) (with date as leading column!) will typically do just fine:
CREATE TABLE tbl (
date date
, key text
, value int
, PRIMARY KEY (date, key)
);
Because ... see:
This also covers range filters on date, but it's less than ideal when combined with an equality filter on key, because ... see:
If your table is as simple as your example suggests and you typically include value in the SELECT list, consider a covering index (requires Postgres 11 or later) to get index-only scans:
...
, PRIMARY KEY (date, key) INCLUDE (value)
See:
