What is the best way to create index and unique constraint?

Question

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

Answer 1

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:

• Is a composite index also good for queries on the first field?
• PostgreSQL composite primary key
• Why can I create a table with PRIMARY KEY on a nullable column?
• How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?

This also covers range filters on date, but it's less than ideal when combined with an equality filter on key, because ... see:

• Multicolumn index and performance

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:

• Does a query with a primary key and foreign keys run faster than a query with just primary keys?