Configuración de la partición de tablas Captura de planos para la partición de tablas Aplicación de un plan de partición de tablas Convención de nomenclatura

Compatibilidad con partición de tablas

La administración de planes de consulta (QPM) de Aurora PostgreSQL admite particiones de tablas declarativas en las siguientes versiones:

Versión 15.3 y versiones posteriores a la 15
Versión 14.8 y versiones posteriores a la 14
Versión 13.11 y versiones posteriores a la 13

Para obtener más información, consulte Table Partitioning.

Temas

Configuración de la partición de tablas
Captura de planos para la partición de tablas
Aplicación de un plan de partición de tablas
Convención de nomenclatura

Configuración de la partición de tablas

Para configurar la partición de tablas en QPMde Aurora PostgreSQL, haga lo siguiente:

Establezca apg_plan_mgmt.plan_hash_version en 3 o más en el grupo de parámetros del clúster de base de datos.
Navegue hasta una base de datos que utilice QPM y tenga entradas en la vista apg_plan_mgmt.dba_plans.
Llame a apg_plan_mgmt.validate_plans('update_plan_hash') para actualizar el valor plan_hash en la tabla de planes.
Repita los pasos 2 y 3 para todas las bases de datos con administración del plan de consultas habilitada que tengan entradas e la vista apg_plan_mgmt.dba_plans.

Para obtener más información sobre estos parámetros, consulte Referencia de parámetros para la administración de planes de consultas de Aurora PostgreSQL.

Captura de planos para la partición de tablas

En QPM, los diferentes planes se distinguen por su valor de plan_hash. Para entender cómo cambia plan_hash, primero hay que entender planes similares.

La combinación de métodos de acceso, nombres de índice sin dígitos y nombres de particiones sin dígitos, acumulados en el nivel del nodo Append, debe ser constante para que los planes se consideren iguales. Las particiones específicas a las que se accede en los planos no son significativas. En el ejemplo siguiente, se crea una tabla tbl_a con 4 particiones.


postgres=>create table tbl_a(i int, j int, k int, l int, m int) partition by range(i);
CREATE TABLE
postgres=>create table tbl_a1 partition of tbl_a for values from (0) to (1000);
CREATE TABLE
postgres=>create table tbl_a2 partition of tbl_a for values from (1001) to (2000);
CREATE TABLE
postgres=>create table tbl_a3 partition of tbl_a for values from (2001) to (3000);
CREATE TABLE
postgres=>create table tbl_a4 partition of tbl_a for values from (3001) to (4000);
CREATE TABLE
postgres=>create index t_i on tbl_a using btree (i);
CREATE INDEX
postgres=>create index t_j on tbl_a using btree (j);
CREATE INDEX
postgres=>create index t_k on tbl_a using btree (k);
CREATE INDEX

Los siguientes planes se consideran iguales porque se utiliza un único método de examen para examinar tbl_a independientemente del número de particiones que revise la consulta.


postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 999 and j < 9910 and k > 50;
            
                        QUERY PLAN
-------------------------------------------------------------------
Seq Scan on tbl_a1 tbl_a
    Filter: ((i >= 990) AND (i <= 999) AND (j < 9910) AND (k > 50))
SQL Hash: 1553185667, Plan Hash: -694232056
(3 rows)


postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 1100 and j < 9910 and k > 50;
            
                        QUERY PLAN
-------------------------------------------------------------------
Append
    ->  Seq Scan on tbl_a1 tbl_a_1
            Filter: ((i >= 990) AND (i <= 1100) AND (j < 9910) AND (k > 50))
    ->  Seq Scan on tbl_a2 tbl_a_2
            Filter: ((i >= 990) AND (i <= 1100) AND (j < 9910) AND (k > 50))
    SQL Hash: 1553185667, Plan Hash: -694232056
    (6 rows)


postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 2100 and j < 9910 and k > 50;
            
                QUERY PLAN
--------------------------------------------------------------------------
 Append
   ->  Seq Scan on tbl_a1 tbl_a_1
         Filter: ((i >= 990) AND (i <= 2100) AND (j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a2 tbl_a_2
         Filter: ((i >= 990) AND (i <= 2100) AND (j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a3 tbl_a_3
         Filter: ((i >= 990) AND (i <= 2100) AND (j < 9910) AND (k > 50))
 SQL Hash: 1553185667, Plan Hash: -694232056
(8 rows)

Los siguientes 3 planes también se consideran iguales porque, en el nivel principal, los métodos de acceso, los nombres de índice sin dígitos y los nombres de las particiones sin dígitos son SeqScan tbl_a, IndexScan (i_idx) tbl_a.


postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 1100 and j < 9910 and k > 50;
            
                                QUERY PLAN
--------------------------------------------------------------------------
 Append
   ->  Seq Scan on tbl_a1 tbl_a_1
         Filter: ((i >= 990) AND (i <= 1100) AND (j < 9910) AND (k > 50))
   ->  Index Scan using tbl_a2_i_idx on tbl_a2 tbl_a_2
         Index Cond: ((i >= 990) AND (i <= 1100))
         Filter: ((j < 9910) AND (k > 50))
 SQL Hash: 1553185667, Plan Hash: -993736942
(7 rows)


postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 2100 and j < 9910 and k > 50;
            
                                QUERY PLAN
--------------------------------------------------------------------------
 Append
   ->  Index Scan using tbl_a1_i_idx on tbl_a1 tbl_a_1
         Index Cond: ((i >= 990) AND (i <= 2100))
         Filter: ((j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a2 tbl_a_2
         Filter: ((i >= 990) AND (i <= 2100) AND (j < 9910) AND (k > 50))
   ->  Index Scan using tbl_a3_i_idx on tbl_a3 tbl_a_3
         Index Cond: ((i >= 990) AND (i <= 2100))
         Filter: ((j < 9910) AND (k > 50))
 SQL Hash: 1553185667, Plan Hash: -993736942
(10 rows)


postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 3100 and j < 9910 and k > 50;
            
                                QUERY PLAN
--------------------------------------------------------------------------
 Append
   ->  Seq Scan on tbl_a1 tbl_a_1
         Filter: ((i >= 990) AND (i <= 3100) AND (j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a2 tbl_a_2
         Filter: ((i >= 990) AND (i <= 3100) AND (j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a3 tbl_a_3
         Filter: ((i >= 990) AND (i <= 3100) AND (j < 9910) AND (k > 50))
   ->  Index Scan using tbl_a4_i_idx on tbl_a4 tbl_a_4
         Index Cond: ((i >= 990) AND (i <= 3100))
         Filter: ((j < 9910) AND (k > 50))
 SQL Hash: 1553185667, Plan Hash: -993736942
(11 rows)

Independientemente del diferente orden y número de ocurrencias en las particiones secundarias, los métodos de acceso, los nombres de índice sin dígitos y los nombres de las particiones sin dígitos son constantes en el nivel principal para cada uno de los planes anteriores.

Sin embargo, los planes se considerarán diferentes si se cumple alguna de las siguientes condiciones:

Se utiliza algún método de acceso adicional en el plan.


postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 2100 and j < 9910 and k > 50;
                    
                                QUERY PLAN
--------------------------------------------------------------------------
 Append
   ->  Seq Scan on tbl_a1 tbl_a_1
         Filter: ((i >= 990) AND (i <= 2100) AND (j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a2 tbl_a_2
         Filter: ((i >= 990) AND (i <= 2100) AND (j < 9910) AND (k > 50))
   ->  Bitmap Heap Scan on tbl_a3 tbl_a_3
         Recheck Cond: ((i >= 990) AND (i <= 2100))
         Filter: ((j < 9910) AND (k > 50))
         ->  Bitmap Index Scan on tbl_a3_i_idx
               Index Cond: ((i >= 990) AND (i <= 2100))
 SQL Hash: 1553185667, Plan Hash: 1134525070
(11 rows)

Se deja de utilizar alguno de los métodos de acceso del plan.


postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 1100 and j < 9910 and k > 50;
                    
                               QUERY PLAN
--------------------------------------------------------------------------
 Append
   ->  Seq Scan on tbl_a1 tbl_a_1
         Filter: ((i >= 990) AND (i <= 1100) AND (j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a2 tbl_a_2
         Filter: ((i >= 990) AND (i <= 1100) AND (j < 9910) AND (k > 50))
 SQL Hash: 1553185667, Plan Hash: -694232056
(6 rows)

Se cambia el índice asociado a un método de índice.


postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 1100 and j < 9910 and k > 50;
                    
                             QUERY PLAN
--------------------------------------------------------------------------
 Append
   ->  Seq Scan on tbl_a1 tbl_a_1
         Filter: ((i >= 990) AND (i <= 1100) AND (j < 9910) AND (k > 50))
   ->  Index Scan using tbl_a2_j_idx on tbl_a2 tbl_a_2
         Index Cond: (j < 9910)
         Filter: ((i >= 990) AND (i <= 1100) AND (k > 50))
 SQL Hash: 1553185667, Plan Hash: -993343726
(7 rows)

Aplicación de un plan de partición de tablas

Los planes aprobados para tablas particionadas se aplican mediante correspondencia posicional. Los planes no son específicos de las particiones y se pueden aplicar a particiones distintas de los planes a los que se hace referencia en la consulta original. Los planes también pueden aplicarse a las consultas que accedan a un número de particiones diferente al esquema original aprobado.

Por ejemplo, si el esquema aprobado es para el siguiente plan:


postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 2100 and j < 9910 and k > 50;
            
                                QUERY PLAN
--------------------------------------------------------------------------
 Append
   ->  Index Scan using tbl_a1_i_idx on tbl_a1 tbl_a_1
         Index Cond: ((i >= 990) AND (i <= 2100))
         Filter: ((j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a2 tbl_a_2
         Filter: ((i >= 990) AND (i <= 2100) AND (j < 9910) AND (k > 50))
   ->  Index Scan using tbl_a3_i_idx on tbl_a3 tbl_a_3
         Index Cond: ((i >= 990) AND (i <= 2100))
         Filter: ((j < 9910) AND (k > 50))   
 SQL Hash: 1553185667, Plan Hash: -993736942
(10 rows)

A continuación, este plan también se puede aplicar a las consultas SQL que hagan referencia a 2, 4 o más particiones. Los posibles planes que podrían surgir de estos escenarios para el acceso a 2 y 4 particiones son:


postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 1100 and j < 9910 and k > 50;
            
                                QUERY PLAN
----------------------------------------------------------------------------------
 Append
   ->  Index Scan using tbl_a1_i_idx on tbl_a1 tbl_a_1
         Index Cond: ((i >= 990) AND (i <= 1100))
         Filter: ((j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a2 tbl_a_2
         Filter: ((i >= 990) AND (i <= 1100) AND (j < 9910) AND (k > 50))
 Note: An Approved plan was used instead of the minimum cost plan. 
 SQL Hash: 1553185667, Plan Hash: -993736942, Minimum Cost Plan Hash: -1873216041
(8 rows)


postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 3100 and j < 9910 and k > 50;
            
                                QUERY PLAN
--------------------------------------------------------------------------
 Append
   ->  Index Scan using tbl_a1_i_idx on tbl_a1 tbl_a_1
         Index Cond: ((i >= 990) AND (i <= 3100))
         Filter: ((j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a2 tbl_a_2
         Filter: ((i >= 990) AND (i <= 3100) AND (j < 9910) AND (k > 50))
   ->  Index Scan using tbl_a3_i_idx on tbl_a3 tbl_a_3
         Index Cond: ((i >= 990) AND (i <= 3100))
         Filter: ((j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a4 tbl_a_4
         Filter: ((i >= 990) AND (i <= 3100) AND (j < 9910) AND (k > 50))
 Note: An Approved plan was used instead of the minimum cost plan.
 SQL Hash: 1553185667, Plan Hash: -993736942, Minimum Cost Plan Hash: -1873216041 
(12 rows)


postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 3100 and j < 9910 and k > 50;
            
                                QUERY PLAN
----------------------------------------------------------------------------------
 Append
   ->  Index Scan using tbl_a1_i_idx on tbl_a1 tbl_a_1
         Index Cond: ((i >= 990) AND (i <= 3100))
         Filter: ((j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a2 tbl_a_2
         Filter: ((i >= 990) AND (i <= 3100) AND (j < 9910) AND (k > 50))
   ->  Index Scan using tbl_a3_i_idx on tbl_a3 tbl_a_3
         Index Cond: ((i >= 990) AND (i <= 3100))
         Filter: ((j < 9910) AND (k > 50))
   ->  Index Scan using tbl_a4_i_idx on tbl_a4 tbl_a_4
         Index Cond: ((i >= 990) AND (i <= 3100))
         Filter: ((j < 9910) AND (k > 50))
 Note: An Approved plan was used instead of the minimum cost plan.
 SQL Hash: 1553185667, Plan Hash: -993736942, Minimum Cost Plan Hash: -1873216041
(14 rows)

Considere otro plan aprobado con diferentes métodos de acceso para cada partición:


postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 2100 and j < 9910 and k > 50;
            
                                QUERY PLAN
--------------------------------------------------------------------------
 Append
   ->  Index Scan using tbl_a1_i_idx on tbl_a1 tbl_a_1
         Index Cond: ((i >= 990) AND (i <= 2100))
         Filter: ((j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a2 tbl_a_2
         Filter: ((i >= 990) AND (i <= 2100) AND (j < 9910) AND (k > 50))
   ->  Bitmap Heap Scan on tbl_a3 tbl_a_3
         Recheck Cond: ((i >= 990) AND (i <= 2100))
         Filter: ((j < 9910) AND (k > 50))
         ->  Bitmap Index Scan on tbl_a3_i_idx
               Index Cond: ((i >= 990) AND (i <= 2100))
 SQL Hash: 1553185667, Plan Hash: 2032136998
(12 rows)

En este caso, cualquier plan que lea de dos particiones no podría aplicarse. A menos que se puedan utilizar todas las combinaciones (método de acceso, nombre de índice) del plan aprobado, no se puede aplicar el plan. Por ejemplo, los siguientes planes tienen distintos hashes de plan y el plan aprobado no se puede aplicar en estos casos:


postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 1900 and j < 9910 and k > 50;
            
                              QUERY PLAN
-------------------------------------------------------------------------
 Append
   ->  Bitmap Heap Scan on tbl_a1 tbl_a_1
         Recheck Cond: ((i >= 990) AND (i <= 1900))
         Filter: ((j < 9910) AND (k > 50))
         ->  Bitmap Index Scan on tbl_a1_i_idx
               Index Cond: ((i >= 990) AND (i <= 1900))
   ->  Bitmap Heap Scan on tbl_a2 tbl_a_2
         Recheck Cond: ((i >= 990) AND (i <= 1900))
         Filter: ((j < 9910) AND (k > 50))
         ->  Bitmap Index Scan on tbl_a2_i_idx
               Index Cond: ((i >= 990) AND (i <= 1900))
  Note: This is not an Approved plan.  No usable Approved plan was found.
  SQL Hash: 1553185667, Plan Hash: -568647260
(13 rows)


postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 1900 and j < 9910 and k > 50;
            
                              QUERY PLAN
--------------------------------------------------------------------------
 Append
   ->  Index Scan using tbl_a1_i_idx on tbl_a1 tbl_a_1
         Index Cond: ((i >= 990) AND (i <= 1900))
         Filter: ((j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a2 tbl_a_2
         Filter: ((i >= 990) AND (i <= 1900) AND (j < 9910) AND (k > 50))
 Note: This is not an Approved plan.  No usable Approved plan was found.
 SQL Hash: 1553185667, Plan Hash: -496793743
(8 rows)

Convención de nomenclatura

Para que QPM aplique un plan con tablas particionadas declarativas, debe seguir reglas de nomenclatura específicas para las tablas principales, las particiones de tablas y los índices:

Nombres de las tablas principales: estos nombres deben diferir en letras de alfabeto o caracteres especiales y no solo en dígitos. Por ejemplo, tA, tB y tC son nombres aceptables para tablas principales independientes, mientras que t1, t2 y t3 no lo son.
Nombres de tablas de particiones individuales: las particiones del mismo elemento principal solo deben diferir entre sí en dígitos. Por ejemplo, los nombres de partición aceptables de tA podrían ser tA1, tA2 o t1A, t2A o incluso varios dígitos.

Cualquier otra diferencia (letras, caracteres especiales) no garantizará la aplicación del plan.
Nombres de índice: en la jerarquía de tablas de particiones, asegúrese de que todos los índices tengan nombres únicos. Esto significa que las partes no numéricas de los nombres deben ser diferentes. Por ejemplo, si tiene una tabla particionada nombrada tA con un nombre de índice tA_col1_idx1, no puede tener otro índice nombrado tA_col1_idx2. Sin embargo, puede tener un índice llamado tA_a_col1_idx2 porque la parte no numérica del nombre es única. Esta regla se aplica a los índices creados tanto en la tabla principal como en las tablas de particiones individuales.

El incumplimiento de las convenciones de nomenclatura anteriores puede provocar que los planes aprobados no se apliquen. El siguiente ejemplo ilustra un error de ejecución de este tipo:


postgres=>create table t1(i int, j int, k int, l int, m int) partition by range(i);
CREATE TABLE
postgres=>create table t1a partition of t1 for values from (0) to (1000);
CREATE TABLE
postgres=>create table t1b partition of t1 for values from (1001) to (2000);
CREATE TABLE
postgres=>SET apg_plan_mgmt.capture_plan_baselines TO 'manual';
SET
postgres=>explain (hashes true, costs false) select count(*) from t1 where i > 0;

                            QUERY PLAN
--------------------------------------------------------------------------
 Aggregate
   ->  Append
         ->  Seq Scan on t1a t1_1
               Filter: (i > 0)
         ->  Seq Scan on t1b t1_2
               Filter: (i > 0)
 SQL Hash: -1720232281, Plan Hash: -1010664377
(7 rows)


postgres=>SET apg_plan_mgmt.use_plan_baselines TO 'on';
SET
postgres=>explain (hashes true, costs false) select count(*) from t1 where i > 1000;

                            QUERY PLAN
-------------------------------------------------------------------------
 Aggregate
   ->  Seq Scan on t1b t1
         Filter: (i > 1000)
 Note: This is not an Approved plan. No usable Approved plan was found.
 SQL Hash: -1720232281, Plan Hash: 335531806
(5 rows)

Aunque los dos planes parezcan idénticos, sus valores Plan Hash son diferentes debido a los nombres de las tablas secundarias. Los nombres de las tablas varían en caracteres alfabéticos y no solo en dígitos, lo que provoca un error de cumplimiento.

Aviso JavaScript está desactivado o no está disponible en su navegador.

Para utilizar la documentación de AWS, debe estar habilitado JavaScript. Para obtener más información, consulte las páginas de ayuda de su navegador.

Convenciones del documento

Captura de planes de ejecución de Aurora PostgreSQL en réplicas

Uso de extensiones y contenedores de datos externos