Distinct, group by : accélérer la recherche des différentes années dans une table

[Phil]

Merci à Julien pour sa question :

"J'utilise PostgreSQL 11 et j'ai une table assez grosse appelée ANACHARGE avec une colonne DATEDEBUTMOUVEMENT de type TIMESTAMP. Je dois déterminer les différentes années qu'elle contient afin de créer des partitions mais c'est assez lent même si la colonne DATEDEBUTMOUVEMENT a un index.
Le code de la requête est:
select distinct extract(year from datedebutmouvement) from anacharge;

Comment faire pour accélérer ça ?"

Réponse :
En effet, cette syntaxe directe n'est pas actuellement efficace avec PostgreSQL 11. Il est cependant tout à fait possible de trouver rapidement les différentes années avec un code basé sur une jointure latérale. Il s'agit de constituer un ensemble avec les années possible en déterminant la date minimale et la date maximale puis de vérifier si chaque année potentielle est effectivement présente :

Code : Tout sélectionner

select version();
                                                    version                                                     
----------------------------------------------------------------------------------------------------------------
 PostgreSQL 11.6 (Debian 11.6-0+deb10u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit


\d+
                          Liste des relations
   Schéma   |    Nom    | Type  | Propriétaire | Taille  | Description
------------+-----------+-------+--------------+---------+-------------
extensions | anacharge | table | postgres     | 4042 MB |
 
with
exercices as (select generate_series(extract (year from min(datedebutmouvement))::int, extract (year from max(datedebutmouvement))::int, 1) exercice_potentiel from anacharge)
select exercice_potentiel exercice from exercices e join lateral (select 1 from anacharge a
where a.datedebutmouvement >= date (e.exercice_potentiel || '-01-01')
and a.datedebutmouvement <  date (e.exercice_potentiel+1 || '-01-01') limit 1) x
on (true)
;
--------------------
…
               2020
 
Temps : 3,094 ms

3 millisecondes, ce n'est pas mal du tout.

A comparer avec :

Code : Tout sélectionner

select distinct extract(year from datedebutmouvement) from anacharge;
date_part
-----------
…
      2020
 
Durée : 73962,598 ms (01:13,963)
 

1 minute 13 secondes et ce serait évidemment de pire en pire avec l'augmentation de la volumétrie de la table ANACHARGE.

A noter qu'il serait possible de faire un peu mieux, je vérifie à nouveau dans la requête principale que l'année minimale et l'année maximale sont présentes alors que je sais très bien que c'est le cas...