0

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.

Improve this question

1 Answer 1

Reset to default
0

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.

Improve this answer
3
  • 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 Are you sure about this cause some queries pass from 10 seconds to 1 second depending the order of the index ? If i remove the constraint index it will not affect the constraint and the pk ? Commented May 4, 2023 at 15:49
  • 3
    @Eventeos see this: postgresql.org/docs/current/indexes-ordering.html. You cannot remove the index without removing the constraint and the pk. Commented May 4, 2023 at 16:58
  • @Eventeos regarding query times, to get a more detailed answer you have to provide the query and also EXPLAIN (ANALYZE, BUFFERS) for both slow and fast query Commented May 4, 2023 at 17:02

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.