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.