“Pruning” de partitions sous PostgreSQL ou comment bien élaguer !

La possibilité de partitionnement natif de tables a été introduite en version 10 de PostgreSQL. C’était possible auparavant mais par le biais de la création de tables par héritage (clause INHERIT). Avec l’exécution en parallélisation de requêtes SQL (introduite en v9.6), cela a constitué une évolution majeure pour adresser les problématiques d’accès et de gestion de données très volumineuses.

je ne reviendrai pas sur la méthode pour partitionner des tables avec PostgreSQL, et les évolutions apportées à cette fonctionnalité depuis la v10. Je vous renvoie pour cela aux articles PostgreSQL : évolution du partitionnement de 9.6 à 12 (1/2) et PostgreSQL : évolution du partitionnement de 9.6 à 12 (2/2) sur le sujet.

Avant de se lancer tête baissée dans le partitionnement des plus grosses tables de sa base de données, il faut considérer ces facteurs essentiels pour faire les bons choix :

• Quelle sera la clé retenue pour partitionner ma(mes) table(s) ?
• Comment m’assurer que mon application saura tirer le plus grand bénéfice de ce partitionnement ?

La clé sera naturelle si elle suit la logique dans l’organisation des données et si elle correspond à un critère majeur et répété dans les prédicats des requêtes des utilisateurs.

La clé peut être une colonne de type date ou timestamp correspondant à la date/heure de l’événement enregistré dans la table ou une autre clé qui suivrait la logique de l’organisation (clé d’une filiale ou d’une zone géographique suivant une logique d’accès des utilisateurs à leurs données locales). Le partitionnement étant possible sur plusieurs niveaux, on peut aussi partitionner et sous-partitionner suivant 2 clés.

Une fois les clés retenues et une ou plusieurs tables migrées en tables partitionnées, l’autre question majeure consiste à savoir si mes requêtes sauront tirer le bénéfice de ce partitionnement.

En effet, à travers le plan d’exécution de la requête déterminé par le “planner” de PostgreSQL , il s’agit de pouvoir lire les seules partitions utiles et pas la totalité de la table partitionnée (élimination de partitions  ou “pruning” qu’on peut traduire littéralement par “élagage”). L’écriture des requêtes doit permettre à PostgreSQL de faire cette élimination dès l’élaboration du plan d’exécution de la requête, sinon à l’exécution de celle-ci (possibilité de “pruning” pendant l’exécution introduite en version 11).

Prenons un exemple sur la toute dernière version 13 de PostgreSQL, avec le schéma “pgbench” dans lequel j’ai  créé une table partitionnée  “pgbench_history_part” à partir des données de pgbench_history suivant deux clés:

• 10 partitions par LIST sur bid (clé “branche id” référençant les branches de pgbench_branches), une partition par “branche” (bid de 1 à 10)
• puis des sous partitions par RANGE sur la date mtime.

pgbench=# \d+ pgbench_history_part
Partitioned table "public.pgbench_history_part"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
tid | integer | | | | plain | |
bid | integer | | | | plain | |
aid | integer | | | | plain | |
delta | integer | | | | plain | |
mtime | timestamp without time zone | | | | plain | |
filler | character(22) | | | | extended | |
Partition key: LIST (bid)
Indexes:
"pgbench_history_part_mtime_idx" btree (mtime)
Partitions: pgbench_history_part_1 FOR VALUES IN (1), PARTITIONED,
pgbench_history_part_10 FOR VALUES IN (10), PARTITIONED,
pgbench_history_part_2 FOR VALUES IN (2), PARTITIONED,
pgbench_history_part_3 FOR VALUES IN (3), PARTITIONED,
pgbench_history_part_4 FOR VALUES IN (4), PARTITIONED,
pgbench_history_part_5 FOR VALUES IN (5), PARTITIONED,
pgbench_history_part_6 FOR VALUES IN (6), PARTITIONED,
pgbench_history_part_7 FOR VALUES IN (7), PARTITIONED,
pgbench_history_part_8 FOR VALUES IN (8), PARTITIONED,
pgbench_history_part_9 FOR VALUES IN (9), PARTITIONED

Description d’une des 10 partitions et son sous-partitionnement par RANGE sur mtime, un RANGE correspondant à une année (données artificiellement créées pour couvrir 20 années de façon égale pour les 10 valeurs de bid):

pgbench=# \d+ pgbench_history_part_1
Partitioned table "public.pgbench_history_part_1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
tid | integer | | | | plain | |
bid | integer | | | | plain | |
aid | integer | | | | plain | |
delta | integer | | | | plain | |
mtime | timestamp without time zone | | | | plain | |
filler | character(22) | | | | extended | |
Partition of: pgbench_history_part FOR VALUES IN (1)
Partition constraint: ((bid IS NOT NULL) AND (bid = 1))
Partition key: RANGE (mtime)
Indexes:
"pgbench_history_part_1_mtime_idx" btree (mtime)
Partitions: pgbench_history_part_1_2001 FOR VALUES FROM ('2001-01-01 00:00:00') TO ('2002-01-01 00:00:00'),
pgbench_history_part_1_2002 FOR VALUES FROM ('2002-01-01 00:00:00') TO ('2003-01-01 00:00:00'),
pgbench_history_part_1_2003 FOR VALUES FROM ('2003-01-01 00:00:00') TO ('2004-01-01 00:00:00'),
pgbench_history_part_1_2004 FOR VALUES FROM ('2004-01-01 00:00:00') TO ('2005-01-01 00:00:00'),
pgbench_history_part_1_2005 FOR VALUES FROM ('2005-01-01 00:00:00') TO ('2006-01-01 00:00:00'),
pgbench_history_part_1_2006 FOR VALUES FROM ('2006-01-01 00:00:00') TO ('2007-01-01 00:00:00'),
pgbench_history_part_1_2007 FOR VALUES FROM ('2007-01-01 00:00:00') TO ('2008-01-01 00:00:00'),
pgbench_history_part_1_2008 FOR VALUES FROM ('2008-01-01 00:00:00') TO ('2009-01-01 00:00:00'),
pgbench_history_part_1_2009 FOR VALUES FROM ('2009-01-01 00:00:00') TO ('2010-01-01 00:00:00'),
pgbench_history_part_1_2010 FOR VALUES FROM ('2010-01-01 00:00:00') TO ('2011-01-01 00:00:00'),
pgbench_history_part_1_2011 FOR VALUES FROM ('2011-01-01 00:00:00') TO ('2012-01-01 00:00:00'),
pgbench_history_part_1_2012 FOR VALUES FROM ('2012-01-01 00:00:00') TO ('2013-01-01 00:00:00'),
pgbench_history_part_1_2013 FOR VALUES FROM ('2013-01-01 00:00:00') TO ('2014-01-01 00:00:00'),
pgbench_history_part_1_2014 FOR VALUES FROM ('2014-01-01 00:00:00') TO ('2015-01-01 00:00:00'),
pgbench_history_part_1_2015 FOR VALUES FROM ('2015-01-01 00:00:00') TO ('2016-01-01 00:00:00'),
pgbench_history_part_1_2016 FOR VALUES FROM ('2016-01-01 00:00:00') TO ('2017-01-01 00:00:00'),
pgbench_history_part_1_2017 FOR VALUES FROM ('2017-01-01 00:00:00') TO ('2018-01-01 00:00:00'),
pgbench_history_part_1_2018 FOR VALUES FROM ('2018-01-01 00:00:00') TO ('2019-01-01 00:00:00'),
pgbench_history_part_1_2019 FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2019-12-31 00:00:00'),
pgbench_history_part_1_2020 FOR VALUES FROM ('2020-01-01 00:00:00') TO ('2021-01-01 00:00:00')

Je n’ai créé qu’un seul index pgbench_history_part_mtime_idx sur (mtime), index propagé sur chaque partition.

Imaginons vouloir écrire un simple SELECT en jointure sur pgbench_history_part et pgbench_branches où on souhaite sélectionner les données de pgbench_history_part d’une ou plusieurs branches sans connaissance des ID de ces branches (bid) mais avec prédicat sur bbalance, colonne de la table jointe pgbench_branches,  :

select h.* from pgbench_history_part h JOIN pgbench_branches b ON h.bid=b.bid 
WHERE b.bbalance=-1060874;

Le plan d’exécution (EXPLAIN ANALYZE) montre que toutes les partitions ont été lues, avec une durée d’exécution de 279 secondes  pour rows=10016860 lignes retournées ! Nous remarquons au passage que ce nombre de ligne réellement lues (actual rows) est 2 fois supérieur à l’estimation rows=500515.

pgbench=# explain analyse select h.* from pgbench_history_part h JOIN pgbench_branches b 
ON h.bid=b.bid WHERE b.bbalance=-1060874;

                                                                               QUERY PLAN                                                                           
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1014.14..1426216.66 rows=500515 width=116) (actual time=227398.538..269726.289 rows=10016860 loops=1)
   Workers Planned: 2
   Workers Launched: 2
     Hash Join  (cost=14.14..1375165.16 rows=208548 width=116) (actual time=151981.556..188122.355 rows=3338953 loops=3)
         Hash Cond: (h.bid = b.bid)
           Parallel Append  (cost=0.00..1263343.33 rows=41709561 width=116) (actual time=0.700..134063.520 rows=33367642 loops=3)
                 Parallel Seq Scan on pgbench_history_part_8_2001 h_141  (cost=0.00..5284.98 rows=208998 width=116) (actual time=0.932..1047.692 rows=501596 loops=1)
                 Parallel Seq Scan on pgbench_history_part_8_2002 h_142  (cost=0.00..5284.98 rows=208998 width=116) (actual time=0.493..1225.944 rows=501596 loops=1)
                 Parallel Seq Scan on pgbench_history_part_8_2003 h_143  (cost=0.00..5284.98 rows=208998 width=116) (actual time=7.471..970.286 rows=501596 loops=1)
                 Parallel Seq Scan on pgbench_history_part_8_2004 h_144  (cost=0.00..5284.98 rows=208998 width=116) (actual time=0.917..1114.619 rows=501596 loops
			   ...
		 Parallel Seq Scan on pgbench_history_part_3_2016 h_56  (cost=0.00..5254.75 rows=207775 width=116) (actual time=0.010..927.365 rows=498661 loops=1)
                 Parallel Seq Scan on pgbench_history_part_3_2017 h_57  (cost=0.00..5254.75 rows=207775 width=116) (actual time=8.567..1025.146 rows=498661 loops=1)
                 Parallel Seq Scan on pgbench_history_part_3_2018 h_58  (cost=0.00..5254.75 rows=207775 width=116) (actual time=3.004..1164.583 rows=498661 loops=1)
                 Parallel Seq Scan on pgbench_history_part_3_2019 h_59  (cost=0.00..5254.75 rows=207775 width=116) (actual time=3.489..1096.999 rows=498661 loops=1)
                 Parallel Seq Scan on pgbench_history_part_3_2020 h_60  (cost=0.00..5254.75 rows=207775 width=116) (actual time=0.668..1026.125 rows=498661 loops=1)
       Hash  (cost=14.12..14.12 rows=1 width=4) (actual time=422.343..422.347 rows=1 loops=3)
               Buckets: 1024  Batches: 1  Memory Usage: 9kB
                 Seq Scan on pgbench_branches b  (cost=0.00..14.12 rows=1 width=4) (actual time=422.323..422.336 rows=1 loops=3)
                     Filter: (bbalance = '-1060874'::integer)
                     Rows Removed by Filter: 9
 Planning Time: 4.387 ms
 JIT:
   Functions: 27
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 13.267 ms, Inlining 423.653 ms, Optimization 484.481 ms, Emission 356.528 ms, Total 1277.928 ms
 Execution Time: 279848.557 ms
(217 rows)

Essayons une autre approche en remplaçant la jointure explicite par un SOUS SELECT permettant un prédicat sur h.bid :

explain analyse select h.* from pgbench_history_part h
WHERE h.bid IN (SELECT b.bid FROM pgbench_branches b
WHERE b.bbalance=-1060874);

Le plan est le même avec parcours de toutes les partitions et même durée.

En fait, nous savons que chaque branche a sa valeur bbalance unique :

pgbench=# select * from pgbench_branches ;
bid | bbalance | filler
-----+----------+--------
9 | 2420106 |
10 | 2947968 |
2 | 7995538 |
6 | -1060874 |
3 | -539711 |
7 | -2102106 |
4 | 2273395 |
5 | -2919603 |
1 | -1734545 |
8 | -649184 |
(10 rows)

Ainsi, l’opérateur d’égalité est possible dans ce contexte particulier de mes données :

 SELECT h.* from pgbench_history_part h WHERE h.bid = (SELECT b.bid FROM pgbench_branches b WHERE b.bbalance=-1060874); 

Le nombre de lignes retournées est le même avec cette fois une estimation conforme à la réalité. Le plan d’exécution semble identique, mais la différence essentielle réside dans le fait que des lectures de partitions ne sont pas exécutées (never executed). C’est une application du “pruning” réalisé à l’exécution.

Le temps d’exécution est logiquement divisé par 10 avec 28 secondes.

pgbench=# explain analyse select h.* from pgbench_history_part h WHERE h.bid = (SELECT b.bid FROM pgbench_branches b WHERE b.bbalance=-1060874);

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=14.12..2389515.31 rows=100102925 width=116) (actual time=3962.001..22765.583 rows=10016860 loops=1)
InitPlan 1 (returns $0)
  Seq Scan on pgbench_branches b (cost=0.00..14.12 rows=1 width=4) (actual time=0.055..0.063 rows=1 loops=1)
Filter: (bbalance = '-1060874'::integer)
Rows Removed by Filter: 9
 Seq Scan on pgbench_history_part_1_2001 h_1 (cost=0.00..9452.89 rows=500951 width=116) (never executed)
Filter: (bid = $0)
 Seq Scan on pgbench_history_part_1_2002 h_2 (cost=0.00..9452.89 rows=500951 width=116) (never executed)
Filter: (bid = $0)
 Seq Scan on pgbench_history_part_1_2003 h_3 (cost=0.00..9452.89 rows=500951 width=116) (never executed)
Filter: (bid = $0)
 Seq Scan on pgbench_history_part_1_2004 h_4 (cost=0.00..9452.92 rows=500954 width=116) (never executed)
Filter: (bid = $0)
 Seq Scan on pgbench_history_part_1_2005 h_5 (cost=0.00..9452.91 rows=500953 width=116) (never executed)
Filter: (bid = $0)
...
 Seq Scan on pgbench_history_part_6_2005 h_105 (cost=0.00..9451.54 rows=500843 width=116) (actual time=0.616..353.166 rows=500843 loops=1)
Filter: (bid = $0)
 Seq Scan on pgbench_history_part_6_2006 h_106 (cost=0.00..9451.54 rows=500843 width=116) (actual time=0.717..348.679 rows=500843 loops=1)
Filter: (bid = $0)
 Seq Scan on pgbench_history_part_6_2007 h_107 (cost=0.00..9451.54 rows=500843 width=116) (actual time=0.518..349.334 rows=500843 loops=1)
Filter: (bid = $0)
 Seq Scan on pgbench_history_part_6_2008 h_108 (cost=0.00..9451.54 rows=500843 width=116) (actual time=0.572..354.518 rows=500843 loops=1)
Filter: (bid = $0)
 Seq Scan on pgbench_history_part_6_2009 h_109 (cost=0.00..9451.54 rows=500843 width=116) (actual time=0.595..351.174 rows=500843 loops=1)
Filter: (bid = $0)
 Seq Scan on pgbench_history_part_6_2010 h_110 (cost=0.00..9451.54 rows=500843 width=116) (actual time=0.761..348.445 rows=500843 loops=1)
Filter: (bid = $0)
 Seq Scan on pgbench_history_part_6_2011 h_111 (cost=0.00..9451.54 rows=500843 width=116) (actual time=0.619..347.263 rows=500843 loops=1)
Filter: (bid = $0)
 Seq Scan on pgbench_history_part_6_2012 h_112 (cost=0.00..9451.54 rows=500843 width=116) (actual time=2.862..350.480 rows=500843 loops=1)
Filter: (bid = $0)
 Seq Scan on pgbench_history_part_6_2013 h_113 (cost=0.00..9451.54 rows=500843 width=116) (actual time=0.541..345.805 rows=500843 loops=1)
Filter: (bid = $0)
 Seq Scan on pgbench_history_part_6_2014 h_114 (cost=0.00..9451.54 rows=500843 width=116) (actual time=0.528..347.940 rows=500843 loops=1)
Filter: (bid = $0)
 Seq Scan on pgbench_history_part_6_2015 h_115 (cost=0.00..9451.54 rows=500843 width=116) (actual time=0.802..348.066 rows=500843 loops=1)
Filter: (bid = $0)
 Seq Scan on pgbench_history_part_6_2016 h_116 (cost=0.00..9451.54 rows=500843 width=116) (actual time=0.706..346.924 rows=500843 loops=1)
Filter: (bid = $0)
 Seq Scan on pgbench_history_part_6_2017 h_117 (cost=0.00..9451.54 rows=500843 width=116) (actual time=0.589..349.294 rows=500843 loops=1)
Filter: (bid = $0)
 Seq Scan on pgbench_history_part_6_2018 h_118 (cost=0.00..9451.54 rows=500843 width=116) (actual time=0.889..346.096 rows=500843 loops=1)
Filter: (bid = $0)
 Seq Scan on pgbench_history_part_6_2019 h_119 (cost=0.00..9451.54 rows=500843 width=116) (actual time=0.554..347.696 rows=500843 loops=1)
Filter: (bid = $0)
 Seq Scan on pgbench_history_part_6_2020 h_120 (cost=0.00..9451.54 rows=500843 width=116) (actual time=0.562..350.516 rows=500843 loops=1)
Filter: (bid = $0)
 Seq Scan on pgbench_history_part_7_2001 h_121 (cost=0.00..9436.80 rows=500064 width=116) (never executed)
Filter: (bid = $0)
 Seq Scan on pgbench_history_part_7_2002 h_122 (cost=0.00..9436.80 rows=500064 width=116) (never executed)
...
Planning Time: 4.950 ms
JIT:
Functions: 405
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 40.662 ms, Inlining 9.622 ms, Optimization 2439.467 ms, Emission 1507.671 ms, Total 3997.423 ms
Execution Time: 28649.118 ms
(411 rows)

Ces SQL simples listant directement les clés “bid” avec opérateur IN ou BETWEEN vont évidemment permettre le pruning, déterminé cette fois dès l’élaboration du plan d’exécution:

select h.* from pgbench_history_part h WHERE h.bid IN (3,7);
select h.* from pgbench_history_part h WHERE h.bid between 3 and 5;

Pour résumer, le pruning sera possible à l’exécution si :

• un prédicat porte sur la clé de partitionnement (ici bid), même si le prédicat ne porte pas directement sur la colonne clé de la table partitionnée mais sur la colonne d’une autre table jointe, clé étrangère sur la clé de partitionnement.
• l’opérateur d’égalité est utilisé dans un prédicat avec SOUS SELECT retournant la valeur comparée (auquel cas une seule ligne serait attendue en retour de ce SOUS SELECT).

Il ne fonctionnera pas toujours dans le cas où :

• un opérateur IN est utilisé dans le prédicat sur la clé de partitionnement avec SOUS SELECT, d’autant plus pénalisant si le SOUS SELECT est requis pour retourner plusieurs valeurs. En revanche, l’opérateur IN peut être utilisée avec une liste de valeurs.
• le prédicat porte sur une autre colonne que la clé de partitionnement, typiquement une colonne d’une autre table jointe (comme dans notre exemple ou nous voulions appliquer un filtre sur pgbench_branches.bbalance).

Intéressons nous maintenant au second niveau de partitionnement par RANGE de la colonne mtime, en ajoutant une clause BETWEEN sur mtime pour sélectionner des données sur une plage mtime de 10 minutes seulement le 03/11/2013 :

 SELECT h.* from pgbench_history_part h 
WHERE h.bid = (SELECT b.bid FROM pgbench_branches b WHERE b.bbalance=-1060874) 
AND mtime BETWEEN '2013-11-03 12:00:00'::timestamp AND '2013-11-03 12:10:00'::timestamp; 

Le plan d’exécution révèle un “pruning” permettant de ne lire qu’une seule partition, celle de l’année 2013 de la branche 6, par “Index Scan” avec l’index créé sur mtime (“Index Scan using pgbench_history_part_6_2013_mtime_idx on pgbench_history_part_6_2013”). Le pruning à l’élaboration du plan permettait de savoir que seules les partitions “2013” seraient utiles du fait des valeurs explicites dans le prédicat sur mtime, et c’est ensuite à l’exécution seulement que celles des branches autres que bid=6 ont pu aussi être éliminées (never executed) après résolution de la valeur retournée par le SOUS SELECT. La durée d’exécution est cette fois de 30 ms !

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=14.55..4727.49 rows=103349 width=116) (actual time=0.074..23.478 rows=11335 loops=1)
InitPlan 1 (returns $0)
 Seq Scan on pgbench_branches b (cost=0.00..14.12 rows=1 width=4) (actual time=0.029..0.038 rows=1 loops=1)
Filter: (bbalance = '-1060874'::integer)
Rows Removed by Filter: 9
 Index Scan using pgbench_history_part_1_2013_mtime_idx on pgbench_history_part_1_2013 h_1 (cost=0.42..439.77 rows=10905 width=116) (never executed)
Index Cond: ((mtime >= '2013-11-03 12:00:00'::timestamp without time zone) AND (mtime <= '2013-11-03 12:10:00'::timestamp without time zone))
Filter: (bid = $0)
 Index Scan using pgbench_history_part_2_2013_mtime_idx on pgbench_history_part_2_2013 h_2 (cost=0.42..439.75 rows=10860 width=116) (never executed)
Index Cond: ((mtime >= '2013-11-03 12:00:00'::timestamp without time zone) AND (mtime <= '2013-11-03 12:10:00'::timestamp without time zone))
Filter: (bid = $0)
 Index Scan using pgbench_history_part_3_2013_mtime_idx on pgbench_history_part_3_2013 h_3 (cost=0.42..419.00 rows=10311 width=116) (never executed)
Index Cond: ((mtime >= '2013-11-03 12:00:00'::timestamp without time zone) AND (mtime <= '2013-11-03 12:10:00'::timestamp without time zone))
Filter: (bid = $0)
 Index Scan using pgbench_history_part_4_2013_mtime_idx on pgbench_history_part_4_2013 h_4 (cost=0.42..450.14 rows=11140 width=116) (never executed)
Index Cond: ((mtime >= '2013-11-03 12:00:00'::timestamp without time zone) AND (mtime <= '2013-11-03 12:10:00'::timestamp without time zone))
Filter: (bid = $0)
 Index Scan using pgbench_history_part_5_2013_mtime_idx on pgbench_history_part_5_2013 h_5 (cost=0.42..375.25 rows=9183 width=116) (never executed)
Index Cond: ((mtime >= '2013-11-03 12:00:00'::timestamp without time zone) AND (mtime <= '2013-11-03 12:10:00'::timestamp without time zone))
Filter: (bid = $0)
 Index Scan using pgbench_history_part_6_2013_mtime_idx on pgbench_history_part_6_2013 h_6 (cost=0.42..422.91 rows=10425 width=116) (actual time=0.030..10.050 rows=11335 loops=1)
Index Cond: ((mtime >= '2013-11-03 12:00:00'::timestamp without time zone) AND (mtime <= '2013-11-03 12:10:00'::timestamp without time zone))
Filter: (bid = $0)
 Index Scan using pgbench_history_part_7_2013_mtime_idx on pgbench_history_part_7_2013 h_7 (cost=0.42..434.29 rows=10677 width=116) (never executed)
Index Cond: ((mtime >= '2013-11-03 12:00:00'::timestamp without time zone) AND (mtime <= '2013-11-03 12:10:00'::timestamp without time zone))
Filter: (bid = $0)
 Index Scan using pgbench_history_part_8_2013_mtime_idx on pgbench_history_part_8_2013 h_8 (cost=0.42..419.51 rows=10312 width=116) (never executed)
Index Cond: ((mtime >= '2013-11-03 12:00:00'::timestamp without time zone) AND (mtime <= '2013-11-03 12:10:00'::timestamp without time zone))
Filter: (bid = $0)
 Index Scan using pgbench_history_part_9_2013_mtime_idx on pgbench_history_part_9_2013 h_9 (cost=0.42..393.85 rows=9695 width=116) (never executed)
Index Cond: ((mtime >= '2013-11-03 12:00:00'::timestamp without time zone) AND (mtime <= '2013-11-03 12:10:00'::timestamp without time zone))
Filter: (bid = $0)
 Index Scan using pgbench_history_part_10_2013_mtime_idx on pgbench_history_part_10_2013 h_10 (cost=0.42..402.16 rows=9841 width=116) (never executed)
Index Cond: ((mtime >= '2013-11-03 12:00:00'::timestamp without time zone) AND (mtime <= '2013-11-03 12:10:00'::timestamp without time zone))
Filter: (bid = $0)
Planning Time: 0.796 ms
Execution Time: 30.138 ms
(37 rows)

Evidemment, le bénéfice sera le même avec opérateurs <, > ou = sur mtime :

SELECT h.* from pgbench_history_part h WHERE h.bid = (SELECT b.bid FROM pgbench_branches b WHERE b.bbalance=-1060874) AND mtime = '2013-11-03 12:00:00'::timestamp ;

Par contre, toute transformation appliquée au champ mtime ne permettra pas de pruning, pas plus qu’il ne rendra possible l’utilisation de l’index, par exemple en ajoutant un intervalle à mtime :

SELECT h.* from pgbench_history_part h WHERE h.bid = (SELECT b.bid FROM pgbench_branches b WHERE b.bbalance=-1060874) AND mtime + interval '1 hour' = '2013-11-03 12:00:00'::timestamp ;

PostgreSQL utilise les contraintes de LIST ou RANGE définissant les partitions pour déterminer les conditions permettant d’éliminer des partitions.

Avec les améliorations successives du pruning, que le partitionnement soit par RANGE, LIST ou HASH, de nombreux opérateurs de comparaison sur la clé de partitionnement permettent l’élimination de partitions dès l’étape de planification, que ce soit avec =, >, <, BETWEEN ou encore IN (liste de valeurs); du moment que les valeurs soient interprétables à la planification.

Dans le cas où les valeurs de la clé ne peuvent être déterminées qu’à l’exécution, le pruning possible à l’exécution dépendra beaucoup plus de l’écriture des jointures ou/et sous-requêtes dans les prédicats…

Au sujet du pruning dans la documentation officielle : https://www.postgresql.org/docs/13/ddl-partitioning.html#DDL-PARTITION-PRUNING 

Continuez votre lecture sur le blog :

• PostgreSQL 13 : présentation (Emmanuel RAMI) [PostgreSQL]
• PostgreSQL : évolution du partitionnement de 9.6 à 12 (2/2) (Capdata team) [PostgreSQL]
• Nouveautés pg_stat_statements avec PostgreSQL 15 (David Baffaleuf) [PostgreSQL]
• Partitionnement sémantique (Capdata team) [Sybase]
• Réplication logique avec PostgreSQL (Capdata team) [PostgreSQL]