Impact of Reordering Composite Index on PostgreSQL: Performance, Space, and Overhead Considerations

Question

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:

ts	plant_id	instance_type	instance_id	readings
2024-05-31 23:59:00.000	4	ALARM	1765	[{"c": "ALARM_TAG_1", "d": "NUMBER", "s": "S15", "u": "kW/kWp", "v": null}, {"c": "ALARM_TAG_2", "d": "BOOLEAN", "s": "S00", "u": "AU", "v": "false"}, {"c": "ALARM_TAG_3", "d": "NUMBER", "s": "S00", "u": "kW/kWp", "v": "12.25"}]

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.

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?

Answer 1

Indexes are updated whenever you modify the table. If it were re-created from scratch, the performance would be unbearable.

The new index will have approximately the same size as the old one. It will be smaller initially, because it has no bloat yet, but the sizes should even out over time.

The order of the columns in the index doesn't affect the performance of creating or updating the index.

Changing the order of the index columns can have enormous effects on the performance of your queries. For example, this query:

SELECT plant_id, instance_type
FROM readingtable
WHERE ts BETWEEN '2024-07-01 00:00:00' AND '2024-07-02 00:00:00';

would be very fast with the original primary key, but much slower with the new primary key.