Optimisation et CTE (with queries)


      Afin d'améliorer la lisibilité de son code SQL, le gobelin Margiono utilise souvent les CTE (common table expressions, with queries).
      Au passage d'un SGBD lambda à PostgreSQL, il se devait d'être particulièrement vigilant. Historiquement, les CTE sont en effet systématiquement matérialisées avec PostgreSQL. Cela signifie qu'un jeu de résultats intermédiaire correspondant à la CTE était toujours constitué. Ce n'est par exemple pas le cas avec Oracle Database, sauf lorsque le hint non documenté /*+ MATERIALIZE */ est utilisé.
      Cadre commun pour cette page, une seule table geants :

create table geants( id integer generated by default as identity primary key, dtn date, genre char(1), taille smallint, masse smallint, actif boolean, devise varchar(128), pw smallint, heureux boolean, couleur varchar(8), veteran boolean, clan smallint, gabarit varchar(8), revenu integer, pm smallint, berserk boolean, tutelaire smallint, ere varchar(10), cyclope boolean); CREATE TABLE WITH serie(i) AS (SELECT generate_series(55000000,1000001,-1)) insert into geants(dtn, genre, taille, masse, actif, devise, pw, heureux, couleur, veteran, clan, gabarit, revenu, pm, berserk, tutelaire, ere, cyclope) select date '2018-01-01' - (ceil(i/3) || ' minutes')::interval + (trunc(random() * 500 + 1) || ' days')::interval, case when random() < 0.45 then 'M' else 'F' end, 200 + (trunc(random() * 200 + 1)), 300 + (trunc(random() * 200 + 1)), case when random() < 0.7 then false else true end, upper(md5(random()::text)), (trunc(random()*100 + 1)), case when random() < 0.1 then false else true end, case when random() < 0.7 then 'GRIS' when random() < 0.8 then 'NOIR' else 'BLEU' end, case when random() < 0.9 then false else true end, (trunc(random()*1000 + 1)), case when random() < 0.1 then 'PETIT' when random() < 0.9 then 'MOYEN' else 'GRAND' end, (trunc(random()*1000000 + 1)), (trunc(random()*10 + 1)), case when random() < 0.01 then true else false end, (trunc(random()*10 + 1)), case when i < 18000000 then 'TAUREAU' when i < 40000000 then 'LICORNE' else 'DRAGON' end, case when random() < 0.05 then true else false end from serie; INSERT 0 54000000 create index geants_i1 on geants(cyclope, dtn) include (id); CREATE INDEX VACUUM ANALYZE geants; VACUUM


      Margiono va s'intéresser aux géants cyclopes nés en 2018 et après. Il a déjà écrit une CTE des cyclopes pour une autre requête, il essaie d'abord de la réutiliser sans la modifier.
      Démonstration avec PostgreSQL 11 :

select version(); version ---------------------------------------------------------------------------------------------------------------- PostgreSQL 11.2 (Debian 11.2-1.pgdg+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.2.0-20) 8.2.0, 64-bit (1 ligne) explain with cyclopes as (select id, dtn from geants where cyclope) select id from cyclopes where dtn >= date '01-01-2018'; QUERY PLAN ----------------------------------------------------------------------------------------------- CTE Scan on cyclopes (cost=89960.60..151520.65 rows=912001 width=4) Filter: (dtn >= '2018-01-01'::date) CTE cyclopes -> Index Only Scan using geants_i1 on geants (cost=0.56..89960.60 rows=2736002 width=8) Index Cond: (cyclope = true) Filter: cyclope with cyclopes as (select id, dtn from geants where cyclope) select id from cyclopes where dtn >= date '01-01-2018'; Durée : 1901,329 ms (00:01,901) Durée : 1937,996 ms (00:01,938) Durée : 1863,773 ms (00:01,864) explain with cyclopes_2018_et_apres as (select id, dtn from geants where cyclope and dtn >= date '01-01-2018') select id from cyclopes_2018_et_apres; QUERY PLAN ------------------------------------------------------------------------------------------- CTE Scan on cyclopes_2018_et_apres (cost=704.77..1100.97 rows=19810 width=4) CTE cyclopes_2018_et_apres -> Index Only Scan using geants_i1 on geants (cost=0.56..704.77 rows=19810 width=8) Index Cond: ((cyclope = true) AND (dtn >= '2018-01-01'::date)) Filter: cyclope with cyclopes_2018_et_apres as (select id, dtn from geants where cyclope and dtn >= date '01-01-2018') select id from cyclopes_2018_et_apres; Temps : 22,845 ms Temps : 22,791 ms Temps : 23,116 ms explain select id, dtn from geants where cyclope and dtn >= date '01-01-2018'; QUERY PLAN ----------------------------------------------------------------------------------- Index Only Scan using geants_i1 on geants (cost=0.56..704.77 rows=19810 width=8) Index Cond: ((cyclope = true) AND (dtn >= '2018-01-01'::date)) Filter: cyclope select id, dtn from geants where cyclope and dtn >= date '01-01-2018'; Temps : 19,161 ms Temps : 19,157 ms Temps : 18,753 ms

      Les requêtes sans CTE ou avec les filtres remontés au niveau de la CTE sont ici BEAUCOUP plus efficaces (près de 100 fois) que la requête avec la CTE d'origine.
      Ce cas est encore une illustration du fait que voir "index" ou même "index only scan" dans un plan ne garantit PAS des performances optimales. En effet, PostgreSQL se sert bien de l'index geants_i1 pour matérialiser la CTE d'origine et récupérer les cyclopes. Mais le meilleur critère est ici celui sur les dates et il n'est pas utilisé via l'index.
      Écrire la requête sans CTE ou en modifiant la CTE permet de pleinement tirer parti de l'index, de ne pas matérialiser de résultats intermédiaires inutiles et de bénéficier de meilleures performances.
      Le planner a donc un comportement très particulier avec les CTE jusque PostgreSQL 11 inclus. En connaissant ce comportement, elles étaient d'ailleurs utilisées comme un moyen de forcer un plan d'exécution.

      Ce comportement devrait changer par défaut avec PostgreSQL 12, encore en préparation au 18/02/2019, cf ce développement d'Andreas Karlsson, Andrew Gierth, David Fetter validé par Tom Lane le 16/02/2019.
      Si la CTE respecte certains critères, si elle n'est utilisée qu'une fois dans la requête principale, si elle n'est pas récursive et si elle ne comporte pas de fonction volatiles, le planner (optimiseur) PostgreSQL va pouvoir choisir de la mettre INLINE, c'est à dire de l'injecter dans le reste de la requête et ainsi bénéficier d'éventuels filtres supplémentaires etc.
      Il est aussi possible de ne pas laisser le planner PostgreSQL décider du comportement à adopter avec les CTE et de le fixer via de nouvelles syntaxes dédiées. Avec "AS MATERIALIZED", la CTE sera toujours matérialisée. Avec "AS NOT MATERIALIZED", elle ne le sera jamais.
      Démonstration avec PostgreSQL 12 devel :

select version(); version ----------------------------------------------------------------------------------------------------------------------------------------------------- PostgreSQL 12devel (Debian 12~~devel~20190218.1158-1~566.git8e6ab9f.pgdg+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.2.0-21) 8.2.0, 64-bit (1 ligne) explain with cyclopes as (select id, dtn from geants where cyclope) select id from cyclopes where dtn >= date '01-01-2018'; QUERY PLAN ----------------------------------------------------------------------------------- Index Only Scan using geants_i1 on geants (cost=0.56..723.59 rows=20351 width=4) Index Cond: ((cyclope = true) AND (dtn >= '2018-01-01'::date)) (2 lignes) with cyclopes as (select id, dtn from geants where cyclope) select id from cyclopes where dtn >= date '01-01-2018'; Temps : 12,528 ms Temps : 12,821 ms Temps : 12,425 ms explain select id, dtn from geants where cyclope and dtn >= date '01-01-2018'; QUERY PLAN ----------------------------------------------------------------------------------- Index Only Scan using geants_i1 on geants (cost=0.56..723.59 rows=20351 width=8) Index Cond: ((cyclope = true) AND (dtn >= '2018-01-01'::date)) (2 lignes) select id, dtn from geants where cyclope and dtn >= date '01-01-2018'; Temps : 14,513 ms Temps : 12,490 ms Temps : 12,947 ms explain with cyclopes_2018_et_apres as (select id, dtn from geants where cyclope and dtn >= date '01-01-2018') select id from cyclopes_2018_et_apres; QUERY PLAN ----------------------------------------------------------------------------------- Index Only Scan using geants_i1 on geants (cost=0.56..723.59 rows=20351 width=4) Index Cond: ((cyclope = true) AND (dtn >= '2018-01-01'::date)) (2 lignes) with cyclopes_2018_et_apres as (select id, dtn from geants where cyclope and dtn >= date '01-01-2018') select id from cyclopes_2018_et_apres; Temps : 13,212 ms Temps : 13,483 ms Temps : 13,886 ms explain with cyclopes as materialized (select id, dtn from geants where cyclope) select id from cyclopes where dtn >= date '01-01-2018'; QUERY PLAN ----------------------------------------------------------------------------------------------- CTE Scan on cyclopes (cost=92507.10..155808.65 rows=937801 width=4) Filter: (dtn >= '2018-01-01'::date) CTE cyclopes -> Index Only Scan using geants_i1 on geants (cost=0.56..92507.10 rows=2813402 width=8) Index Cond: (cyclope = true) JIT: Functions: 8 Options: Inlining false, Optimization false, Expressions true, Deforming true (8 lignes) with cyclopes as materialized (select id, dtn from geants where cyclope) select id from cyclopes where dtn >= date '01-01-2018'; Durée : 1693,077 ms (00:01,693) Durée : 1677,149 ms (00:01,677) Durée : 1684,891 ms (00:01,685) explain with cyclopes as not materialized (select id, dtn from geants where cyclope) select id from cyclopes where dtn >= date '01-01-2018'; QUERY PLAN ----------------------------------------------------------------------------------- Index Only Scan using geants_i1 on geants (cost=0.56..723.59 rows=20351 width=4) Index Cond: ((cyclope = true) AND (dtn >= '2018-01-01'::date)) (2 lignes) with cyclopes as not materialized (select id, dtn from geants where cyclope) select id from cyclopes where dtn >= date '01-01-2018'; Temps : 12,955 ms Temps : 12,337 ms Temps : 12,461 ms

      Le comportement du planner est bien celui attendu, tel que décrit dans la documentation. Les performances sont à présent similaires avec la CTE d'origine, la CTE modifiée et sans CTE.
      Il est toujours possible de revenir à l'ancien comportement avec "AS MATERIALIZED" ou au contraire de forcer PostgreSQL à ne pas matérialiser le jeu de résultats intermédiaire correspondant à la CTE avec "AS NOT MATERIALIZED".

Conclusion

      Cette évolution est vraiment bienvenue et va grandement faciliter l'utilisation des CTE pour tous les usages. Cependant, comme le comportement par défaut devrait être modifié, une grande vigilance sur cet aspect sera nécessaire lors des mises à jour des clusters PostgreSQL de versions 11 et inférieures vers les versions 12 et supérieures.

Mise à jour : 18/02/2018