Drop unique index of a unique constraint

Question

I'm currently working on a postgresql 15 database.

I've created a data table named "measures" with following schema

CREATE TABLE measures (
  timestamp TIMESTAMPTZ NOT NULL,
  value double precision,
  variable_id INTEGER REFERENCES variables(id) ON DELETE CASCADE,
  PRIMARY KEY (variable_id, timestamp)
)

When I create this table, the primary key (variable_id, timestamp) creates a unique constraint with a unique index.

My problem is that the default index is ordered ASC. I need to add another index like this

CREATE UNIQUE INDEX IF NOT EXISTS _index_measure_timestamp_variable ON measures (timestamp DESC, variable_id DESC ); 

It creates another index which is duplicated with the constraint index. The only difference is the order.

My questions are:

• after the creation of the second index, can I remove the constraint index?
• If I can't remove the constraint index is it possible to change the order of an index?
• If not should I keep the two indexes?

Thx for your help.

Answer 1

There should be no reason to change an index order from ASC to DESC, because Postgresql can use the same index for queries in ASCending or DESCending order without performance loss. So changing the order from ASC to DESC will make no difference.

However, you are actually proposing to change the order of the columns in the index, so that timestamp come first and variable_id comes last.

This change will indeed make a difference, because:

• it will optimize queries sorting or grouping by those columns in that order;
• it will optimize queries where you have contraints only on timestamp and not on variable_id;

So, to answer your questions:

after the creation of the second index, can i remove the constraint index ?

Yes, you can with ALTER TABLE measures DROP CONSTRAINT measures_pkey;

If i can't remove the constraint index is it possible to change the order of a index ?

No, to change an index you have to drop and recreate it.

If not should i keep the two index ?

Even if you could drop the first index, you have to determine if you should drop it or if you need both. It all depends on the queries you need to optimize.