Indexation
de pattern avec les varchar_patterns et pg_trgm
Créer un index simple sur la colonne contenu de la
table.
CREATE INDEX ON textes(contenu);Il y aura une erreur si la base textes est dans sa
version complète, un livre de Marcel Proust dépasse la taille indexable
maximale :
Pour l’exercice, on supprime ce livre avant d’indexer la colonne :
DELETE FROM textes where livre = 'Les Demi-Vierges, Prévost, Marcel' ;CREATE INDEX ON textes(contenu);
Rechercher un enregistrement commençant par « comme disent » :
l’index est-il utilisé ?
Le plan exact peut dépendre de la version de PostgreSQL, du
paramétrage exact, d’éventuelles modifications à la table. Dans beaucoup
de cas, on obtiendra :
SET jit TO off ;SET max_parallel_workers_per_gather TO 0 ;ANALYZE textes;EXPLAIN ANALYZE SELECT * FROM textes WHERE contenu LIKE 'comme disent%' ;C’est un Seq Scan : l’index n’est pas utilisé !
Dans d’autres cas, on aura ceci (avec PostgreSQL 12 et la version
complète de la base ici) :
EXPLAIN ANALYZE SELECT * FROM textes WHERE contenu LIKE 'comme disent%' ;C’est un Index Scan mais il ne faut pas crier victoire :
l’index est parcouru entièrement (50 000 blocs !). Il ne sert qu’à lire
toutes les valeurs de contenu en lisant moins de blocs que
par un Seq Scan de la table. Le choix de PostgreSQL entre
lire cet index et lire la table dépend notamment du paramétrage et des
tailles respectives.
Le problème est que l’index sur contenu utilise la
collation C et non la collation par défaut de la base,
généralement en_US.UTF-8 ou fr_FR.UTF-8. Pour
contourner cette limitation, PostgreSQL fournit deux classes
d’opérateurs : varchar_pattern_ops pour
varchar et text_pattern_ops pour
text.
Créer un index utilisant la classe text_pattern_ops.
Refaire le test.
DROP INDEX textes_contenu_idx;CREATE INDEX ON textes(contenu text_pattern_ops);EXPLAIN (ANALYZE ,BUFFERS)SELECT * FROM textes WHERE contenu LIKE 'comme disent%' ;On constate que comme l’ordre choisi est l’ordre ASCII, l’optimiseur
sait qu’après « comme disent », c’est « comme disenu » qui apparaît dans
l’index.
Noter que Index Cond contient le filtre utilisé pour
l’index (réexprimé sous forme d’inégalités en collation C)
et Filter un filtrage des résultats de l’index.
On veut chercher les lignes finissant par « Et vivre ». Indexer
reverse(contenu) et trouver les lignes.
Cette recherche n’est possible avec un index B-Tree qu’en utilisant
un index sur fonction :
CREATE INDEX ON textes(reverse (contenu) text_pattern_ops);Il faut ensuite utiliser ce reverse systématiquement
dans les requêtes :
EXPLAIN (ANALYZE )SELECT * FROM textes WHERE reverse (contenu) LIKE reverse ('%Et vivre' ) ;On constate que le résultat de reverse(contenu) a été
directement utilisé par l’optimiseur. La requête est donc très rapide.
On peut utiliser une méthode similaire pour la recherche insensible à la
casse, en utiliser lower() ou upper().
Toutefois, ces méthodes ne permettent de filtrer qu’au début ou à la
fin de la chaîne, ne permettent qu’une recherche sensible ou insensible
à la casse, mais pas les deux simultanément, et imposent aux
développeurs de préciser reverse, lower, etc.
partout.
Installer l’extension pg_trgm, puis créer un index GIN
spécialisé de recherche dans les chaînes. Rechercher toutes les lignes
de texte contenant « Valjean » de façon sensible à la casse, puis
insensible.
Pour installer l’extension pg_trgm :
CREATE EXTENSION pg_trgm;Pour créer un index GIN sur la colonne contenu :
CREATE INDEX idx_textes_trgm ON textes USING gin (contenu gin_trgm_ops);Recherche des lignes contenant « Valjean » de façon sensible à la
casse :
EXPLAIN (ANALYZE )SELECT * FROM textes WHERE contenu LIKE '%Valjean%' ;Puis insensible à la casse :
EXPLAIN ANALYZE SELECT * FROM textes WHERE contenu ILIKE '%Valjean%' ;On constate que l’index a été nettement plus long à créer, et que la
recherche est plus lente. La contrepartie est évidemment que les
trigrammes sont infiniment plus souples. On constate aussi que le
LIKE a dû encore filtrer 1 enregistrement après le parcours
de l’index : en effet l’index trigramme est insensible à la casse, il
ramène donc trop d’enregistrements, et une ligne avec « VALJEAN » a dû
être filtrée.
Rechercher toutes les lignes contenant « Fantine » OU « Valjean » :
on peut utiliser une expression rationnelle.
EXPLAIN ANALYZE SELECT * FROM textes WHERE contenu ~ 'Valjean|Fantine' ;
Rechercher toutes les lignes mentionnant à la fois « Fantine » ET
« Valjean ». Une formulation d’expression rationnelle simple est
« Fantine puis Valjean » ou « Valjean puis Fantine ».
EXPLAIN ANALYZE SELECT * FROM textesWHERE contenu ~ '(Valjean.*Fantine)|(Fantine.*Valjean)' ;auto_explain
Installer le module auto_explain (documentation : https://docs.postgresql.fr/current/auto-explain.html ).
Dans le fichier postgresql.conf, chargement du module et
activation globale pour toutes les requêtes (ce qu’on évitera
de faire en production) :
= 'auto_explain' = 0 Redémarrer PostgreSQL.
Exécuter des requêtes sur n’importe quelle base de données, et
inspecter les traces générées.
Le plan de la moindre requête (même un \d+) doit
apparaître dans la trace.
Passer le niveau de messages de sa session
(client_min_messages) à log.
Il est possible de recevoir les messages directement dans sa session.
Tous les messages de log sont marqués d’un niveau de priorité. Les
messages produits par auto_explain sont au niveau
log. Il suffit donc de passer le paramètre
client_min_messages au niveau log.
Positionner le paramètre de session comme ci-dessous, ré-exécuter la
requête.
SET client_min_messages TO log ;pg_stat_statements
pg_stats_statements nécessite une bibliothèque
préchargée. La positionner dans le fichier postgresql.conf,
redémarrer PostgreSQL et créer l’extension.
Si une autre extension (ici auto_explain) est également
présente, on peut les lister ainsi :
= 'auto_explain,pg_stat_statements' Redémarrer PostgreSQL.
Dans la base postgres (par exemple), créer
l’extension :
CREATE EXTENSION IF NOT EXISTS pg_stat_statements ;
Inspecter le contenu de l’extension
pg_stat_statements (\dx et
\dx+).
Vérifier que le serveur est capable d’activer la
mesure de la durée des entrées-sorties avec pg_test_timing.
Puis l’activer (track_io_timing), sans oublier de
redémarrer PostgreSQL.
pg_test_timing
est livré avec PostgreSQL :
/usr/pgsql-16/bin/pg_test_timing Si le temps de mesure n’est que de quelques dizaines de nanosecondes,
c’est OK. (C’est le cas sur presque toutes les machines et systèmes
d’exploitation actuels, mais il y a parfois des surprises.) Sinon,
éviter de faire ce qui suit sur un serveur de production. Sur une
machine de formation, ce n’est pas un problème.
Dans le fichier postgresql.conf, positionner :
Changer ce paramètre nécessite de redémarrer PostgreSQL.
Depuis un autre terminal, créer une base pgbench
(si pas déjà disponible), l’initialiser (même si elle existait), et
lancer une activité dessus :
# en tant qu'utilisateur postgres createdb -e pgbench/usr/pgsql-16/bin/pgbench -i -s135 pgbench/usr/pgsql-16/bin/pgbench -c5 -j1 pgbench -T 600 -P1
/usr/pgsql-16/bin/pgbench -i -s135 pgbench/usr/pgsql-16/bin/pgbench -c5 -j1 pgbench -T 600 -P1 On a donc 5 clients qui vont mettre à jour la base à raison de 2000
transactions par seconde (valeur très dépendante des CPUs et des
disques).
Dans la vue pg_stat_statements,
récupérer les 5 requêtes les plus gourmandes en temps cumulé sur
l’instance et leur nombre de lignes.
SELECT calls, query , rows ,* interval '1ms' AS tps_totalFROM pg_stat_statementsORDER BY total_exec_time DESC LIMIT 5 Le résultat va dépendre de l’historique de votre instance, et du
temps déroulé depuis le lancement de pgbench, mais c’est
probablement proche de ceci :
Noter que l’unique COPY pour créer la base dure plus que
les centaines de milliers d’occurences de la cinquième requête.
Quelle est la requête générant le plus d’écritures directes sur
disques (written ) ? Et en temps d’écriture ?
Pour les written , il faut tenir compte des trois sources :
blocs du cache partagé, blocs des backends , fichiers
temporaires.
SELECT calls,8192 ::numeric * (shared_blks_written+ local_blks_written+ temp_blks_written)) AS written,8192 ::numeric * shared_blks_written) AS shared_written,8192 ::numeric * temp_blks_written) AS temp_written,* interval '1ms' AS blk_write_time,* interval '1ms' AS temp_blk_write_time,query FROM pg_stat_statementsORDER BY shared_blks_written+ local_blks_written+ temp_blks_written DESC LIMIT 3 ;Il y a donc beaucoup d’écritures directes. C’est le signe que le
cache en écriture de PostgreSQL est insuffisant (la base fait 2 Go, à
peu près intégralement balayée, et le shared_buffers par
défaut ne fait que 128 Mo) ou que le background writer doit
être modifié pour nettoyer plus souvent les blocs dirty .
On note que l’UPDATE et le COPY ont écrit
des blocs qui auraient dû passer uniquement par le cache, alors le
ALTER TABLE, lui, a essentiellement écrit un fichier
temporaire (c’est logique lors d’une création d’index).
Avec des shared buffers plus importants, les
shared_written sont quasiment absents. Ils proviennent
essentiellement d’ordres lourds comme COPY.
Quel est le hit ratio des requêtes les plus fréquentes ?
SELECT calls, total_exec_time,round (100.0 * shared_blks_hit/ nullif (shared_blks_hit+ shared_blks_read, 0 ),2 ) AS "hit %" ,query FROM pg_stat_statementsORDER BY total_exec_time DESC LIMIT 5 ;On constate que le hit ratio est parfait, sauf la première
requête. C’est logique, car la table pgbench_accounts ne
tient pas dans le cache par défaut et elle est balayée à peu près
entièrement par les requêtes de pgbench.
PL/Python, import
de page web et compression
Sur la base du code suivant en python 3 utilisant un des modules
standard (documentation : https://docs.python.org/3/library/urllib.request.html ),
créer une fonction PL/Python récupérant le code HTML d’une page web avec
un simple
SELECT pageweb('https://www.postgresql.org/') :
import urllib.request= urllib.request.urlopen('https://www.postgresql.org/' )print (f.read().decode('utf-8' ))
Il faut bien évidemment que PL/Python soit installé. D’abord le
paquet, ici sous Rocky Linux 8 avec PostgreSQL 14 :
# dnf install postgresql14-plpython3Sur Debian et dérivés ce sera :
# apt install postgresql-plpython3-14Puis, dans la base de données concernée :
CREATE EXTENSION plpython3u ;La fonction PL/Python est :
CREATE OR REPLACE FUNCTION pageweb (url text)AS $$= urllib.request.urlopen(url)return f.read ().decode ('utf-8' )COST 10000 ;Évidemment, il ne s’agit que d’un squelette ne gérant pas les
erreurs, les redirections, etc.
Stocker le résultat dans une table.
On vérifie ainsi le bon fonctionnement :
CREATE TABLE pagesweb (url text, page text, pagebz2 bytea, page2 text ) ;INSERT INTO pagesweb (url, page)SELECT 'https://www.postgresql.org/' , pageweb('https://www.postgresql.org/' ) ;
Puis stocker cette page en compression maximale dans un champ
bytea, en passant par une fonction python inspirée du code
suivant (documentation : https://docs.python.org/3/library/bz2.html ) :
import bz2= bz2.compress(data, compresslevel= 9 )
import bz2= bz2.compress(data, compresslevel= 9 )Même si la page récupérée est en texte, la fonction python exige du
binaire, donc le champ en entrée sera du bytea :
-- version pour bytea CREATE OR REPLACE FUNCTION bz2 (objet bytea)AS $$return bz2.compress (objet, compresslevel= 9 )COST 1000000 ;On peut faire la conversion depuis text à l’appel ou
modifier la fonction pour qu’elle convertisse d’elle-même. Mais le plus
confortable est de créer une fonction SQL de même nom qui se chargera de
la conversion. Selon le type en paramètre, l’une ou l’autre fonction
sera appelée.
-- fonction d'enrobage pour s'épargner une conversion explicite en bytea CREATE OR REPLACE FUNCTION bz2 (objet text)AS $$SELECT bz2(objet::bytea ) ;Compression de la page :
UPDATE pageswebSET pagebz2 = bz2 (page) ;
NB : PostgreSQL stocke déjà les textes longs sous forme compressée
(mécanisme du TOAST).
Tout ceci n’a donc d’intérêt que pour gagner quelques octets
supplémentaires, ou si le .bz2 doit être réutilisé
directement. Noter que l’on utilise ici uniquement des fonctionnalités
standards de PostgreSQL et python3, sans module extérieur à la fiabilité
inconnue.
De plus, les données ne quittent pas le serveur, épargnant du trafic
réseau.
Écrire la fonction de décompression avec la fonction python
bz2.decompress.
CREATE OR REPLACE FUNCTION bz2d (objet bytea)AS $$return bz2.decompress(objet)COST 1000000 ;
Utiliser ensuite convert_from( bytea, 'UTF8') pour
récupérer un text.
CREATE OR REPLACE FUNCTION bz2_to_text (objetbz2 bytea)AS $$SELECT convert_from( bz2d(objetbz2), 'UTF8' )Vérification que l’on obtient au final le même texte qu’avant
compression :
UPDATE pageswebSET page2 = bz2_to_text( pagebz2 )-- Vérification que la page décompressée est identique à l'originale SELECT count (* ) AS pages,count (* ) FILTER (WHERE page = page2) AS pages_identiqueFROM pagesweb ;
Créer la fonction multi_replace en PL/pgSQL à partir du
wiki PostgreSQL : https://wiki.postgresql.org/wiki/Multi_Replace_plpgsql
Le code sur le wiki est le suivant :
/* This function quotes characters that may be interpreted as special in a regular expression. It's used by the function below and declared separately for clarity. */ CREATE FUNCTION quote_meta(text) RETURNS text AS $$SELECT regexp_replace ($1 , '([\[\] \\ \^\$\.\| \? \*\+\(\)])' , ' \\ \1' , 'g' );/* Substitute a set of substrings within a larger string. When several strings match, the longest wins. Similar to php's strtr(string $str, array $replace_pairs). Example: select multi_replace('foo and bar is not foobar', '{"bar":"foo", "foo":"bar", "foobar":"foobar"}'::jsonb); => 'bar and foo is not foobar' */ CREATE FUNCTION multi_replace(str text, substitutions jsonb)AS $$DECLARE := '' ;BEGIN SELECT string_agg(quote_meta(term), '|' )FROM jsonb_object_keys(substitutions) AS x(term)WHERE term <> '' INTO rx;IF (COALESCE (rx, '' ) = '' ) THEN -- the loop on the RE can't work with an empty alternation RETURN str;END IF ;:= concat ('^(.*?)(' , rx, ')(.*)$' ); -- match no more than 1 row loop := str;SELECT concat (matches[1 ], substitutions->> matches[2 ]),3 ]FROM 'g' ) AS matchesINTO s_left, str;WHEN s_left IS NULL ;:= res || s_left;END loop ;:= res || s_tail;RETURN res;END
Récupérer la fonction en PL/perl sur le même wiki : https://wiki.postgresql.org/wiki/Multi_Replace_Perl .
Évidemment, il faudra l’extension dédiée au langage Perl :
# dnf install postgresql14-plperlLe code de la fonction est :
CREATE FUNCTION multi_replace(string text, orig text[], repl text[])AS $BODY$= @_;if (@$orig != @$repl) {"array sizes mismatch" );if (ref @$orig[0 ] eq 'ARRAY' || ref @$repl[0 ] eq 'ARRAY' ) {"array dimensions mismatch" );= @$repl;= join "|" , map quotemeta,sort { (length ($b) <=> length ($a)) } keys %subs;= qr/ ($re)/ ;= ~ s/ $re/ $subs{$1 }/ g;return $string;
Vérifier que les deux fonctions ont le même nom mais des types de
paramètres différents.
PostgreSQL sait quelle fonction appeler selon les paramètres
fournis.
Le test va consister à transposer tous les noms et lieux des
Misérables de Victor Hugo dans une version américaine :
Le test va consister à transposer tous les noms et lieux des
Misérables de Victor Hugo dans une version américaine :
charger la base du projet Gutenberg si elle n’est pas déjà en
place.
créer une table miserables reprenant tous les livres
dont le titre commence par « Les misérables ».
CREATE TABLE miserables as select * from textesWHERE livre LIKE 'Les misérables%' ;Cette table fait 68 000 lignes.
Tester le bon fonctionnement avec ces requêtes :
SELECT multi_replace (contenu,'{"Valjean":"Valjohn", "Cosette":"Lucy"}' ::jsonb )FROM miserablesWHERE contenu ~ '(Valjean|Cosette)' LIMIT 5 ;SELECT multi_replace(contenu, '{Valjean,Cosette}' , '{Valjohn, Lucy}' )FROM miserablesWHERE contenu ~ '(Valjean|Cosette)' LIMIT 5 ;
Le texte affiché doit comporter « Jean Valjohn » et « Lucy ».
Pour faciliter la modification, prévoir une table pour stocker les
critères :
CREATE TABLE remplacement (j jsonb, old_t text[], new_t text[]) ;
Insérer par exemple les données suivantes :
INSERT INTO remplacement (j)SELECT '{"Valjean":"Valjohn", "Jean Valjean":"John Valjohn", "Cosette":"Lucy", "Fantine":"Fanny", "Javert":"Green", "Thénardier":"Thenardy", "Éponine":"Sharon", "Azelma":"Azealia", "Marius":"Marc", "Gavroche":"Garry", "Enjolras":"Joker", "Notre-Dame":"Empire State Building", "Victor Hugo":"Victor Hugues", "Hugo":"Hugues", "Fauchelevent":"Dropwind", "Bouchart":"Butcher", "Célestine":"Celeste","Mabeuf":"Myoax", "Leblanc":"White", "Combeferre":"Combiron", "Magloire":"Glory", "Gillenormand":"Jillnorthman", "France":"États-Unis", "Paris":"New York", "Louis Philippe":"Andrew Jackson" }' ::jsonb ;
Copier le contenu sous forme de tableau de caractères dans les autres
champs :
UPDATE remplacementSET old_t = noms_old , new_t = noms_newFROM (SELECT array_agg (key ) AS noms_old, array_agg (value ) AS noms_newFROM (SELECT (jsonb_each_text (j)).* FROM remplacement
On vérifie le contenu :
SELECT * FROM remplacement \gx
Comparer la performance des deux fonctions suivantes :
off -- fonction en PL∕perl EXPLAIN (ANALYZE , BUFFERS)SELECT multi_replace (contenu, (SELECT j FROM remplacement))FROM miserables ;-- fonction en PL/pgSQL EXPLAIN (ANALYZE , BUFFERS)SELECT multi_replace (contenu,SELECT old_t FROM remplacement),SELECT new_t FROM remplacement) )FROM miserables ;
off EXPLAIN (ANALYZE , BUFFERS)SELECT multi_replace (contenu, (SELECT j FROM remplacement))FROM miserables ;EXPLAIN (ANALYZE , BUFFERS)SELECT multi_replace (contenu,SELECT old_t FROM remplacement),SELECT new_t FROM remplacement) )FROM miserables ;Selon les performances de la machine, les résultats peuvent varier,
mais la première (en PL/perl) est probablement plus rapide. La fonction
en PL/perl montre son intérêt quand il y a beaucoup de
substitutions.
hll
Installer l’extension hll dans la
base de données de test :
le paquet est hll_14 ou
postgresql-14-hll (ou l’équivalent pour les autres numéros
de versions) selon la distribution ;
l’extension se nomme hll ;
elle nécessite d’être préalablement déclarée
dans shared_preload_libraries.
Sur Rocky Linux et autres dérivés Red Hat :
Sur Debian et dérivés :
# apt install postgresql-14-hllModifier postgresql.conf ainsi afin que la bibliothèque
soit préchargée dès le démarrage du serveur :
shared_preload_libraries = 'hll' Redémarrer PostgreSQL.
Installer l’extension dans la base :
Créer un jeu de données simulant des voyages en
transport en commun, par passager selon la date :
CREATE TABLE voyagesGENERATED ALWAYS AS IDENTITY,date INSERT INTO voyages (passager_id, d)SELECT sem+ mod (i, sem+ 1 ) || '-' || mod (i,77777 ) AS passager_id, dFROM generate_series (0 ,51 ) sem,SELECT i,'2019-01-01' ::date + sem * interval '7 days' + i * interval '2s' AS dFROM generate_series (1 ,case when sem in (31 ,32 ,33 ) then 0 else 22 end + abs (30 - sem))* 5000 ) iCette table de 9 millions de voyages étalés de janvier à décembre
2019 pèse 442 Mo.
Activer l’affichage du temps
(timing).
Désactiver JIT et le parallélisme.
Passer la mémoire de tri à 1 Go.
Précharger la table dans le cache de
PostgreSQL.
on SET max_parallel_workers_per_gather TO 0 ;SET jit TO off ;SET work_mem TO '1GB' ;CREATE EXTENSION pg_prewarm ;SELECT pg_prewarm('voyages' ) ;
Calculer, par mois, le nombre exact de voyages et
de passagers distincts .
Dans le plan de la requête, chercher où est perdu
le temps.
SELECT 'month' , d)::date AS mois,COUNT (* ) AS nb_voyages,count (DISTINCT passager_id) AS nb_d_passagers_moisFROM voyagesGROUP BY 1 ORDER BY 1 ;Le plan de cette même requête avec
EXPLAIN (ANALYZE, BUFFERS) est :
Le plan est visible sur https://explain.dalibo.com/plan/Hj (pour PostgreSQL 14).
Il suppose que shared_buffers est assez grand pour que tous
tous les accès se fassent en mémoire (shared hits ). Le
work_mem élevé permet que le tri des 765 Mo soit aussi en
mémoire. Le cas est donc idéal. L’essentiel du temps est perdu en tri.
Pour donner une idée de la lourdeur d’un
COUNT(DISTINCT) : un décompte non distinct (qui revient à
calculer le nombre de voyages) prend sur la même machine 5 secondes,
même moins si le parallélisme est utilisé, mais ce qu’un
COUNT (DISTINCT) ne permet pas.
Calculer, pour l’année, le nombre exact de voyages
et de passagers distincts .
SELECT COUNT (* ) AS nb_voyages,COUNT (DISTINCT passager_id) AS nb_d_passagers_anneeFROM voyages;On a donc plusieurs millions de voyages chaque mois, répartis sur
quelques centaines de milliers de passagers mensuels, qui ne totalisent
que 4,7 millions de personnes distinctes. Il y a donc un fort turn-over
tout au long de l’année sans que ce soit un renouvellement complet d’un
mois sur l’autre.
Recompter les passagers dans les deux cas en
remplaçant le COUNT(DISTINCT) par cette expression :
hll_cardinality(hll_add_agg(hll_hash_text(passager_id)))::int
Les ID des passagers sont hachés, agrégés, et le calcul de
cardinalité se fait sur l’ensemble complet.
SELECT 'month' , d)::date AS mois,:int AS nb_d_passagers_moisFROM voyagesGROUP BY 1 ORDER BY 1 ;L’accélération est foudroyante (facteur 10 ici). Les chiffres sont
différents, mais très proches (écart souvent inférieur à 1 %, au maximum
2,8 %).
Le plan indique un parcours de table et un agrégat par hachage :
Pour l’année, on a un résultat similaire :
SELECT :int AS nb_d_passagers_anneeFROM voyages;L’écart est de 1,8 % pour une durée réduite d’un facteur 40. Cet
écart est-il acceptable pour les besoins applicatifs ? C’est un choix
fonctionnel. On peut d’ailleurs agir dessus.
Réexécuter les requêtes après modification du paramétrage de
hll :
SELECT hll_set_defaults(17 , 5 , - 1 , 0 );
Les défauts sont :
log2m=11, regwidth=5, expthresh=-1, sparseon=1.
La requête mensuelle dure à peine plus longtemps (environ 6 s sur la
machine de test) pour un écart par rapport à la réalité de l’ordre de
0,02 à 0,6 %.
La requête sur l’année dure environ le même temps pour seulement
0,2 % d’erreur cette fois :
Selon les cas et après des tests soigneux, on testera donc l’intérêt
de modifier ces paramètres tels que décrits sur le site du projet : https://github.com/citusdata/postgresql-hll
Créer une table d’agrégat par mois avec un champ
d’agrégat hll et la remplir.
CREATE TABLE voyages_moisdate ,int ,INSERT INTO voyages_moisSELECT 'month' , d)::date ,COUNT (DISTINCT passager_id),FROM voyagesGROUP BY 1 ;Cette table d’agrégat n’a que 12 lignes mais contient un champ de
type hll agrégeant les passager_id de ce mois.
Sa taille n’est que d’1 Mo :
À partir de cette table d’agrégat :
calculer le nombre moyen mensuel de passagers distincts,
recalculer le nombre de passagers distincts sur l’année à partir de
cette table d’agrégat.
La fonction pour agréger des champs de type hll est
hll_union_agg. La requête est donc :
SELECT AVG (nb_exact_passagers_mois)::int AS passagers_mois_moyen,:int AS nb_passagers_annuelsFROM voyages_mois ;L’extension HyperLogLog permet donc d’utiliser des tables d’agrégat
pour un COUNT(DISTINCT). De manière presque instantanée, on
retrouve la même estimation presque parfaite que ci-dessus. Il aurait
été impossible de la recalculer depuis la table d’agrégat (au contraire
de la moyenne par mois, ou d’une somme du nombre de voyages).
Avec une fonction de fenêtrage sur hll_union_agg,
calculer une moyenne glissante sur 3 mois du nombre de passagers
distincts.
SELECT mois,CASE WHEN ROW_NUMBER () OVER () > 2 THEN OVER (ORDER BY mois ASC ROWS 2 PRECEDING ) )::bigint ELSE null END AS nb_d_passagers_3_mois_glissantsFROM voyages_moisUn COUNT(DISTINCT) avec une fonction de fenêtrage n’est
en pratique pas faisable, en tout cas pas aussi aisément, et bien plus
lentement.
