I am optimizing SQL query performance by reordering a composite index in PostgreSQL. I need to understand potential repercussions, including space usage, data saving overhead, and any other impacts of this modification.

To address performance issue, I have implemented an approach involving reordering the composite index.

Details:
Database: PostgreSQL
Table Name: readingTable
DDL Statement:

CREATE TABLE readingTable (
    ts timestamp NOT NULL,
    plant_id int8 NOT NULL,
    instance_type varchar(30) NOT NULL,
    instance_id int8 NOT NULL,
    readings jsonb NULL,
    CONSTRAINT idx_readingTable_pk PRIMARY KEY (ts, plant_id, instance_type, instance_id)
);
CREATE INDEX readingTable_ts_idx ON readingTable USING btree (ts DESC);

Table Data fromat:

Query:

SELECT *
FROM readingTable, LATERAL jsonb_array_elements(readings) AS readings_data
WHERE plant_id = 2
  AND instance_type = 'ALARM'
  AND instance_id IN (1765)
  AND ts BETWEEN '2024-05-01 00:00:00' AND '2024-05-30 23:59:00.000'
  AND readings_data ->> 'c' IN ('ALARM_TAG_1')
ORDER BY ts, instance_id;

If I run the above query for idx_readingTable_pk(ts, plant_id, instance_type, instance_id)then it take time to execute query so I have updated the idx_readingTable_pk from (ts, plant_id, instance_type, instance_id) To (plant_id, instance_type, instance_id, ts).

In the updated index, I have reordered the sequence of the composite key because when a condition includes equality checks (=) and range/inequality checks (>, >=, <, <=, IN), the columns involved in equality checks should appear first in the index, with the inequality columns following.

Query to update idx_readingTable_pk:

-- Drop the old primary key constraint
ALTER TABLE readingTable
DROP CONSTRAINT idx_readingTable_pk;

-- Add the new primary key constraint with the updated column order
ALTER TABLE readingTable
ADD CONSTRAINT idx_readingTable_pk PRIMARY KEY (plant_id, instance_type, instance_id,ts);

This modification has led to an improvement in performance, as shown by the below statistics. In the image below, you can see that I have tested the query with both the old and new indexes across various date ranges, and you can observe the performance improvement.

Statistics

Questions:

1. When the first executed the query to update the composite key order, does it update the existing index, create a new one, or both?
2. Are there any space complexities involved, such as increased space requirements for storing the updated index compared to the old one?
3. Does reordering the index introduce any overhead when saving data?
4. Are there any other potential repercussions of modifying the composite index that I should be aware of?