Unmesh Kadam's questions

Estou otimizando o desempenho da consulta SQL reordenando um índice composto no PostgreSQL. Preciso entender as possíveis repercussões, incluindo uso de espaço, sobrecarga de economia de dados e quaisquer outros impactos dessa modificação.

Para resolver o problema de desempenho, implementei uma abordagem que envolve a reordenação do índice composto.

Detalhes: Banco de dados: Nome da tabela PostgreSQL : instrução DDL readingTable :

    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);

Dados da tabela deat:

Consulta:

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;

Se eu executar a consulta acima para idx_readingTable_pk(ts, plant_id, instance_type, instance_id) então levará algum tempo para executar a consulta, então atualizei o idx_readingTable_pk de (ts, plant_id, instance_type, instance_id) para (plant_id, instance_type, instance_id, ts) .

No índice atualizado, reordenei a sequência da chave composta porque quando uma condição inclui verificações de igualdade (=) e verificações de intervalo/desigualdade (>, >=, <, <=, IN), as colunas envolvidas nas verificações de igualdade devem aparecem primeiro no índice, seguidos pelas colunas de desigualdade.

Consulta para atualizar 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);

Esta modificação levou a uma melhoria no desempenho, conforme mostram as estatísticas abaixo. Na imagem abaixo, você pode ver que testei a consulta com os índices antigo e novo em vários períodos e pode observar a melhoria no desempenho.

Questões:

1. Quando o primeiro executou a consulta para atualizar a ordem da chave composta, ele atualiza o índice existente, cria um novo ou ambos?
2. Há alguma complexidade de espaço envolvida, como maiores requisitos de espaço para armazenar o índice atualizado em comparação com o antigo?
3. A reordenação do índice introduz alguma sobrecarga ao salvar dados?
4. Há alguma outra repercussão potencial da modificação do índice composto que eu deva estar ciente?