Estos contenidos se han traducido de forma automática para su comodidad, pero Huawei Cloud no garantiza la exactitud de estos. Para consultar los contenidos originales, acceda a la versión en inglés.
Centro de ayuda/ GaussDB(DWS)/ Preguntas frecuentes/ Uso de la base de datos/ ¿Por qué a veces los índices de consulta de GaussDB(DWS) se vuelven inválidos?
Actualización más reciente 2023-10-12 GMT+08:00
Ver PDF
Compartir

¿Por qué a veces los índices de consulta de GaussDB(DWS) se vuelven inválidos?

La creación de índices para tablas puede mejorar el rendimiento de las consultas de base de datos. Sin embargo, a veces los índices no se pueden utilizar en un plan de consulta. Esta sección describe varias razones comunes y métodos de optimización.

Motivo 1: Los conjuntos de resultados devueltos son grandes.

A continuación, se utiliza Seq Scan y Index Scan en una tabla de almacén de filas como ejemplo:

  • Seq Scan: busca registros de tabla en secuencia. Todos los registros se recuperan durante cada escaneo. Este es el método de escaneo de tabla más simple y básico, y su costo es alto.
  • Análisis de índice: busque primero el índice, encuentre la ubicación de destino (puntero) en el índice y, a continuación, recupera datos en la página de destino.

La exploración de índice es más rápida que la exploración de secuencia en la mayoría de los casos. Sin embargo, si los conjuntos de resultados obtenidos representan una gran proporción (más del 70%) de todos los datos, Index Scan necesita analizar los índices antes de leer los datos de la tabla. Esto hace que el escaneo de la tabla sea más lento.

Razón 2: ANALYZE no se realiza de manera oportuna.

ANALYZE se utiliza para actualizar las estadísticas de la tabla. Si ANALYZE no se ejecuta en una tabla o una gran cantidad de datos se añade o se elimina de una tabla después de ejecutar ANALYZE las estadísticas pueden ser inexactas, lo que puede hacer que una consulta omita el índice.

Método de optimización: ejecute la instrucción ANALYZE en la tabla para actualizar las estadísticas.

Motivo 3: Las condiciones de filtrado contienen funciones o conversión de tipo de datos implícito

Si la conversión de cálculo, función o tipo de datos implícito está contenida en los criterios de filtro, los índices pueden fallar al seleccionarse.

Por ejemplo, cuando se crea una tabla, los índices se crean en las columnas a, b y c.

1
 
create table test(a int, b text, c date);
  • Realice el cálculo en las columnas indexadas.

    El siguiente resultado del comando indica que tanto where a = 101 como where a = 102 - 1 usan el índice en la columna a, pero where a + 1 = 102 no usa el í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 de optimización: use constantes en lugar de expresiones, o ponga el cálculo constante a la derecha del signo igual (=).

  • Utilice funciones en columnas indexadas.

    De acuerdo con el siguiente resultado de ejecución, si se usa una función en una columna indexada, el índice no se puede seleccionar.

     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 de optimización: no utilice funciones innecesarias en columnas indexadas.

  • Conversión implícita de tipos de datos.

    Este escenario es común. Por ejemplo, el tipo de la columna b es Text y la condición de filtrado es where b = 2. Durante la generación del plan, el tipo Text se convierte implícitamente al tipo Bigint y la condición de filtrado real cambia a where b::bigint = 2. Como resultado, el índice de la columna b no es vá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 de optimización: utilice constantes del mismo tipo que la columna indexada para evitar la conversión de tipos implícitos.

Escenario 4: Hashjoin se reemplaza con Nestloop + Indexscan.

Cuando se unen dos tablas, el número de filas en el conjunto de resultados filtrado por la condición WHERE en una tabla es pequeño, por lo que el número de filas en el conjunto de resultados final también es pequeño. En este caso, el efecto de nestloop+indexscan es mejor que el de hashjoin. El mejor plan de ejecución es el siguiente:

Puede ver que el índice Cond: (t1.b = t2.b) en la capa 5 ha empujado la condición de unión hacia abajo hasta el escaneo de la tabla 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)

Si el optimizador no selecciona dicho plan de ejecución, puede optimizarlo de la siguiente manera:

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

Razón 5: El método de escaneo está especificado incorrectamente por las sugerencias.

Las sugerencias del plan de GaussDB(DWS) pueden especificar tres métodos de análisis: tablescan, indexscan y indexonlyscan.

  • Exploración de tablas: exploración de tablas completas, como Seq Scan de tablas de almacenamiento de filas y CStore Scan de tablas de almacenamiento de columnas.
  • Análisis de índices: analiza los índices y, a continuación, obtiene los registros de tabla basados en los índices.
  • Análisis de solo índice: analiza índices, que cubren todos los resultados requeridos. En comparación con el análisis de índice, el análisis de solo índice cubre todas las columnas consultadas. De esta manera, solo se recuperan índices y no es necesario recuperar registros de datos.

En los escenarios de análisis de solo índice, el análisis de índice especificado por una sugerencia no será vá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
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 de optimización: Especifique correctamente Análisis de índice y Análisis solo de índice.

Razón 6: Uso incorrecto del índice GIN en la recuperación de texto completo

Para acelerar la búsqueda de texto, puede crear un índice GIN para la búsqueda de texto completo.

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

Al crear el índice de GIN, debe usar la versión de 2 argumentos de to_tsvector. Solo cuando la consulta también utiliza la versión de 2 argumentos y los argumentos son los mismos que en el índice de Gin, se puede invocar al índice de GIN.

La función to_tsvector() acepta uno o dos aumentos. Si se utiliza la versión de un aumento del índice, el sistema utilizará la configuración especificada por default_text_search_config de forma predeterminada. Para crear un índice, se debe usar la versión de dos aumentos o el contenido del índice puede 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 de optimización: utilice la versión de 2 argumentos de to_tsvector para la consulta y asegúrese de que los valores de los argumentos son los mismos que los del índice.

Tema principal: Uso de la base de datos