Configuration d'une partition de table Capture de plans pour une partition de table Application d'un plan de partition de tables Convention de nommage

Prise en charge de la partition de table

Aurora PostgreSQL Query Plan Management (QPM) prend en charge le partitionnement déclaratif des tables dans les versions suivantes :

Version 15.3 et versions 15 ultérieures
Version 14.8 et versions 14 ultérieures
Version 13.11 et versions 13 ultérieures

Pour plus d'informations, consultez Partitionnement de table.

Rubriques

Configuration d'une partition de table
Capture de plans pour une partition de table
Application d'un plan de partition de tables
Convention de nommage

Configuration d'une partition de table

Pour configurer une partition de table dans la gestion de plans de requêtes Aurora PostgreSQL, procédez comme suit :

Définissez apg_plan_mgmt.plan_hash_version sur 3 ou plus dans le groupe de paramètres du cluster de bases de données.
Accédez à une base de données qui utilise la gestion de plans de requêtes et qui a des entrées dans la vue apg_plan_mgmt.dba_plans.
Appelez apg_plan_mgmt.validate_plans('update_plan_hash') pour mettre à jour la valeur plan_hash dans la table des plans.
Répétez les étapes 2 et 3 pour toutes les bases de données dont la gestion de plans de requêtes est activée et qui a des entrées dans la vue apg_plan_mgmt.dba_plans.

Pour plus d'informations sur ces paramètres, consultez Référence du paramètre de gestion du plan de requête Aurora PostgreSQL.

Capture de plans pour une partition de table

Dans la gestion de plans de requêtes, des plans différents se distinguent par leur valeur de plan_hash. Pour comprendre comment le paramètre plan_hash change, vous devez d'abord comprendre des types de plans similaires.

La combinaison des méthodes d'accès, des noms d'index et des noms de partition sans chiffres, cumulée au niveau du nœud Append doit être constante pour que les plans soient considérés comme identiques. Les partitions spécifiques accessibles dans les plans ne sont pas significatives. Dans l'exemple suivant, une table tbl_a est créée avec 4 partitions.


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

Les plans suivants sont considérés comme identiques car une seule méthode d'analyse est utilisée pour scanner tbl_a quel que soit le nombre de partitions interrogées par la requête.


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)

Les 3 plans suivants sont également considérés comme identiques car, au niveau parent, les méthodes d'accès, les noms d'index et les noms de partition sans chiffres sont 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)

Quels que soient l'ordre et le nombre d'occurrences dans les partitions enfants, les méthodes d'accès, les noms d'index sans chiffres et les noms de partition sans chiffres sont constants au niveau parent pour chacun des plans ci-dessus.

Toutefois, les plans sont considérés comme différents si l'une des conditions suivantes est remplie :

Toutes les méthodes d'accès supplémentaires sont utilisées dans le 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)

Une ou plusieurs méthodes d'accès dans le plan ne sont plus utilisées.


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)

L'index associé à une méthode d'indexation est modifié.


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)

Application d'un plan de partition de tables

Les plans approuvés pour les tables partitionnées sont appliqués avec une correspondance de position. Les plans ne sont pas spécifiques aux partitions et peuvent être appliqués à des partitions autres que les plans référencés dans la requête d'origine. Les plans peuvent également être appliqués pour des requêtes accédant à un nombre différent de partitions de celui du plan approuvé d'origine.

Par exemple, si le plan approuvé se rapporte au plan suivant :


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)

Ensuite, ce plan peut également être appliqué aux requêtes SQL faisant référence à 2 ou 4 partitions, ou encore plus. Les plans possibles qui pourraient découler de ces scénarios pour l'accès à 2 et 4 partitions sont les suivants :


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)

Envisagez un autre plan approuvé avec des méthodes d'accès différentes pour chaque partition :


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)

Dans ce cas, tout plan qui lit à partir de deux partitions ne serait pas appliqué. À moins que toutes les combinaisons (méthode d'accès, nom d'index) du plan approuvé soient utilisables, le plan ne peut pas être appliqué. Par exemple, les plans suivants ont des hachages différents et le plan approuvé ne peut pas être appliqué dans les cas suivants :


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)

Convention de nommage

Pour que QPM applique un plan avec des tables partitionnées déclaratives, vous devez suivre des règles de dénomination spécifiques pour les tables parents, les partitions de table et les index :

Noms des tables mères : ces noms doivent différer par des alphabets ou des caractères spéciaux, et non par de simples chiffres. Par exemple, tA, tB et tC sont des noms acceptables pour des tables parentes distinctes, alors que t1, t2 et t3 ne le sont pas.
Noms de tables de partitions individuels — Les partitions d'un même parent ne doivent différer les unes des autres que par des chiffres. Par exemple, les noms de partition acceptables pour tA peuvent être tA1, tA2 ou t1A, t2A ou même plusieurs chiffres.

Toute autre différence (lettres, caractères spéciaux) ne garantit pas l'application du plan.
Noms d'index : dans la hiérarchie de la table de partition, assurez-vous que tous les index ont des noms uniques. Cela signifie que les parties non numériques des noms doivent être différentes. Par exemple, si vous avez une table partitionnée nommée tA avec un index nommétA_col1_idx1, aucun autre index ne peut être nommétA_col1_idx2. Cependant, vous pouvez faire appeler un index tA_a_col1_idx2 car la partie non numérique du nom est unique. Cette règle s'applique aux index créés à la fois sur la table parent et sur les tables de partition individuelles.

Le non-respect des conventions de nommage ci-dessus peut entraîner l'échec de l'application des plans approuvés. L'exemple suivant illustre un tel échec d'application :


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)

Même si les deux plans peuvent sembler identiques, leurs Plan Hash valeurs sont différentes en raison des noms des tables enfants. Les noms des tables varient en fonction des caractères alphabétiques au lieu de simples chiffres, ce qui entraîne un échec de l'application.

Avertissement JavaScript est désactivé ou n'est pas disponible dans votre navigateur.

Pour que vous puissiez utiliser la documentation AWS, Javascript doit être activé. Vous trouverez des instructions sur les pages d'aide de votre navigateur.

Conventions de rédaction

Capture de plans d'exécution Aurora PostgreSQL dans des réplicas

Utilisation d'extensions avec encapsuleurs de données externes