Vous n'êtes pas identifié(e).
Hello,
I have an issue with the execution plans of postgresql (15.2) when querying a partitioned table. A simple example is provided below.
I would expect both query 1 and query 2 to have the same execution plans but query 1 does scan all the partitions of table_part while the join clause on the partition key should be enough to access only the partition table_part_1 that has id_ref = 1.
Can you provide some help and insights to understand and resolve the issue ?
-- create the tables and partitions
CREATE TABLE IF NOT EXISTS table_ref (
name varchar(10),
id int4 NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS table_part (
id_ref int4 NOT NULL,
item varchar(10),
PRIMARY KEY (id_ref,item)
) PARTITION BY LIST (id_ref);
CREATE TABLE table_part_1 PARTITION OF table_part FOR VALUES IN (1);
CREATE TABLE table_part_2 PARTITION OF table_part FOR VALUES IN (2);
CREATE TABLE table_part_3 PARTITION OF table_part FOR VALUES IN (3);
-- insert some values
INSERT INTO table_ref (name, id) values ('ref_1',1);
INSERT INTO table_ref (name, id) values ('ref_2',2);
INSERT INTO table_ref (name, id) values ('ref_3',3);
INSERT INTO table_part (id_ref,item) values (1,'item_1');
INSERT INTO table_part (id_ref,item) values (2,'item_2');
INSERT INTO table_part (id_ref,item) values (3,'item_3');
-- query 1
SELECT tp.*
FROM table_part tp
JOIN table_ref tr ON tp.id_ref = tr.id
WHERE tr.name = 'ref_1';
/*
QUERY PLAN |
--------------------------------------------------------------------------------+
Hash Join (cost=24.57..115.94 rows=18 width=42) |
Hash Cond: (tp.id_ref = tr.id) |
-> Append (cost=0.00..82.20 rows=3480 width=42) |
-> Seq Scan on table_part_1 tp_1 (cost=0.00..21.60 rows=1160 width=42)|
-> Seq Scan on table_part_2 tp_2 (cost=0.00..21.60 rows=1160 width=42)|
-> Seq Scan on table_part_3 tp_3 (cost=0.00..21.60 rows=1160 width=42)|
-> Hash (cost=24.50..24.50 rows=6 width=4) |
-> Seq Scan on table_ref tr (cost=0.00..24.50 rows=6 width=4) |
Filter: ((name)::text = 'ref_1'::text) |
*/
-- query 2
SELECT tp.*
FROM table_part tp
WHERE tp.id_ref = 1;
/*
QUERY PLAN |
------------------------------------------------------------------------------+
Bitmap Heap Scan on table_part_1 tp (cost=4.20..13.67 rows=6 width=42) |
Recheck Cond: (id_ref = 1) |
-> Bitmap Index Scan on table_part_1_pkey (cost=0.00..4.20 rows=6 width=0)|
Index Cond: (id_ref = 1) |
*/Hors ligne
Bonjour,
Tout d'abord c'est un forum spécifiquement français 
Ensuite concernant votre question, les 2 requêtes ne sont pas équivalentes car il n'y a pas de contrainte de clé étrangère entre les deux tables, la première requête doit donc vérifier qu'il existe bien un enregistrement dans table_part pour chaque ligne de table_ref alors que la 2nde requête renverra des enregistrements potentiellement "orphelins". Vous pouvez supprimer la ligne dans table_ref pour id = 1 et vous verrez des résultats différents pour chaque requête.
Julien.
https://rjuju.github.io/
Hors ligne
Bonjour,
Merci pour la réponse et désolé pour le post en anglais.
Quant au problème j'ai essayé d'ajouter une foreign key mais cela ne change rien.
Ce que je ne comprends pas c'est pourquoi la première requête a besoin de scan toutes les partitions alors que la clé de partition est fournie implicitement via la jointure, j'aimerais savoir comment mieux écrire mes requêtes pour éviter ce comportement.
Hors ligne
Bonjour,
Merci pour la réponse et désolé pour le post en anglais.
Quant au problème j'ai essayé d'ajouter une foreign key mais cela ne change rien.
Ce que je ne comprends pas c'est pourquoi la première requête a besoin de scan toutes les partitions alors que la clé de partition est fournie implicitement via la jointure, j'aimerais savoir comment mieux écrire mes requêtes pour éviter ce comportement.
Bonjour.
Vous avez dans votre table_ref des valeurs qui 'pointent' sur les 3 partitions, donc de toute façon, il devra aller chercher dans les 3 partitions.
ça vous donne quoi :
select pg_relation_size(relname::text) from pg_stat_user_tables where relname like 'table_part_%';et
select n_live_tup,n_dead_tup from pg_stat_user_tables where relname like 'table_part_%';??
Hors ligne
Quant au problème j'ai essayé d'ajouter une foreign key mais cela ne change rien.
Ce que je ne comprends pas c'est pourquoi la première requête a besoin de scan toutes les partitions alors que la clé de partition est fournie implicitement via la jointure, j'aimerais savoir comment mieux écrire mes requêtes pour éviter ce comportement.
Non, vous ne fournissez pas de clé de partition implicitement. Votre modèle de données ne garantit absolument pas l'unicité sur table_ref.name, donc 'ref_1' pourrait tout à fait pointer vers des enregistrements dans toutes les partitions de table_part.
Maintenant, même si vous aviez une contrainte d'unicité sur table_ref.name, postgres continuerait tout de même à chercher dans toutes les partitions car le planificateur n'a aucune idée de la valeur de table_ref.id pour un table_ref.name donné. Mais même s'il le savait il ne pourrait pas l'utiliser car il n'y a aucune garantie que la planification et l'exécution se fasse avec le même snapshot, voire la même transaction.
À priori la seule possibilité serait d'avoir du "runtime partition pruning", donc d'avoir le même plan mais avec une partie du plan non exécutée durant l'exécution de la requête, mais ce cas n'est pas géré j'imagine du fait du peu de gain à espérer par rapport au surcout durant la planification et au faible nombre de cas où l'optimisation serait possible (uniquement en cas d'opérateur d'égalité sur la colonne en question). En effet la bonne pratique est de créer un index sur les champ des clé étrangère, et même si chercher une valeur non existante dans un index n'est pas gratuit c'est quand même relativement peu couteux.
Julien.
https://rjuju.github.io/
Hors ligne
Merci pour vos réponses:
@herve:
select pg_relation_size(relname::text) from pg_stat_user_tables where relname like 'table_part_%';
relname |pg_relation_size|
------------+----------------+
table_part_1| 4431872|
table_part_2| 4431872|
table_part_3| 4431872|Et
select n_live_tup,n_dead_tup from pg_stat_user_tables where relname like 'table_part_%';
relname |n_live_tup|n_dead_tup|
------------+----------+----------+
table_part_1| 0| 0|
table_part_2| 0| 0|
table_part_3| 0| 0|Si je comprends bien cela signifie que toutes les partitions sont a priori équivalentes c'est bien cela ? (j'ai rentré la même quantité de données dans chacune: 10k lignes), ce qui explique les plans d'execution.
@rjuju
Je crois que j'ai mieux compris et qu'en indexant la table_ref sur la colonne name (ou sur id si j'applique un prédicat dessus) je tombe dans un cas similaire au 2ème que vous décrivez, le plan d'execution annonce qu'il va scan toutes les partitions en revanche un analyze donne bien le fonctionnement que j'attends et les scans sur les partitions qui ne sont pas dans le résultat de la première requête ne sont pas executés (même s'il sont annoncés au même poids dans l'explain plan):
QUERY PLAN |
-------------------------------------------------------------------------------------------------------------------------------------------+
Nested Loop (cost=13.24..744.53 rows=260 width=222) (actual time=3.704..29.843 rows=100000 loops=1) |
-> Index Only Scan using table_ref_pkey on table_ref tr (cost=0.15..8.17 rows=1 width=4) (actual time=0.022..0.025 rows=1 loops=1) |
Index Cond: (name = 'ref_1'::text) |
Heap Fetches: 1 |
-> Append (cost=13.09..733.75 rows=261 width=222) (actual time=3.679..19.880 rows=100000 loops=1) |
-> Bitmap Heap Scan on table_part_1 tp_1 (cost=13.09..244.15 rows=87 width=222) (actual time=3.674..12.574 rows=100000 loops=1) |
Recheck Cond: (id_ref = tr.id) |
Heap Blocks: exact=541 |
-> Bitmap Index Scan on table_part_1_pkey (cost=0.00..13.06 rows=87 width=0) (actual time=3.604..3.604 rows=100000 loops=1)|
Index Cond: (id_ref = tr.id) |
-> Bitmap Heap Scan on table_part_2 tp_2 (cost=13.09..244.15 rows=87 width=222) (never executed) |
Recheck Cond: (id_ref = tr.id) |
-> Bitmap Index Scan on table_part_2_pkey (cost=0.00..13.06 rows=87 width=0) (never executed) |
Index Cond: (id_ref = tr.id) |
-> Bitmap Heap Scan on table_part_3 tp_3 (cost=13.09..244.15 rows=87 width=222) (never executed) |
Recheck Cond: (id_ref = tr.id) |
-> Bitmap Index Scan on table_part_3_pkey (cost=0.00..13.06 rows=87 width=0) (never executed) |
Index Cond: (id_ref = tr.id) |
Planning Time: 0.204 ms |
Execution Time: 33.889 ms |L'ajout ou non d'une clé étrangère entre les tables ne change pas ce comportement.
Je pense que j'ai les éléments de réponse pour ma question et que je vais clore le post, merci pour votre aide.
Hors ligne
Merci pour vos réponses:
@herve:select n_live_tup,n_dead_tup from pg_stat_user_tables where relname like 'table_part_%'; relname |n_live_tup|n_dead_tup| ------------+----------+----------+ table_part_1| 0| 0| table_part_2| 0| 0| table_part_3| 0| 0|Si je comprends bien cela signifie que toutes les partitions sont a priori équivalentes c'est bien cela ? (j'ai rentré la même quantité de données dans chacune: 10k lignes), ce qui explique les plans d'execution.
Oui, mais à ce que je comprends, il n'y a jamais eu de VACUUM ANALYZE effectué (j'ai pas vérifié, mais je pense que cela devrait figurer dans les stats des partitions), par conséquent un scan de l'index entraînera forcément une lecture de la page de données pointée par les feuilles de l'index, donc autant effactuer directement un Seq Scan sur les pages de données.
Effectuez un VACUUM ANALYZE sur la table, et refaites un EXPLAIN ANALYZE histoire de voir si le plan d'exécution change.
Hors ligne
L'ajout ou non d'une clé étrangère entre les tables ne change pas ce comportement.
Je déconseille fortement l'utilisation de clés étrangères sur les tables partitionnées PostgreSQL.
D'abord parce qu'un simple ATTACH peut prendre des heures, voire des jours selon la volumétrie et rendre la table inexploitable pendant ce temps (exclusive shared lock).
Ensuite, il y a manifestement des bugs sur les FK de tables partitionnées lors des ATTACH/DETACH(*) qui AMHA ne seront pas résolus de sitôt(**).
Au boulot, en solution de contournement en attendant mieux, on fait une vérification d'intégrité avant de faire le ATTACH ; et on remplace la FK par un trigger "maison" qui fait le check d'intégrité lors des insert/update.
(*) Ref: https://www.postgresql.org/message-id/C … .gmail.com
(**) Ref : https://www.postgresql.org/message-id/C … .gmail.com
Dernière modification par herve.lefebvre (04/04/2023 19:57:44)
Hors ligne