Volatilité des fonctions et performance - partie 2

      Dans cette première partie, nous avons vu qu'il pouvait dans certains cas se révéler coûteux d'utiliser des fonctions dans les clauses WHERE, même lorsque les fonctions ne sont pas appliquées sur les colonnes.
      Le partitionnement ajoute un nouveau point de vigilance. La version 11 introduit la notion de "partition pruning". Le but est d'élimiter toujours davantage de partitions lors de la phase de parse mais aussi d'exécution afin d'améliorer les performances.
      Cela signifie-t-il qu'il ne faut plus se poser de questions ? Quelle influence a le degré de volatilité des fonctions lorsqu'elles sont utilisées au niveau de la clause de restriction (WHERE) dans des tests permettant en théorie d'écarter des partitions ?
      Tout dépend en fait de la fonction et du contexte donc nous allons montrer qu'il faut toujours être vigilant, même en 11 :

select version(); version --------------------------------------------------------------------------------------------------------------------------------------------------- PostgreSQL 11beta1 (Ubuntu 11~beta1-2.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit create table lancers_p_2010 partition of lancers_p for values FROM ('2010-01-01') TO ('2011-01-01') partition by list(idg); CREATE TABLE create table lancers_p_2010_001 partition of lancers_p_2010 for values in (1); CREATE TABLE ... create table lancers_p_2010_010 partition of lancers_p_2010 for values in (10); CREATE TABLE create table lancers_p_2011 partition of lancers_p for values FROM ('2011-01-01') TO ('2012-01-01') partition by list(idg); CREATE TABLE ... create table lancers_p_2019 partition of lancers_p for values FROM ('2019-01-01') TO ('2020-01-01') partition by list(idg); CREATE TABLE ... create table lancers_p_2019_010 partition of lancers_p_2019 for values in (10); CREATE TABLE \d+ lancers_p Table « public.lancers_p » Colonne | Type | Collationnement | NULL-able | Par défaut | Stockage | Cible de statistiques | Description ---------+-----------------------------+-----------------+-----------+------------+----------+-----------------------+------------- dtl | timestamp without time zone | | | | plain | | idg | integer | | | | plain | | perf | integer | | | | plain | | Clé de partition : RANGE (dtl) Partitions: lancers_p_2010 FOR VALUES FROM ('2010-01-01 00:00:00') TO ('2011-01-01 00:00:00'), PARTITIONED, lancers_p_2011 FOR VALUES FROM ('2011-01-01 00:00:00') TO ('2012-01-01 00:00:00'), PARTITIONED, lancers_p_2012 FOR VALUES FROM ('2012-01-01 00:00:00') TO ('2013-01-01 00:00:00'), PARTITIONED, lancers_p_2013 FOR VALUES FROM ('2013-01-01 00:00:00') TO ('2014-01-01 00:00:00'), PARTITIONED, lancers_p_2014 FOR VALUES FROM ('2014-01-01 00:00:00') TO ('2015-01-01 00:00:00'), PARTITIONED, lancers_p_2015 FOR VALUES FROM ('2015-01-01 00:00:00') TO ('2016-01-01 00:00:00'), PARTITIONED, lancers_p_2017 FOR VALUES FROM ('2017-01-01 00:00:00') TO ('2018-01-01 00:00:00'), PARTITIONED, lancers_p_2018 FOR VALUES FROM ('2018-01-01 00:00:00') TO ('2019-01-01 00:00:00'), PARTITIONED, lancers_p_2019 FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2020-01-01 00:00:00'), PARTITIONED \d+ lancers_p_2010 Table « public.lancers_p_2010 » Colonne | Type | Collationnement | NULL-able | Par défaut | Stockage | Cible de statistiques | Description ---------+-----------------------------+-----------------+-----------+------------+----------+-----------------------+------------- dtl | timestamp without time zone | | | | plain | | idg | integer | | | | plain | | perf | integer | | | | plain | | Partition de : lancers_p FOR VALUES FROM ('2010-01-01 00:00:00') TO ('2011-01-01 00:00:00') Contrainte de partition : ((dtl IS NOT NULL) AND (dtl >= '2010-01-01 00:00:00'::timestamp without time zone) AND (dtl < '2011-01-01 00:00:00'::timestamp without time zone)) Clé de partition : LIST (idg) Partitions: lancers_p_2010_001 FOR VALUES IN (1), lancers_p_2010_002 FOR VALUES IN (2), lancers_p_2010_003 FOR VALUES IN (3), lancers_p_2010_004 FOR VALUES IN (4), lancers_p_2010_005 FOR VALUES IN (5), lancers_p_2010_006 FOR VALUES IN (6), lancers_p_2010_007 FOR VALUES IN (7), lancers_p_2010_008 FOR VALUES IN (8), lancers_p_2010_009 FOR VALUES IN (9), lancers_p_2010_010 FOR VALUES IN (10) \d+ lancers_p_2010_001 Table « postgres.lancers_p_2010_001 » Colonne | Type | Collationnement | NULL-able | Par défaut | Stockage | Cible de statistiques | Description ---------+-----------------------------+-----------------+-----------+------------+----------+-----------------------+------------- dtl | timestamp without time zone | | | | plain | | idg | integer | | | | plain | | perf | integer | | | | plain | | Partition de : lancers_p_2010 FOR VALUES IN (1) Contrainte de partition : ((dtl IS NOT NULL) AND (dtl >= '2010-01-01 00:00:00'::timestamp without time zone) AND (dtl < '2011-01-01 00:00:00'::timestamp without time zone) AND (idg IS NOT NULL) AND (idg = 1)) WITH serie(i) AS (SELECT generate_series(200000000,1,-1)) insert into lancers_p(dtl, idg, perf) select current_timestamp - (i || ' seconds')::interval, trunc(random() * 10 + 1), case when random() <= 0.0001 then 50000 + trunc(random() * 50000 + 1) else trunc(random() * 50000 + 1) end from serie; INSERT 0 200000000 vacuum analyze lancers_p; VACUUM set jit=off; SET

      Le contexte est posé : une table lancers partitionnée par année et sous-partitionnée sur l'identifiant du geant.
      Nous allons commencer par filtrer sur le geant en utilisant une constante puis une fonction IMMUTABLE et PARALLEL SAFE :

explain select avg(perf) from lancers_p where idg=1; QUERY PLAN ------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=275913.34..275913.35 rows=1 width=32) -> Gather (cost=275913.12..275913.33 rows=2 width=32) Workers Planned: 2 -> Partial Aggregate (cost=274913.12..274913.13 rows=1 width=32) -> Parallel Append (cost=0.00..254074.94 rows=8335274 width=4) -> Parallel Seq Scan on lancers_p_2016_001 (cost=0.00..33562.64 rows=1317571 width=4) Filter: (idg = 1) -> Parallel Seq Scan on lancers_p_2017_001 (cost=0.00..33501.47 rows=1315158 width=4) Filter: (idg = 1) -> Parallel Seq Scan on lancers_p_2013_001 (cost=0.00..33497.28 rows=1314982 width=4) Filter: (idg = 1) -> Parallel Seq Scan on lancers_p_2014_001 (cost=0.00..33477.77 rows=1314222 width=4) Filter: (idg = 1) -> Parallel Seq Scan on lancers_p_2015_001 (cost=0.00..33444.31 rows=1312905 width=4) Filter: (idg = 1) -> Parallel Seq Scan on lancers_p_2012_001 (cost=0.00..31035.45 rows=1218356 width=4) Filter: (idg = 1) -> Parallel Seq Scan on lancers_p_2018_001 (cost=0.00..13808.84 rows=542068 width=4) Filter: (idg = 1) -> Parallel Seq Scan on lancers_p_2010_001 (cost=0.00..23.60 rows=5 width=4) Filter: (idg = 1) -> Parallel Seq Scan on lancers_p_2011_001 (cost=0.00..23.60 rows=5 width=4) Filter: (idg = 1) -> Parallel Seq Scan on lancers_p_2019_001 (cost=0.00..23.60 rows=5 width=4) Filter: (idg = 1) select avg(perf) from lancers_p where idg=1; avg -------------------- 25005.864500990921 (1 ligne) Temps : 684,995 ms ... Temps : 680,425 ms ... Temps : 693,356 ms \ef mod(text, text) CREATE OR REPLACE FUNCTION pg_catalog.mod(integer, integer) RETURNS integer LANGUAGE internal IMMUTABLE PARALLEL SAFE STRICT AS $function$int4mod$function$ explain select avg(perf) from lancers_p where idg=mod(3,2); QUERY PLAN ------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=275913.34..275913.35 rows=1 width=32) -> Gather (cost=275913.12..275913.33 rows=2 width=32) Workers Planned: 2 -> Partial Aggregate (cost=274913.12..274913.13 rows=1 width=32) -> Parallel Append (cost=0.00..254074.94 rows=8335274 width=4) -> Parallel Seq Scan on lancers_p_2016_001 (cost=0.00..33562.64 rows=1317571 width=4) Filter: (idg = 1) -> Parallel Seq Scan on lancers_p_2017_001 (cost=0.00..33501.47 rows=1315158 width=4) Filter: (idg = 1) -> Parallel Seq Scan on lancers_p_2013_001 (cost=0.00..33497.28 rows=1314982 width=4) Filter: (idg = 1) -> Parallel Seq Scan on lancers_p_2014_001 (cost=0.00..33477.77 rows=1314222 width=4) Filter: (idg = 1) -> Parallel Seq Scan on lancers_p_2015_001 (cost=0.00..33444.31 rows=1312905 width=4) Filter: (idg = 1) -> Parallel Seq Scan on lancers_p_2012_001 (cost=0.00..31035.45 rows=1218356 width=4) Filter: (idg = 1) -> Parallel Seq Scan on lancers_p_2018_001 (cost=0.00..13808.84 rows=542068 width=4) Filter: (idg = 1) -> Parallel Seq Scan on lancers_p_2010_001 (cost=0.00..23.60 rows=5 width=4) Filter: (idg = 1) -> Parallel Seq Scan on lancers_p_2011_001 (cost=0.00..23.60 rows=5 width=4) Filter: (idg = 1) -> Parallel Seq Scan on lancers_p_2019_001 (cost=0.00..23.60 rows=5 width=4) Filter: (idg = 1) select avg(perf) from lancers_p where idg=mod(3,2); avg -------------------- 25005.864500990921 (1 ligne) Temps : 682,717 ms ... Temps : 691,192 ms ... Temps : 690,523 ms

      Ici, aucune différence au niveau du plan prévu comme des temps obtenus. Dés la phase de planification, les partitions ne pouvant contenir de données concernant le géant d'identifiant 1 sont écartées dans les 2 cas. Le contrat est respecté. A arguments constants, PostgreSQL peut remplacer sans se poser de questions un appel à une fonction IMMUTABLE par son résultat...et il ne s'en prive pas.
      A présent qu'en est-il si une fonction STABLE & PARALLEL SAFE, au hasard to_timestamp, est utilisée ?

explain select avg(perf) from lancers_p where idg=1 and dtl >= to_timestamp('29/02/2016', 'DD/MM/YYYY') and dtl < to_timestamp('01/03/2016', 'DD/MM/YYYY'); QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=296809.09..296809.10 rows=1 width=32) -> Gather (cost=296808.87..296809.08 rows=2 width=32) Workers Planned: 2 -> Partial Aggregate (cost=295808.87..295808.88 rows=1 width=32) -> Parallel Append (cost=0.00..295800.52 rows=3340 width=4) -> Parallel Seq Scan on lancers_p_2016_001 (cost=0.00..46738.35 rows=3331 width=4) Filter: ((idg = 1) AND (dtl >= to_timestamp('29/02/2016'::text, 'DD/MM/YYYY'::text)) AND (dtl < to_timestamp('01/03/2016'::text, 'DD/MM/YYYY'::text))) -> Parallel Seq Scan on lancers_p_2017_001 (cost=0.00..46653.04 rows=1 width=4) Filter: ((idg = 1) AND (dtl >= to_timestamp('29/02/2016'::text, 'DD/MM/YYYY'::text)) AND (dtl < to_timestamp('01/03/2016'::text, 'DD/MM/YYYY'::text))) -> Parallel Seq Scan on lancers_p_2013_001 (cost=0.00..46647.10 rows=1 width=4) Filter: ((idg = 1) AND (dtl >= to_timestamp('29/02/2016'::text, 'DD/MM/YYYY'::text)) AND (dtl < to_timestamp('01/03/2016'::text, 'DD/MM/YYYY'::text))) -> Parallel Seq Scan on lancers_p_2014_001 (cost=0.00..46619.99 rows=1 width=4) Filter: ((idg = 1) AND (dtl >= to_timestamp('29/02/2016'::text, 'DD/MM/YYYY'::text)) AND (dtl < to_timestamp('01/03/2016'::text, 'DD/MM/YYYY'::text))) -> Parallel Seq Scan on lancers_p_2015_001 (cost=0.00..46573.36 rows=1 width=4) Filter: ((idg = 1) AND (dtl >= to_timestamp('29/02/2016'::text, 'DD/MM/YYYY'::text)) AND (dtl < to_timestamp('01/03/2016'::text, 'DD/MM/YYYY'::text))) -> Parallel Seq Scan on lancers_p_2012_001 (cost=0.00..43219.01 rows=1 width=4) Filter: ((idg = 1) AND (dtl >= to_timestamp('29/02/2016'::text, 'DD/MM/YYYY'::text)) AND (dtl < to_timestamp('01/03/2016'::text, 'DD/MM/YYYY'::text))) -> Parallel Seq Scan on lancers_p_2018_001 (cost=0.00..19229.52 rows=1 width=4) Filter: ((idg = 1) AND (dtl >= to_timestamp('29/02/2016'::text, 'DD/MM/YYYY'::text)) AND (dtl < to_timestamp('01/03/2016'::text, 'DD/MM/YYYY'::text))) -> Parallel Seq Scan on lancers_p_2010_001 (cost=0.00..34.49 rows=1 width=4) Filter: ((idg = 1) AND (dtl >= to_timestamp('29/02/2016'::text, 'DD/MM/YYYY'::text)) AND (dtl < to_timestamp('01/03/2016'::text, 'DD/MM/YYYY'::text))) -> Parallel Seq Scan on lancers_p_2011_001 (cost=0.00..34.49 rows=1 width=4) Filter: ((idg = 1) AND (dtl >= to_timestamp('29/02/2016'::text, 'DD/MM/YYYY'::text)) AND (dtl < to_timestamp('01/03/2016'::text, 'DD/MM/YYYY'::text))) -> Parallel Seq Scan on lancers_p_2019_001 (cost=0.00..34.49 rows=1 width=4) Filter: ((idg = 1) AND (dtl >= to_timestamp('29/02/2016'::text, 'DD/MM/YYYY'::text)) AND (dtl < to_timestamp('01/03/2016'::text, 'DD/MM/YYYY'::text))) select avg(perf) from lancers_p where idg=1 and dtl >= to_timestamp('29/02/2016', 'DD/MM/YYYY') and dtl < to_timestamp('01/03/2016', 'DD/MM/YYYY'); avg -------------------- 25296.514611712656 (1 ligne) Durée : 3661,824 ms (00:03,662) ... Durée : 3513,036 ms (00:03,513) ... Durée : 3606,647 ms (00:03,607) explain analyze select avg(perf) from lancers_p where idg=1 and dtl >= to_timestamp('29/02/2016', 'DD/MM/YYYY') and dtl < to_timestamp('01/03/2016', 'DD/MM/YYYY'); QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=296809.09..296809.10 rows=1 width=32) (actual time=3551.053..3551.053 rows=1 loops=1) -> Gather (cost=296808.87..296809.08 rows=2 width=32) (actual time=3550.954..3551.041 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=295808.87..295808.88 rows=1 width=32) (actual time=3540.218..3540.218 rows=1 loops=3) -> Parallel Append (cost=0.00..295800.52 rows=3340 width=4) (actual time=2433.782..3540.054 rows=2863 loops=3) -> Parallel Seq Scan on lancers_p_2016_001 (cost=0.00..46738.35 rows=3331 width=4) (actual time=218.132..2213.491 rows=8589 loops=1) Filter: ((idg = 1) AND (dtl >= to_timestamp('29/02/2016'::text, 'DD/MM/YYYY'::text)) AND (dtl < to_timestamp('01/03/2016'::text, 'DD/MM/YYYY'::text))) Rows Removed by Filter: 3153582 -> Parallel Seq Scan on lancers_p_2017_001 (cost=0.00..46653.04 rows=1 width=4) (actual time=2348.570..2348.570 rows=0 loops=1) Filter: ((idg = 1) AND (dtl >= to_timestamp('29/02/2016'::text, 'DD/MM/YYYY'::text)) AND (dtl < to_timestamp('01/03/2016'::text, 'DD/MM/YYYY'::text))) Rows Removed by Filter: 3156378 -> Parallel Seq Scan on lancers_p_2013_001 (cost=0.00..46647.10 rows=1 width=4) (actual time=1282.392..1282.392 rows=0 loops=1) Filter: ((idg = 1) AND (dtl >= to_timestamp('29/02/2016'::text, 'DD/MM/YYYY'::text)) AND (dtl < to_timestamp('01/03/2016'::text, 'DD/MM/YYYY'::text))) Rows Removed by Filter: 3155957 -> Parallel Seq Scan on lancers_p_2014_001 (cost=0.00..46619.99 rows=1 width=4) (actual time=425.516..425.516 rows=0 loops=3) Filter: ((idg = 1) AND (dtl >= to_timestamp('29/02/2016'::text, 'DD/MM/YYYY'::text)) AND (dtl < to_timestamp('01/03/2016'::text, 'DD/MM/YYYY'::text))) Rows Removed by Filter: 1051377 -> Parallel Seq Scan on lancers_p_2015_001 (cost=0.00..46573.36 rows=1 width=4) (actual time=1302.182..1302.182 rows=0 loops=1) Filter: ((idg = 1) AND (dtl >= to_timestamp('29/02/2016'::text, 'DD/MM/YYYY'::text)) AND (dtl < to_timestamp('01/03/2016'::text, 'DD/MM/YYYY'::text))) Rows Removed by Filter: 3150972 -> Parallel Seq Scan on lancers_p_2012_001 (cost=0.00..43219.01 rows=1 width=4) (actual time=1178.839..1178.839 rows=0 loops=1) Filter: ((idg = 1) AND (dtl >= to_timestamp('29/02/2016'::text, 'DD/MM/YYYY'::text)) AND (dtl < to_timestamp('01/03/2016'::text, 'DD/MM/YYYY'::text))) Rows Removed by Filter: 2924054 -> Parallel Seq Scan on lancers_p_2018_001 (cost=0.00..19229.52 rows=1 width=4) (actual time=1017.697..1017.697 rows=0 loops=1) Filter: ((idg = 1) AND (dtl >= to_timestamp('29/02/2016'::text, 'DD/MM/YYYY'::text)) AND (dtl < to_timestamp('01/03/2016'::text, 'DD/MM/YYYY'::text))) Rows Removed by Filter: 1300962 -> Parallel Seq Scan on lancers_p_2010_001 (cost=0.00..34.49 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=1) Filter: ((idg = 1) AND (dtl >= to_timestamp('29/02/2016'::text, 'DD/MM/YYYY'::text)) AND (dtl < to_timestamp('01/03/2016'::text, 'DD/MM/YYYY'::text))) -> Parallel Seq Scan on lancers_p_2011_001 (cost=0.00..34.49 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=1) Filter: ((idg = 1) AND (dtl >= to_timestamp('29/02/2016'::text, 'DD/MM/YYYY'::text)) AND (dtl < to_timestamp('01/03/2016'::text, 'DD/MM/YYYY'::text))) -> Parallel Seq Scan on lancers_p_2019_001 (cost=0.00..34.49 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=1) Filter: ((idg = 1) AND (dtl >= to_timestamp('29/02/2016'::text, 'DD/MM/YYYY'::text)) AND (dtl < to_timestamp('01/03/2016'::text, 'DD/MM/YYYY'::text))) Planning Time: 3.478 ms Execution Time: 3553.133 ms (35 lignes) Durée : 3557,397 ms (00:03,557)

      Ouille, cette fois ce n'est pas bon. Le plan entraíne des accès à 9 partitions en pure perte. Seule la partition lancers_p_2016_001 peut en effet contenir des données susceptibles de nous intéresser dans le cadre de notre requête. Cependant, le planner ne peut pas écarter les autres partitions lancers_p_xxxx_001 lors de la phase de parse. Grâce au "partition pruning", elles pouraient depuis PostgreSQL 11 être écartées à l'exécution mais seulement dans certaines conditions.
      Il serait par ailleurs très malin de positionner un petit index BRIN sur la colonne dtl mais ici nous allons uniquement travailler avec le partitionnement. Quelle syntaxe adopter pour ne s'intéresser qu'à UNE partition dans cet exemple ?

explain select avg(perf) from lancers_p where idg=1 and dtl >= timestamp '2016-02-29' and dtl < timestamp '2016-03-01'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Finalize Aggregate (cost=41175.70..41175.71 rows=1 width=32) -> Gather (cost=41175.48..41175.69 rows=2 width=32) Workers Planned: 2 -> Partial Aggregate (cost=40175.48..40175.49 rows=1 width=32) -> Parallel Append (cost=0.00..40167.15 rows=3331 width=4) -> Parallel Seq Scan on lancers_p_2016_001 (cost=0.00..40150.50 rows=3331 width=4) Filter: ((dtl >= '2016-02-29 00:00:00'::timestamp without time zone) AND (dtl < '2016-03-01 00:00:00'::timestamp without time zone) AND (idg = 1)) select avg(perf) from lancers_p where idg=1 and dtl >= timestamp '2016-02-29' and dtl < timestamp '2016-03-01'; avg -------------------- 25296.514611712656 (1 ligne) Temps : 77,808 ms ... Temps : 77,152 ms ... Temps : 76,351 ms create or replace function to_timestamp_i(arg1 text, arg2 text) returns timestamp as $$ begin return to_timestamp(arg1, arg2); end; $$ language plpgsql immutable parallel safe; CREATE FUNCTION explain select avg(perf) from lancers_p where idg=1 and dtl >= to_timestamp_i('29/02/2016', 'DD/MM/YYYY') and dtl < to_timestamp_i('01/03/2016', 'DD/MM/YYYY'); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Finalize Aggregate (cost=41175.70..41175.71 rows=1 width=32) -> Gather (cost=41175.48..41175.69 rows=2 width=32) Workers Planned: 2 -> Partial Aggregate (cost=40175.48..40175.49 rows=1 width=32) -> Parallel Append (cost=0.00..40167.15 rows=3331 width=4) -> Parallel Seq Scan on lancers_p_2016_001 (cost=0.00..40150.50 rows=3331 width=4) Filter: ((dtl >= '2016-02-29 00:00:00'::timestamp without time zone) AND (dtl < '2016-03-01 00:00:00'::timestamp without time zone) AND (idg = 1)) select avg(perf) from lancers_p where idg=1 and dtl >= to_timestamp_i('29/02/2016', 'DD/MM/YYYY') and dtl < to_timestamp_i('01/03/2016', 'DD/MM/YYYY'); avg -------------------- 25296.514611712656 (1 ligne) Temps : 75,498 ms ... Temps : 79,562 ms ... Temps : 73,256 ms explain with bornes as (select to_timestamp('29/02/2016', 'DD/MM/YYYY') inf, to_timestamp('01/03/2016', 'DD/MM/YYYY') sup) select avg(perf) from lancers_p where idg = 1 and dtl >= (select inf from bornes) and dtl < (select sup from bornes); QUERY PLAN ---------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=255404.07..255404.08 rows=1 width=32) CTE bornes -> Result (cost=0.00..0.01 rows=1 width=16) InitPlan 2 (returns $1) -> CTE Scan on bornes (cost=0.00..0.02 rows=1 width=8) InitPlan 3 (returns $2) -> CTE Scan on bornes bornes_1 (cost=0.00..0.02 rows=1 width=8) -> Gather (cost=255403.80..255404.01 rows=2 width=32) Workers Planned: 2 Params Evaluated: $1, $2 -> Partial Aggregate (cost=254403.80..254403.81 rows=1 width=32) -> Parallel Append (cost=0.00..254299.60 rows=41680 width=4) -> Parallel Seq Scan on lancers_p_2016_001 (cost=0.00..40150.50 rows=6588 width=4) Filter: ((dtl >= $1) AND (dtl < $2) AND (idg = 1)) -> Parallel Seq Scan on lancers_p_2017_001 (cost=0.00..40077.26 rows=6576 width=4) Filter: ((dtl >= $1) AND (dtl < $2) AND (idg = 1)) -> Parallel Seq Scan on lancers_p_2013_001 (cost=0.00..40072.19 rows=6575 width=4) Filter: ((dtl >= $1) AND (dtl < $2) AND (idg = 1)) -> Parallel Seq Scan on lancers_p_2014_001 (cost=0.00..40048.88 rows=6571 width=4) Filter: ((dtl >= $1) AND (dtl < $2) AND (idg = 1)) -> Parallel Seq Scan on lancers_p_2015_001 (cost=0.00..40008.84 rows=6565 width=4) Filter: ((dtl >= $1) AND (dtl < $2) AND (idg = 1)) -> Parallel Seq Scan on lancers_p_2012_001 (cost=0.00..37127.23 rows=6092 width=4) Filter: ((dtl >= $1) AND (dtl < $2) AND (idg = 1)) -> Parallel Seq Scan on lancers_p_2018_001 (cost=0.00..16519.18 rows=2710 width=4) Filter: ((dtl >= $1) AND (dtl < $2) AND (idg = 1)) -> Parallel Seq Scan on lancers_p_2010_001 (cost=0.00..29.04 rows=1 width=4) Filter: ((dtl >= $1) AND (dtl < $2) AND (idg = 1)) -> Parallel Seq Scan on lancers_p_2011_001 (cost=0.00..29.04 rows=1 width=4) Filter: ((dtl >= $1) AND (dtl < $2) AND (idg = 1)) -> Parallel Seq Scan on lancers_p_2019_001 (cost=0.00..29.04 rows=1 width=4) Filter: ((dtl >= $1) AND (dtl < $2) AND (idg = 1)) with bornes as (select to_timestamp('29/02/2016', 'DD/MM/YYYY') inf, to_timestamp('01/03/2016', 'DD/MM/YYYY') sup) select avg(perf) from lancers_p where idg = 1 and dtl >= (select inf from bornes) and dtl < (select sup from bornes); avg -------------------- 25296.514611712656 (1 ligne) Temps : 199,983 ms ... Temps : 192,921 ms ... Temps : 192,135 ms explain analyze with bornes as (select to_timestamp('29/02/2016', 'DD/MM/YYYY') inf, to_timestamp('01/03/2016', 'DD/MM/YYYY') sup) select avg(perf) from lancers_p where idg = 1 and dtl >= (select inf from bornes) and dtl < (select sup from bornes); QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=255404.07..255404.08 rows=1 width=32) (actual time=190.887..190.887 rows=1 loops=1) CTE bornes -> Result (cost=0.00..0.01 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=1) InitPlan 2 (returns $1) -> CTE Scan on bornes (cost=0.00..0.02 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=1) InitPlan 3 (returns $2) -> CTE Scan on bornes bornes_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=1) -> Gather (cost=255403.80..255404.01 rows=2 width=32) (actual time=190.836..190.878 rows=3 loops=1) Workers Planned: 2 Params Evaluated: $1, $2 Workers Launched: 2 -> Partial Aggregate (cost=254403.80..254403.81 rows=1 width=32) (actual time=187.623..187.623 rows=1 loops=3) -> Parallel Append (cost=0.00..254299.60 rows=41680 width=4) (actual time=21.764..187.454 rows=2863 loops=3) -> Parallel Seq Scan on lancers_p_2016_001 (cost=0.00..40150.50 rows=6588 width=4) (actual time=21.725..187.271 rows=2863 loops=3) Filter: ((dtl >= $1) AND (dtl < $2) AND (idg = 1)) Rows Removed by Filter: 1051194 -> Parallel Seq Scan on lancers_p_2017_001 (cost=0.00..40077.26 rows=6576 width=4) (never executed) Filter: ((dtl >= $1) AND (dtl < $2) AND (idg = 1)) -> Parallel Seq Scan on lancers_p_2013_001 (cost=0.00..40072.19 rows=6575 width=4) (never executed) Filter: ((dtl >= $1) AND (dtl < $2) AND (idg = 1)) -> Parallel Seq Scan on lancers_p_2014_001 (cost=0.00..40048.88 rows=6571 width=4) (never executed) Filter: ((dtl >= $1) AND (dtl < $2) AND (idg = 1)) -> Parallel Seq Scan on lancers_p_2015_001 (cost=0.00..40008.84 rows=6565 width=4) (never executed) Filter: ((dtl >= $1) AND (dtl < $2) AND (idg = 1)) -> Parallel Seq Scan on lancers_p_2012_001 (cost=0.00..37127.23 rows=6092 width=4) (never executed) Filter: ((dtl >= $1) AND (dtl < $2) AND (idg = 1)) -> Parallel Seq Scan on lancers_p_2018_001 (cost=0.00..16519.18 rows=2710 width=4) (never executed) Filter: ((dtl >= $1) AND (dtl < $2) AND (idg = 1)) -> Parallel Seq Scan on lancers_p_2010_001 (cost=0.00..29.04 rows=1 width=4) (never executed) Filter: ((dtl >= $1) AND (dtl < $2) AND (idg = 1)) -> Parallel Seq Scan on lancers_p_2011_001 (cost=0.00..29.04 rows=1 width=4) (never executed) Filter: ((dtl >= $1) AND (dtl < $2) AND (idg = 1)) -> Parallel Seq Scan on lancers_p_2019_001 (cost=0.00..29.04 rows=1 width=4) (never executed) Filter: ((dtl >= $1) AND (dtl < $2) AND (idg = 1)) Planning Time: 2.900 ms Execution Time: 192.825 ms (36 lignes) Temps : 196,451 ms explain analyze select avg(perf) from lancers_p where idg = 1 and dtl >= (select to_timestamp('29/02/2016', 'DD/MM/YYYY')) and dtl < (select to_timestamp('01/03/2016', 'DD/MM/YYYY')); QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=255404.04..255404.05 rows=1 width=32) (actual time=189.436..189.436 rows=1 loops=1) InitPlan 1 (returns $0) -> Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.014..0.015 rows=1 loops=1) InitPlan 2 (returns $1) -> Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1) -> Gather (cost=255403.80..255404.01 rows=2 width=32) (actual time=189.397..189.399 rows=3 loops=1) Workers Planned: 2 Params Evaluated: $0, $1 Workers Launched: 2 -> Partial Aggregate (cost=254403.80..254403.81 rows=1 width=32) (actual time=184.753..184.754 rows=1 loops=3) -> Parallel Append (cost=0.00..254299.60 rows=41680 width=4) (actual time=21.223..184.603 rows=2863 loops=3) -> Parallel Seq Scan on lancers_p_2016_001 (cost=0.00..40150.50 rows=6588 width=4) (actual time=21.205..184.449 rows=2863 loops=3) Filter: ((dtl >= $0) AND (dtl < $1) AND (idg = 1)) Rows Removed by Filter: 1051194 -> Parallel Seq Scan on lancers_p_2017_001 (cost=0.00..40077.26 rows=6576 width=4) (never executed) Filter: ((dtl >= $0) AND (dtl < $1) AND (idg = 1)) -> Parallel Seq Scan on lancers_p_2013_001 (cost=0.00..40072.19 rows=6575 width=4) (never executed) Filter: ((dtl >= $0) AND (dtl < $1) AND (idg = 1)) -> Parallel Seq Scan on lancers_p_2014_001 (cost=0.00..40048.88 rows=6571 width=4) (never executed) Filter: ((dtl >= $0) AND (dtl < $1) AND (idg = 1)) -> Parallel Seq Scan on lancers_p_2015_001 (cost=0.00..40008.84 rows=6565 width=4) (never executed) Filter: ((dtl >= $0) AND (dtl < $1) AND (idg = 1)) -> Parallel Seq Scan on lancers_p_2012_001 (cost=0.00..37127.23 rows=6092 width=4) (never executed) Filter: ((dtl >= $0) AND (dtl < $1) AND (idg = 1)) -> Parallel Seq Scan on lancers_p_2018_001 (cost=0.00..16519.18 rows=2710 width=4) (never executed) Filter: ((dtl >= $0) AND (dtl < $1) AND (idg = 1)) -> Parallel Seq Scan on lancers_p_2010_001 (cost=0.00..29.04 rows=1 width=4) (never executed) Filter: ((dtl >= $0) AND (dtl < $1) AND (idg = 1)) -> Parallel Seq Scan on lancers_p_2011_001 (cost=0.00..29.04 rows=1 width=4) (never executed) Filter: ((dtl >= $0) AND (dtl < $1) AND (idg = 1)) -> Parallel Seq Scan on lancers_p_2019_001 (cost=0.00..29.04 rows=1 width=4) (never executed) Filter: ((dtl >= $0) AND (dtl < $1) AND (idg = 1)) Planning Time: 3.003 ms Execution Time: 191.398 ms (34 lignes) Temps : 195,151 ms

      La solution la plus évidente est de ne PAS utiliser to_timestamp, c'est à dire adopter une syntaxe basée sur des constantes permettant d'éliminer les partitions dès la phase de planification. Le temps de réponse à l'exécution est alors ici de moins de 80ms.
      Une fausse bonne solution est de redéfinir une fonction STABLE en fonction IMMUTABLE. Cette mauvaise solution est ici uniquement fournie à fin de démonstration mais c'est en règle générale une erreur grave susceptible d'entraîner des résultats faux. Si la communauté PostgreSQL a marqué une fonction comme STABLE, c'est pour d'excellentes raisons.
      Si vous voulez/devez malgré tout utiliser to_timestamp alors vous pouvez utiliser des sous-requêtes ou encore une CTE (with query). Le plan d'exécution obtenu avec EXPLAIN ne semble pas favorable mais nous pouvons constater avec un EXPLAIN ANALYZE que certaines branches du plan ont été écartées pendant la phase d'exécution (never executed). C'est une nouveauté introduite par le "partition pruning" de la version 11, les partitions sont ici éliminées en fonction de ce que renvoient les sous-requêtes. Les temps obtenus, autour de 200ms, sont bien meilleurs que ceux obtenus en appliquant directement la fonction to_timestamp mais cependant moins bons que ceux obtenus avec la syntaxe basée sur des constantes.


Conclusion
      Le partitionnement rend encore plus nécessaire de s'intéresser à nos clauses de filtrage afin de bénéficier au maximum de l'élimination des partitions. Les fonctions et leur degré de volatilité sont plus que jamais des éléments à maîtriser afin d'obtenir les meilleures performances.
      Note : les plus attentifs auront noté que j'ai volontairement écarté les capacités de compilation à la volée (JIT) introduites avec PostgreSQL 11 (set jit=off). JIT est activé par défaut en 11 beta mais pourrait êtrer désactivé par défaut par la suite. Cette nouveauté a parfois donné de mauvais résultats dans les exemples de cette page. La dernière requête donne par exemple un temps de 250ms au lieu de 200ms lorsque jit=on est positionné. JIT donne toutefois d'excellents résultats dans certains cas donc cette option sera à considérer dans le cadre d'optimisations.

Mise à jour : 09/06/2018