Global Secondary Index - Hologres: Central de Documentação do Alibaba Cloud

Pré-requisitos

Sua instância do Hologres deve estar na versão V4.0 ou posterior. Caso utilize uma versão anterior, consulte Atualização de instância.

Limitações

Tabelas com índice secundário global não permitem gravação de dados via Fixed FE ou Fixed Copy.
As colunas de índice secundário global aceitam apenas os tipos de dados TEXT, INTEGER, BIGINT e VARCHAR.
Não é possível modifique um índice secundário global.
Uma mesma coluna não pode ser usada simultaneamente como coluna de índice e coluna incluída.
A tabela source precisa ter uma chave primária.
O parâmetro time_to_live_in_seconds não pode ser defina na tabela source.
O total de colunas de índice e colunas incluídas em um índice secundário global não pode ultrapassar 512.
Índices secundários globais só podem ser crie em tabelas internas. Tabelas particionadas físicas e lógicas não são suportadas.
Se uma coluna da tabela source fizer parte de um índice secundário global, ela não poderá ser exclua nem modifique.
Não é permitido alterar o Table Group ou execute Resharding em uma tabela source que possua índice secundário global.
Por padrão, índices secundários globais utilizam apenas armazenamento padrão.
O índice secundário global adota o mesmo formato de armazenamento da tabela source: orientado a linhas, orientado a colunas ou híbrido (linhas e colunas).
- Quando a tabela source usa armazenamento orientado a linhas, o índice secundário global também utiliza esse formato por padrão.
- Para tabelas source com armazenamento orientado a colunas, o índice secundário global segue o mesmo padrão.
- Se a tabela source emprega armazenamento híbrido, o índice secundário global mantém essa configuração automaticamente.

Crie um índice secundário global

Sintaxe

CREATE GLOBAL INDEX [ IF NOT EXISTS ] index_name
  ON [schema_name.]table_name (index_column_name [, ...])
  [ INCLUDE (include_column_name[, ...]) ]

Parâmetros

Parâmetro	Obrigatório	Descrição
index_name	Sim	Nome do índice secundário global.
schema_name	Não	Nome do schema da tabela source. Se omitido, o sistema usa o schema padrão.
table_name	Sim	Nome da tabela source.
index_column_name	Sim	Colunas a serem indexadas. Para obter melhores resultados, utilize as colunas usadas como filtro em consultas pontuais (point queries) que não envolvem chave primária.
include_column_name	Não	Colunas adicionais a serem incluídas no índice secundário global.

Observações de uso
- Após o envie da instrução SQL, o sistema inicia a construção do índice. A operação CREATE GLOBAL INDEX só é concluída quando o índice estiver totalmente criado e visualize.
- A criação do índice gera escrita de dados extras, o que impacta o desempenho de gravação. Esse efeito aumenta conforme o volume de dados na tabela source e a quantidade de colunas no índice.
- O índice secundário global é sempre criado no mesmo schema da tabela source. Não é possível especifique um schema diferente para o índice.
- Uma consulta só utiliza o índice secundário global se todas as colunas referenciadas estiverem cobertas pelo índice, seja como coluna de índice ou coluna incluída.

Exclua um índice secundário global

Sintaxe
```
DROP INDEX [schema_name.]index_name
```

Parâmetros

Parâmetro	Obrigatório	Descrição
schema_name	Não	Nome do schema do índice secundário global. Se não for especificado, o sistema utiliza o schema padrão.
index_name	Sim	Nome do índice secundário global.

Visualize índices secundários globais

Listar todos os índices secundários globais

SELECT 
    n.nspname AS table_namespace,
    t.relname AS table_name,
    i.relname AS index_name
FROM 
    pg_class t
JOIN 
    pg_index ix ON t.oid = ix.indrelid
JOIN 
    pg_class i ON i.oid = ix.indexrelid
JOIN 
    pg_am am ON am.oid = i.relam
JOIN
    pg_namespace n ON n.oid = t.relnamespace 
WHERE 
    t.relkind = 'r'  -- Query only regular tables.
    AND am.amname = 'globalindex'

Verificar o tamanho de armazenamento do índice secundário global

Neste exemplo, global_index_name representa o nome do índice secundário global.
```
SELECT pg_relation_size('schema_name.global_index_name');
```

Consultar colunas incluídas

SELECT pg_catalog.pg_get_indexdef('global_index_name'::regclass, 0, true);

Exemplos

Considere um aplicativo de pedidos que consulta frequentemente os dados por prioridade do pedido. O exemplo abaixo utiliza a tabela orders:

Coluna	Tipo	Descrição
O_ORDERKEY	BIGINT	ID do pedido (chave primária).
O_CUSTKEY	INT	ID do cliente (chave estrangeira que referencia a tabela CUSTOMER).
O_ORDERSTATUS	CHAR(1)	Status do pedido ('F' = Finalizado, 'O' = Aberto, 'P' = Em processamento).
O_TOTALPRICE	DECIMAL(15,2)	Preço total do pedido.
O_ORDERDATE	DATE	Data de criação do pedido.
O_ORDERPRIORITY	TEXT	Prioridade do pedido ('1-URGENT', '2-HIGH', etc.).
O_CLERK	TEXT	ID do funcionário que processou o pedido.
O_SHIPPRIORITY	INT	Prioridade de envio. Valores maiores indicam maior prioridade.
O_COMMENT	TEXT	Comentários sobre o pedido.

Instrução SQL para crie a tabela de exemplo orders:

CREATE TABLE ORDERS
(
    O_ORDERKEY      BIGINT      NOT NULL PRIMARY KEY,
    O_CUSTKEY       INT         NOT NULL,
    O_ORDERSTATUS   CHAR(1)         NOT NULL,
    O_TOTALPRICE    DECIMAL(15,2) NOT NULL,
    O_ORDERDATE     DATE NOT NULL,
    O_ORDERPRIORITY TEXT        NOT NULL,
    O_CLERK         TEXT        NOT NULL,
    O_SHIPPRIORITY  INT         NOT NULL,
    O_COMMENT       TEXT        NOT NULL
) WITH (
  orientation='row,column',
  segment_key='O_ORDERDATE',
  distribution_key='O_ORDERKEY',
  bitmap_columns='O_ORDERSTATUS,O_ORDERPRIORITY,O_CLERK,O_SHIPPRIORITY',
  dictionary_encoding_columns='o_comment:off,o_orderpriority,o_clerk'
);
COMMENT ON TABLE ORDERS IS 'Main order table that stores basic order information and status.';
COMMENT ON COLUMN ORDERS.O_ORDERKEY IS 'The order ID (primary key).';
COMMENT ON COLUMN ORDERS.O_CUSTKEY IS 'The customer ID (a foreign key that references the CUSTOMER table).';
COMMENT ON COLUMN ORDERS.O_ORDERSTATUS IS 'The order status (''F'' = Finished, ''O'' = Open, ''P'' = Processing).';
COMMENT ON COLUMN ORDERS.O_TOTALPRICE IS 'The total price of the order.';
COMMENT ON COLUMN ORDERS.O_ORDERDATE IS 'The date when the order was created.';
COMMENT ON COLUMN ORDERS.O_ORDERPRIORITY IS 'The order priority (''1-URGENT'', ''2-HIGH'', etc.).';
COMMENT ON COLUMN ORDERS.O_CLERK IS 'The ID of the employee who processed the order.';
COMMENT ON COLUMN ORDERS.O_SHIPPRIORITY IS 'The shipping priority. A larger value indicates a higher priority.';
COMMENT ON COLUMN ORDERS.O_COMMENT IS 'Comments about the order.';

Caso você execute frequentemente consultas como a seguinte para buscar pedidos de uma prioridade específica:

SELECT 
  O_ORDERKEY,
  O_CUSTKEY,
  O_ORDERSTATUS,
  O_TOTALPRICE,
  O_ORDERDATE,
  O_ORDERPRIORITY,
  O_CLERK,
  O_SHIPPRIORITY,
  O_COMMENT
FROM ORDERS
WHERE O_ORDERPRIORITY='1-URGENT'

Utilize EXPLAIN para verificar o plano de execução da instrução SQL:

EXPLAIN
SELECT 
  O_ORDERKEY,
  O_CUSTKEY,
  O_ORDERSTATUS,
  O_TOTALPRICE,
  O_ORDERDATE,
  O_ORDERPRIORITY,
  O_CLERK,
  O_SHIPPRIORITY,
  O_COMMENT
FROM ORDERS
WHERE O_ORDERPRIORITY='1-URGENT'

QUERY PLAN
Gather  (cost=0.00..1.00 rows=1 width=53)
  ->  Local Gather  (cost=0.00..1.00 rows=1 width=53)
        ->  Index Scan using Clustering_index on orders  (cost=0.00..1.00 rows=1 width=53)
              Bitmap Filter: (o_orderpriority = '1-URGENT'::text)
Query Queue: init_warehouse.default_queue
Optimizer: HQO version 4.0.0

É possível tornar as consultas mais eficientes adicionando um índice à coluna O_ORDERPRIORITY.

O plano retornado indica que a consulta utilizou um Bitmap Index, o que oferece melhoria limitada de desempenho. Para alcançar um QPS maior, crie um índice secundário global na coluna O_ORDERPRIORITY:
```
CREATE GLOBAL INDEX idx_orders ON orders(O_ORDERPRIORITY)
INCLUDE (
  O_CUSTKEY,
  O_ORDERSTATUS,
  O_TOTALPRICE,
  O_ORDERDATE,
  O_CLERK,
  O_SHIPPRIORITY,
  O_COMMENT
  );
```
Após adicionar o índice, execute novamente a instrução EXPLAIN para analisar o novo plano de execução:
```
QUERY PLAN
Local Gather  (cost=0.00..1.76 rows=3035601 width=99)
  ->  Index Scan using Clustering_index on idx_orders  (cost=0.00..1.54 rows=3035601 width=99)
        Shard Prune: Eagerly
        Shards selected: 1 out of 20
        Cluster Filter: (o_orderpriority = '1-URGENT'::text)
Query Queue: init_warehouse.default_queue
Optimizer: HQO version 4.0.0
```
O plano agora mostra que Index Scan using Clustering_index on tem como alvo o índice secundário global idx_orders, com aplicação de shard pruning. Isso melhora efetivamente o QPS.

Use um Fixed Plan para aumentar ainda mais o QPS.

SET hg_experimental_enable_fixed_dispatcher_for_scan = true;

O plano de execução passa a exibir um FixedSelectNode no índice, confirmando que a consulta foi otimizada com Fixed Plan para máximo desempenho.

SET hg_experimental_enable_fixed_dispatcher_for_scan = true;
EXPLAIN
SELECT
    O_ORDERKEY,
    O_CUSTKEY,
    O_ORDERSTATUS,
    O_TOTALPRICE,
    O_ORDERDATE,
    O_ORDERPRIORITY,
    O_CLERK,
    O_SHIPPRIORITY,
    O_COMMENT
FROM ORDERS
WHERE O_ORDERPRIORITY='1-URGENT'
QUERY PLAN
FixedSelectNode on idx_orders  (cost=0.00..0.00 rows=0 width=0)
  Qual: o_orderpriority =>  ranges: {['1-URGENT'::text,'1-URGENT'::text]}
Optimizer: HQO version 4.0.0