Este conteúdo foi traduzido por máquina para sua conveniência e a Huawei Cloud não pode garantir que o conteúdo foi traduzido com precisão. Para exibir o conteúdo original, use o link no canto superior direito para mudar para a página em inglês.
Central de ajuda/ GaussDB(DWS)/ Perguntas frequentes/ Uso do banco de dados/ Por que às vezes os índices de consulta do GaussDB(DWS) se tornam inválidos?
Atualizado em 2024-05-09 GMT+08:00
Ver PDF
Compartilhar

Por que às vezes os índices de consulta do GaussDB(DWS) se tornam inválidos?

A criação de índices para tabelas pode melhorar o desempenho da consulta do banco de dados. No entanto, às vezes os índices não podem ser usados em um plano de consulta. Esta seção descreve vários motivos comuns e métodos de otimização.

Razão 1: os conjuntos de resultados retornados são grandes.

O seguinte usa Seq Scan e Index Scan em uma tabela de armazenamento de linha como um exemplo:

  • Seq Scan: pesquisa registros de tabelas em sequência. Todos os registros são recuperados durante cada varredura. Este é o método de digitalização de tabela mais simples e básico, e seu custo é alto.
  • Index Scan: pesquisa o índice primeiro, encontra o local de destino (ponteiro) no índice e, em seguida, recupera dados na página de destino.

A varredura de índice é mais rápida do que a varredura de sequência na maioria dos casos. No entanto, se os conjuntos de resultados obtidos representarem uma grande proporção (mais de 70%) de todos os dados, Index Scan precisa verificar os índices antes de ler os dados da tabela. Isso torna a varredura de tabela mais lenta.

Razão 2: ANALYZE não é realizada em tempo hábil.

ANALYZE é usada para atualizar as estatísticas da tabela. Se ANALYZE não for executada em uma tabela ou uma grande quantidade de dados for adicionada ou excluída de uma tabela após a execução de ANALYZE, as estatísticas podem ser imprecisas, o que pode fazer com que uma consulta pule o índice.

Método da optimização: execute a instrução ANALYZE na tabela para atualizar as estatísticas.

Razão 3: condições de filtragem contém funções ou conversão de tipo de dados implícita

Se cálculo, função ou conversão implícita de tipo de dados estiver contida nos critérios de filtro, os índices podem falhar ao serem selecionados.

Por exemplo, quando uma tabela é criada, os índices são criados nas colunas a, b e c.

1
 
create table test(a int, b text, c date);
  • Execute o cálculo nas colunas indexadas.

    A saída do comando a seguir indica que ambos where a = 101 e where a = 102 - 1 usam o índice na coluna a, mas where a + 1 = 102 não usa o índice.

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
    		 
    explain verbose select * from test where a  = 101;
                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
  id |                   operation                    | E-rows | E-distinct | E-memory | E-width | E-costs
 ----+------------------------------------------------+--------+------------+----------+---------+---------
   1 | ->  Streaming (type: GATHER)                   |      1 |            |          |      44 | 16.27
   2 |    ->  Index Scan using index_a on public.test |      1 |            | 1MB      |      44 | 8.27

 Predicate Information (identified by plan id)
 ---------------------------------------------
   2 --Index Scan using index_a on public.test
         Index Cond: (test.a = 101)

 Targetlist Information (identified by plan id)
 ----------------------------------------------
   1 --Streaming (type: GATHER)
         Output: a, b, c
         Node/s: dn_6005_6006
   2 --Index Scan using index_a on public.test
         Output: a, b, c
         Distribute Key: a

   ====== Query Summary =====
 -------------------------------
 System available mem: 3358720KB
 Query Max mem: 3358720KB
 Query estimated mem: 1024KB
(24 rows)

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
    		 
     explain verbose select * from test where a  = 102 - 1;
                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
  id |                   operation                    | E-rows | E-distinct | E-memory | E-width | E-costs
 ----+------------------------------------------------+--------+------------+----------+---------+---------
   1 | ->  Streaming (type: GATHER)                   |      1 |            |          |      44 | 16.27
   2 |    ->  Index Scan using index_a on public.test |      1 |            | 1MB      |      44 | 8.27

 Predicate Information (identified by plan id)
 ---------------------------------------------
   2 --Index Scan using index_a on public.test
         Index Cond: (test.a = 101)

 Targetlist Information (identified by plan id)
 ----------------------------------------------
   1 --Streaming (type: GATHER)
         Output: a, b, c
         Node/s: dn_6005_6006
   2 --Index Scan using index_a on public.test
         Output: a, b, c
         Distribute Key: a

   ====== Query Summary =====
 -------------------------------
 System available mem: 3358720KB
 Query Max mem: 3358720KB
 Query estimated mem: 1024KB
(24 rows)

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
    		 
    explain verbose select * from test where a + 1 = 102;
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
  id |           operation            | E-rows | E-distinct | E-memory | E-width | E-costs
 ----+--------------------------------+--------+------------+----------+---------+---------
   1 | ->  Streaming (type: GATHER)   |      1 |            |          |      44 | 22.21
   2 |    ->  Seq Scan on public.test |      1 |            | 1MB      |      44 | 14.21

 Predicate Information (identified by plan id)
 ---------------------------------------------
   2 --Seq Scan on public.test
         Filter: ((test.a + 1) = 102)

 Targetlist Information (identified by plan id)
 ----------------------------------------------
   1 --Streaming (type: GATHER)
         Output: a, b, c
         Node/s: All datanodes
   2 --Seq Scan on public.test
         Output: a, b, c
         Distribute Key: a

   ====== Query Summary =====
 -------------------------------
 System available mem: 3358720KB
 Query Max mem: 3358720KB
 Query estimated mem: 1024KB
(24 rows)

    Método da optimização: use constantes em vez de expressões ou coloque cálculo constante à direita do sinal de igual (=).

  • Use funções em colunas indexadas.

    De acordo com o seguinte resultado de execução, se uma função for usada em uma coluna indexada, o índice não será selecionado.

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
    		 
    explain verbose select * from test where to_char(c, 'yyyyMMdd') = to_char(CURRENT_DATE,'yyyyMMdd');
                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
  id |           operation            | E-rows | E-distinct | E-memory | E-width | E-costs
 ----+--------------------------------+--------+------------+----------+---------+---------
   1 | ->  Streaming (type: GATHER)   |      1 |            |          |      44 | 22.28
   2 |    ->  Seq Scan on public.test |      1 |            | 1MB      |      44 | 14.28

                                               Predicate Information (identified by plan id)
 ------------------------------------------------------------------------------------------------------------------------------------------
   2 --Seq Scan on public.test
         Filter: (to_char(test.c, 'yyyyMMdd'::text) = to_char(('2022-11-30'::pg_catalog.date)::timestamp with time zone, 'yyyyMMdd'::text))

 Targetlist Information (identified by plan id)
 ----------------------------------------------
   1 --Streaming (type: GATHER)
         Output: a, b, c
         Node/s: All datanodes
   2 --Seq Scan on public.test
         Output: a, b, c
         Distribute Key: a

   ====== Query Summary =====
 -------------------------------
 System available mem: 3358720KB
 Query Max mem: 3358720KB
 Query estimated mem: 1024KB
(24 rows)

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
    		 
    explain verbose select * from test where c = current_date;
                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
  id |                   operation                    | E-rows | E-distinct | E-memory | E-width | E-costs
 ----+------------------------------------------------+--------+------------+----------+---------+---------
   1 | ->  Streaming (type: GATHER)                   |      1 |            |          |      44 | 16.27
   2 |    ->  Index Scan using index_c on public.test |      1 |            | 1MB      |      44 | 8.27

        Predicate Information (identified by plan id)
 ------------------------------------------------------------
   2 --Index Scan using index_c on public.test
         Index Cond: (test.c = '2022-11-30'::pg_catalog.date)

 Targetlist Information (identified by plan id)
 ----------------------------------------------
   1 --Streaming (type: GATHER)
         Output: a, b, c
         Node/s: All datanodes
   2 --Index Scan using index_c on public.test
         Output: a, b, c
         Distribute Key: a

   ====== Query Summary =====
 -------------------------------
 System available mem: 3358720KB
 Query Max mem: 3358720KB
 Query estimated mem: 1024KB
(24 rows)

    Método da optimização: não use funções desnecessárias em colunas indexadas.

  • Conversão implícita de tipos de dados.

    Esse cenário é comum. Por exemplo, o tipo de coluna b é Text e a condição de filtragem é where b = 2. Durante a geração do plano, o tipo Text é implicitamente convertido para o tipo Bigint e a condição de filtragem real muda para where b::bigint = 2. Como resultado, o índice na coluna b se torna inválido.

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
    		 
    explain verbose select * from test where b = 2;
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
  id |           operation            | E-rows | E-distinct | E-memory | E-width | E-costs
 ----+--------------------------------+--------+------------+----------+---------+---------
   1 | ->  Streaming (type: GATHER)   |      1 |            |          |      44 | 22.21
   2 |    ->  Seq Scan on public.test |      1 |            | 1MB      |      44 | 14.21

 Predicate Information (identified by plan id)
 ---------------------------------------------
   2 --Seq Scan on public.test
         Filter: ((test.b)::bigint = 2)

 Targetlist Information (identified by plan id)
 ----------------------------------------------
   1 --Streaming (type: GATHER)
         Output: a, b, c
         Node/s: All datanodes
   2 --Seq Scan on public.test
         Output: a, b, c
         Distribute Key: a

   ====== Query Summary =====
 -------------------------------
 System available mem: 3358720KB
 Query Max mem: 3358720KB
 Query estimated mem: 1024KB
(24 rows)

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
    		 
    explain verbose select * from test where b = '2';
                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
  id |                   operation                    | E-rows | E-distinct | E-memory | E-width | E-costs
 ----+------------------------------------------------+--------+------------+----------+---------+---------
   1 | ->  Streaming (type: GATHER)                   |      1 |            |          |      44 | 16.27
   2 |    ->  Index Scan using index_b on public.test |      1 |            | 1MB      |      44 | 8.27

 Predicate Information (identified by plan id)
 ---------------------------------------------
   2 --Index Scan using index_b on public.test
         Index Cond: (test.b = '2'::text)

 Targetlist Information (identified by plan id)
 ----------------------------------------------
   1 --Streaming (type: GATHER)
         Output: a, b, c
         Node/s: All datanodes
   2 --Index Scan using index_b on public.test
         Output: a, b, c
         Distribute Key: a

   ====== Query Summary =====
 -------------------------------
 System available mem: 3358720KB
 Query Max mem: 3358720KB
 Query estimated mem: 1024KB
(24 rows)

    Método da optimização: use constantes do mesmo tipo que a coluna indexada para evitar a conversão de tipo implícita.

Cenário 4: Hashjoin é substituído por Nestloop + Indexscan.

Quando duas tabelas são juntadas, o número de linhas no conjunto de resultados filtradas pela condição WHERE em uma tabela é pequeno, portanto, o número de linhas no conjunto de resultados finais também é pequeno. Neste caso, o efeito de nestloop+indexscan é melhor do que o de hashjoin. O melhor plano de execução é o seguinte:

Você pode ver que Index Cond: (t1.b = t2.b) na camada 5 empurrou a condição de junção para baixo para a varredura da tabela base.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
 
explain verbose select t1.a,t1.b from t1,t2 where t1.b=t2.b and t2.a=4;
 id |                    operation                     | E-rows | E-distinct | E-memory | E-width | E-costs 
----+--------------------------------------------------+--------+------------+----------+---------+---------
  1 | ->  Streaming (type: GATHER)                     |     26 |            |          |       8 | 17.97
  2 |    ->  Nested Loop (3,5)                         |     26 |            | 1MB      |       8 | 11.97
  3 |       ->  Streaming(type: BROADCAST)             |      2 |            | 2MB      |       4 | 2.78
  4 |          ->  Seq Scan on public.t2               |      1 |            | 1MB      |       4 | 2.62
  5 |       ->  Index Scan using t1_b_idx on public.t1 |     26 |            | 1MB      |       8 | 9.05
(5 rows)

 Predicate Information (identified by plan id) 
-----------------------------------------------
   4 --Seq Scan on public.t2
         Filter: (t2.a = 4)
   5 --Index Scan using t1_b_idx on public.t1
         Index Cond: (t1.b = t2.b)
(4 rows)

 Targetlist Information (identified by plan id) 
------------------------------------------------
   1 --Streaming (type: GATHER)
         Output: t1.a, t1.b
         Node/s: All datanodes
   2 --Nested Loop (3,5)
         Output: t1.a, t1.b
   3 --Streaming(type: BROADCAST)
         Output: t2.b
         Spawn on: datanode2
         Consumer Nodes: All datanodes
   4 --Seq Scan on public.t2
         Output: t2.b
         Distribute Key: t2.a
   5 --Index Scan using t1_b_idx on public.t1
         Output: t1.a, t1.b
         Distribute Key: t1.a
(15 rows)

   ====== Query Summary =====    
---------------------------------
 System available mem: 9262694KB
 Query Max mem: 9471590KB
 Query estimated mem: 5144KB
(3 rows)

Se o otimizador não selecionar tal plano de execução, você poderá otimizá-lo da seguinte maneira:

1
2
3
 
set enable_index_nestloop = on;
set enable_hashjoin = off;
set enable_seqscan = off;

Razão 5: o método de verificação é especificado incorretamente por dicas.

As dicas de plano do GaussDB(DWS) podem especificar três métodos de varredura: tablescan, indexscan e indexonlyscan.

  • Table Scan: varredura de tabela completa, como Seq Scan de tabelas de armazenamento de linha e CStore Scan de tabelas de armazenamento de coluna.
  • Index Scan: verifica índices e, em seguida, obtém registros de tabela com base nos índices.
  • Index-Only Scan: verifica os índices, que cobrem todos os resultados necessários. Comparada com index scan, a index-only scan cobre todas as colunas consultadas. Dessa forma, apenas os índices são recuperados e os registros de dados não precisam ser recuperados.

Em cenários de Index-Only Scan, a Index Scan especificada por uma dica será inválida.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
explain verbose select/*+ indexscan(test)*/ b from test where b = '1';
WARNING:  unused hint: IndexScan(test)
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
  id |                      operation                      | E-rows | E-distinct | E-memory | E-width | E-costs
 ----+-----------------------------------------------------+--------+------------+----------+---------+---------
   1 | ->  Streaming (type: GATHER)                        |      1 |            |          |      32 | 16.27
   2 |    ->  Index Only Scan using index_b on public.test |      1 |            | 1MB      |      32 | 8.27

   Predicate Information (identified by plan id)
 --------------------------------------------------
   2 --Index Only Scan using index_b on public.test
         Index Cond: (test.b = '1'::text)

   Targetlist Information (identified by plan id)
 --------------------------------------------------
   1 --Streaming (type: GATHER)
         Output: b
         Node/s: All datanodes
   2 --Index Only Scan using index_b on public.test
         Output: b
         Distribute Key: a

   ====== Query Summary =====
 -------------------------------
 System available mem: 3358720KB
 Query Max mem: 3358720KB
 Query estimated mem: 1024KB
(24 rows)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
explain verbose select/*+ indexonlyscan(test)*/ b from test where b = '1';
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
  id |                      operation                      | E-rows | E-distinct | E-memory | E-width | E-costs
 ----+-----------------------------------------------------+--------+------------+----------+---------+---------
   1 | ->  Streaming (type: GATHER)                        |      1 |            |          |      32 | 16.27
   2 |    ->  Index Only Scan using index_b on public.test |      1 |            | 1MB      |      32 | 8.27

   Predicate Information (identified by plan id)
 --------------------------------------------------
   2 --Index Only Scan using index_b on public.test
         Index Cond: (test.b = '1'::text)

   Targetlist Information (identified by plan id)
 --------------------------------------------------
   1 --Streaming (type: GATHER)
         Output: b
         Node/s: All datanodes
   2 --Index Only Scan using index_b on public.test
         Output: b
         Distribute Key: a

   ====== Query Summary =====
 -------------------------------
 System available mem: 3358720KB
 Query Max mem: 3358720KB
 Query estimated mem: 1024KB
(24 rows)

Método da optimização: especifique corretamente Index scan e Index-Only Scan.

Razão 6: uso Incorreto de índice GIN na recuperação de texto completo

Para acelerar a pesquisa de texto, você pode criar um índice GIN para pesquisa de texto completo.

1
 
CREATE INDEX idxb ON test using gin(to_tsvector('english',b));

Ao criar o índice GIN, você deve usar a versão de 2 argumentos de to_tsvector. Somente quando a consulta também usa a versão de 2 argumentos e os argumentos são os mesmos que no índice GIN, o índice GIN pode ser chamado.

A função to_tsvector() aceita um ou dois argumentos. Se a versão de um argumento do índice for usada, o sistema usará a configuração especificada por default_text_search_config por padrão. Para criar um índice, a versão de dois argumentos deve ser usada, ou o conteúdo do índice pode ser inconsistente.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
 
explain verbose select  * from test where to_tsvector(b) @@ to_tsquery('cat') order by 1;
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
  id |             operation             | E-rows | E-distinct | E-memory | E-width | E-costs
 ----+-----------------------------------+--------+------------+----------+---------+---------
   1 | ->  Streaming (type: GATHER)      |      2 |            |          |      44 | 22.23
   2 |    ->  Sort                       |      2 |            | 16MB     |      44 | 14.23
   3 |       ->  Seq Scan on public.test |      1 |            | 1MB      |      44 | 14.21

        Predicate Information (identified by plan id)
 -----------------------------------------------------------
   3 --Seq Scan on public.test
         Filter: (to_tsvector(test.b) @@ '''cat'''::tsquery)

 Targetlist Information (identified by plan id)
 ----------------------------------------------
   1 --Streaming (type: GATHER)
         Output: a, b, c
         Merge Sort Key: test.a
         Node/s: All datanodes
   2 --Sort
         Output: a, b, c
         Sort Key: test.a
   3 --Seq Scan on public.test
         Output: a, b, c
         Distribute Key: a

   ====== Query Summary =====
 -------------------------------
 System available mem: 3358720KB
 Query Max mem: 3358720KB
 Query estimated mem: 1024KB
(29 rows)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
 
explain verbose select  * from test where to_tsvector('english',b) @@ to_tsquery('cat') order by 1;
                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
  id |                 operation                 | E-rows | E-distinct | E-memory | E-width | E-costs
 ----+-------------------------------------------+--------+------------+----------+---------+---------
   1 | ->  Streaming (type: GATHER)              |      2 |            |          |      44 | 20.03
   2 |    ->  Sort                               |      2 |            | 16MB     |      44 | 12.03
   3 |       ->  Bitmap Heap Scan on public.test |      1 |            | 1MB      |      44 | 12.02
   4 |          ->  Bitmap Index Scan            |      1 |            | 1MB      |       0 | 8.00

                      Predicate Information (identified by plan id)
 ---------------------------------------------------------------------------------------
   3 --Bitmap Heap Scan on public.test
         Recheck Cond: (to_tsvector('english'::regconfig, test.b) @@ '''cat'''::tsquery)
   4 --Bitmap Index Scan
         Index Cond: (to_tsvector('english'::regconfig, test.b) @@ '''cat'''::tsquery)

 Targetlist Information (identified by plan id)
 ----------------------------------------------
   1 --Streaming (type: GATHER)
         Output: a, b, c
         Merge Sort Key: test.a
         Node/s: All datanodes
   2 --Sort
         Output: a, b, c
         Sort Key: test.a
   3 --Bitmap Heap Scan on public.test
         Output: a, b, c
         Distribute Key: a

   ====== Query Summary =====
 -------------------------------
 System available mem: 3358720KB
 Query Max mem: 3358720KB
 Query estimated mem: 2048KB
(32 rows)

Método da optimização: use a versão de dois argumentos de to_tsvector para a consulta e certifique-se de que os valores do argumento sejam os mesmos do índice.

Tópico principal: Uso do banco de dados