PostgreSQL : estimation rapide d'un COUNT(*)

Un peu de précision contre beaucoup de vitesse

Ce billet présente une technique peu orthodoxe pour estimer (très) rapidement le nombre de lignes (rows) dans une table, même avec un filtre WHERE sur des valeurs non indexées. Une explication technique du fonctionnement de l’astuce et de ses limites est proposée en guise de conclusion.

Compter avec COUNT(*)

Voici la configuration des tests qui suivront :

  • PostgreSQL 14.3, tunings par défaut de la distribution ;
  • Fedora Linux 36 (noyau 5.18.13-200.fc36.x86_64) ;
  • disque SSD en NVMe ;
  • CPU Intel(R) Core(TM) i7-10710U en mode performance.

Les specs sont plutôt bonnes et les valeurs nominales peuvent finalement être acceptables dans l’absolu, même si nous allons voir comment augmenter drastiquement la vitesse des comptages.

Définissons une table de la façon suivante : 20 millions de lignes, chacune contenant, en plus de son ID, un nombre aléatoire entre 0 et 100 (colonne g). Cette colonne g n’est pas indexée.

create table grades (id serial, g int);

insert into grades (g)
    select
      random()*100
    from
      generate_series(1, 20000000); -- prend quelques secondes

Compter le tout avec un count(*) prend un peu plus d’une demi-seconde (557 ms) sur ma machine (il faut regarder la ligne Execution Time: 556.957 ms) :

db=> explain analyze select count(*) from grades;
                                                                  QUERY PLAN                                                                  
----------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=193663.38..193663.39 rows=1 width=8) (actual time=555.043..556.939 rows=1 loops=1)
   ->  Gather  (cost=193663.17..193663.38 rows=2 width=8) (actual time=554.956..556.934 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=192663.17..192663.18 rows=1 width=8) (actual time=553.603..553.604 rows=1 loops=3)
               ->  Parallel Seq Scan on grades  (cost=0.00..171829.73 rows=8333373 width=0) (actual time=0.053..346.266 rows=6666667 loops=3)
               
 Planning Time: 0.033 ms
 Execution Time: 556.957 ms
(8 rows)

(Résultat : 20 000 000)

Malgré l’index de la colonne id et l’absence de filtre WHERE qui pourrait nous laisser penser qu’il suffirait de regarder les métadonnées de l’arbre-B pour tout compter, il y a tout de même un parcours séquentiel (Seq Scan) sur l’intégralité des 20M lignes pour les compter. Fort heureusement, PostgreSQL parallélise les accès avec deux workers en l’occurrence (Workers Launched: 2).

Voyons voir le résultat d’une requête similaire, cette fois-ci en ajoutant des filtres pour connaître le nombre de valeurs g comprises dans le quart supérieur (entre 75 et 100) :

select count(*) from grades where g between 75 and 100;
db=> explain analyze select count(*) from grades where g between 75 and 100;
                                                                  QUERY PLAN                                                                  
----------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=219845.64..219845.65 rows=1 width=8) (actual time=428.994..430.907 rows=1 loops=1)
   ->  Gather  (cost=219845.43..219845.64 rows=2 width=8) (actual time=428.900..430.901 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=218845.43..218845.44 rows=1 width=8) (actual time=427.465..427.466 rows=1 loops=3)
               ->  Parallel Seq Scan on grades  (cost=0.00..213496.60 rows=2139531 width=0) (actual time=0.129..370.624 rows=1699696 loops=3)
                     Filter: ((g >= 75) AND (g <= 100))
                     Rows Removed by Filter: 4966971
                     
 Planning Time: 0.044 ms
 Execution Time: 430.924 ms
(10 rows)

(Résultat : 5 099 087)

Cette fois PostgreSQL a vérifié les clauses ((g >= 75) AND (g <= 100)) en chemin, mais le plan d’exécution reste globalement le même. Le plan d’exécution n’est donc pas surprenant, et est donc similaire au premier plan vu précédemment, avec des temps comparables : Execution Time: 430.924 ms.

Tout cela reste quand même assez long, si nous voulions obtenir la distribution en quatre parties, il faudrait presque une bonne seconde d’attente (985 ms) avec la requête suivante1 :

select
    count(*) filter (where g between 0  and 25) as fst,
    count(*) filter (where g between 26  and 50) as snd,
    count(*) filter (where g between 51  and 75) as trd,
    count(*) filter (where g between 76  and 100) as fth
from grades ;
db=> explain analyze select
    count(*) filter (where g between 0  and 25) as fst,
    count(*) filter (where g between 26  and 50) as snd,
    count(*) filter (where g between 51  and 75) as trd,
    count(*) filter (where g between 76  and 100) as fth
 from grades ;
 
[...]

 Planning Time: 0.042 ms
 Execution Time: 984.510 ms
(8 rows)

Résultat (à retenir pour la suite) :

fst (0–25) snd (26–50) trd (51–75) fth (76–100)
5 100 815 5 001 831 4 998 524 4 898 830

Le Planning Time

Avez-vous remarqué le Planning Time ridiculement petit par rapport à l'Execution Time qui reste souvent sous la milliseconde ? Le EXPLAIN ANALYZE calcule le planning time, mais exécute aussi effectivement la requête pour avoir l'execution time. Pour se passer de la seconde analyse, un simple EXPLAIN (sans ANALYZE) le permettra et est presque instantané. Étant donné que la requête ne sera pas exécutée effectivement, nous pouvons nous permettre de requêter directement les résultats avec select * plutôt que select count(*) :

db=> explain select * from grades where g between 76 and 100;
                           QUERY PLAN                            
-----------------------------------------------------------------
 Seq Scan on grades  (cost=0.00..388496.00 rows=4911233 width=8)
   Filter: ((g >= 76) AND (g <= 100))
(2 rows)

Le résultat est instantané et quelque chose saute aux yeux : ce rows=4911233. C’est drôle, nous sommes très proches de la valeur exacte, à savoir 4 898 830 comme vu précédemment. Notre erreur est de 0,25 %. Nous avons économisé plus de 500 ms d’exécution au prix d’une erreur de 0,25 %. Ce n’est pas si mal, c’est même parfaitement acceptable dans bon nombre de situations.

PostgreSQL permet de retourner le résultat sous forme JSON, ce qui permet même d’extraire cette valeur de façon programmatique :

db=> explain (format json) select * from grades where g between 75 and 100;
                  QUERY PLAN                  
----------------------------------------------
 [                                           +
   {                                         +
     "Plan": {                               +
       "Node Type": "Seq Scan",              +
       "Parallel Aware": false,              +
       "Relation Name": "grades",            +
       "Alias": "grades",                    +
       "Startup Cost": 0.00,                 +
       "Total Cost": 388496.00,              +
       "Plan Rows": 4911233,                 +
       "Plan Width": 8,                      +
       "Filter": "((g >= 76) AND (g <= 100))"+
     }                                       +
   }                                         +
 ]
(1 row)

Il n’y a plus qu’à parser cela et extraire le champ [0]["Plan"]["Plan Rows"] pour implémenter l’estimation rapide.

Magique, n’est-ce pas ?

Explication et limites

En se penchant en détail sur le fonctionnement interne de PostgreSQL, quelques éléments d’explication nous apparaissent.

PostgreSQL utilise le mécanisme du MVCC (Multiversion Concurrency Control) pour écrire ou lire les données de façon concurrente en gérant les conflits en se passant d’un lock global (quand c’est possible) qui amoindrirait les performances sur les workloads typiques. Cela signifie qu’il est possible de lire les données d’une table sans être bloqué par une écriture concurrente, et vice versa, le tout en garantissant une cohérence in fine.

Lors de la lecture des données (ce qui constitue une transaction), il faut donc vérifier que chacune des lignes lues appartient bien au snapshot de la transaction en cours (si les lignes sont bien versionnées). Une ligne qui « appartiendrait » à une autre transaction concurrente ne doit ainsi pas être comptabilisée afin de retourner un résultat cohérent.

Cette vérification a un coût non négligeable quand le nombre de lignes à vérifier est important, c’est ce que nous payons ici. Le wiki du PostgreSQL nous dit2 :

The basic SQL standard query to count the rows in a table is:

 SELECT count(*) FROM table_name;

This can be rather slow because PostgreSQL has to check visibility for all rows, due to the MVCC model.

https://wiki.postgresql.org/wiki/Count_estimate

Pour avoir une estimation rapide et se passer des vérifications qui nous ralentissent, nous accédons alors aux données statistiques internes de PostgreSQL qui servent à élaborer les plans retournés pas un EXPLAIN. Ce sont ces statistiques qui lui permettent d’organiser des plans d’exécution efficaces, elles sont mises à jour au fur et à mesure de l’évolution des données, mais pas forcément en temps réel (PostgreSQL s’en occupe lui-même).

Limites

Mais une question se pose alors. Comme nous l’avons vu, la lenteur s’explique par la vérification de chaque ligne dans le cadre du MVCC. Tout semble indiquer que la méthode alternative outrepasse bien cette vérification en se basant uniquement sur les statistiques internes. Mais cela ne risquerait-il pas alors de dégrader la précision de l’estimation si jamais des phases d’écritures concurrentes massives, y compris les UPDATE, avaient lieu ?

Pour rappel, PostgreSQL ne met pas à jour les lignes in situ, il en créées de nouvelles et marquent les anciennes comme obsolètes, lesquelles peuvent tout de même rester visibles pour les transactions antérieures qui sont encore en cours d’exécution. Quand les anciennes lignes ne sont plus nécessaires (si plus aucune transaction n’en n’a besoin), elles peuvent être supprimées physiquement et les statistiques internes se mettent à jour. Mais cette opération coûteuse n’est pas systématiquement instantanée.

Illustrons cela en mettant à jour l’intégralité des lignes de la table en incrémentant chaque g de 1 :

update grades set g = g+1; -- met quelques bonnes secondes

Si nous relançons notre estimation, le résultat est surprenant :

db=> explain select * from grades where g between 76 and 100;
                           QUERY PLAN                            
-----------------------------------------------------------------
 Seq Scan on grades  (cost=0.00..776989.12 rows=9822419 width=8)
   Filter: ((g >= 76) AND (g <= 100))
(2 rows)

Il y a 9 822 419 lignes contre 4 911 233 avant, c’est presque le double ! C’est cohérent avec ce qui vient d’être vu : sans vérifier si chaque ligne est encore à jour dans le contexte d’une transaction donnée, on compte tout sans distinction, à savoir l’à jour et l’obsolète ce qui fait à peu près le double.

Nous l’avons évoqué, PostgreSQL sait faire le ménage et évincer les lignes obsolètes qui n’auront plus besoin d’être là pour d’autres transactions ultérieures. Il s’agit d’un vacuum3, habituellement lancé périodiquement, mais que l’on peut aussi lancer manuellement pour ne pas attendre :

vacuum;

Voyons le résultat après avoir fait passé l’aspirateur :

db=> explain select * from grades where g between 76 and 100;
                           QUERY PLAN                            
-----------------------------------------------------------------
 Seq Scan on grades  (cost=0.00..475472.26 rows=5214400 width=8)
   Filter: ((g >= 76) AND (g <= 100))
(2 rows)

Nous avons 5 214 400 lignes, voilà qui est plus raisonnable.

Sur un workload qui met à jour en masse et régulièrement les données déjà insérées, cette technique est donc à disqualifier. La commande VACUUM peut être assez lente sur des gros jeux de données. Il n’est donc pas si intéressant de systématiquement y avoir recours en vue d’obtenir une estimation des lignes juste après.


  1. La variante présentée semble plus rapide que select sum(case when g between 0 and 25 then 1 else 0 end) as fst, ...
  2. Count estimate
  3. VACUUM — garbage-collect and optionally analyze a database


6 commentaires

Je ne connais pas assez PostgreSQL pour savoir à quel point le parallèle peut être fait mais je sais au moins ceci :

Sous Oracle, c’est facile d’avoir des statistiques pas à jour (surtout sur les bases mal gérées… ce qui est en fait très courant vue la complexité et l’opacité du logiciel). Et des statistiques pas à jour sur une base non-triviale qui contient des requêtes un peu complexes, ça peut très vite tourner à la catastrophe pour cause de plans d’exécution complètement incohérents avec les données réelles, et donc beaucoup plus lents que ce qu’ils devraient être. Par exemple, c’était un problème récurrent dans mon boulot précédent.

Salut,

Est-ce qu’il y a un intérêt opérationnel ? Si la commande est lancée de manière occasionnelle, il me semble qu’une demi-seconde d’attente n’est pas très important. Et sinon, est-ce qu’il ne serait pas mieux de juste indexer la colonne ?

+0 -0

Salut,

Est-ce qu’il y a un intérêt opérationnel ? Si la commande est lancée de manière occasionnelle, il me semble qu’une demi-seconde d’attente n’est pas très important. Et sinon, est-ce qu’il ne serait pas mieux de juste indexer la colonne ?

Moté

Pour ce qui est de tout compter sans filtrer avec WHERE, l’index ne change rien, il ne permet pas de disqualifier des pans inutiles et il faut quand même vérifier que chaque ligne est valide dans le contexte de la transaction en cours.

Mais effectivement on aurait pu indexer g pour gagner un peu de temps sur les comptages avec les filtres WHERE. Plus le range est restreint, plus on gagne du temps, exemple :

db=> explain analyze select count(*) from grades where g between 76 and 100;
                                                                              QUERY PLAN                                                                              
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=93282.59..93282.60 rows=1 width=8) (actual time=134.752..136.585 rows=1 loops=1)
   ->  Gather  (cost=93282.38..93282.59 rows=2 width=8) (actual time=134.730..136.575 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=92282.38..92282.39 rows=1 width=8) (actual time=133.217..133.218 rows=1 loops=3)
               ->  Parallel Index Only Scan using grades_g_idx on grades  (cost=0.44..87112.65 rows=2067889 width=0) (actual time=0.031..80.713 rows=1633881 loops=3)
                     Index Cond: ((g >= 76) AND (g <= 100))
                     Heap Fetches: 0
 Planning Time: 0.081 ms
 Execution Time: 136.604 ms
(10 rows)

Le parcours de l’arbre est bien entendu complètement négligeable ici, toutes ces millisecondes s’expliquent bien par le fait de vérifier quelques ~5 millions de lignes.

Mais on ne rivalise toujours pas face à la méthode de l’estimation qui reste sous la milliseconde (facteur 100+).

Est-ce qu’il y a un intérêt opérationnel ?

Ça, tu es le seul à le savoir selon ton projet ;)

Si la commande est lancée de façon occasionnelle, ça peut être un non-problème. Je précise néanmoins que j’ai ici une machine assez performante. Sur un disque dur SATA ou SAS (qui reste encore d’actualité sur des setups pro avec du gros volume), je serais curieux de voir ce qu’on pourrait obtenir et si ça retesterait aussi satisfaisant qu’une « petite » demi-seconde.

Sous Oracle, c’est facile d’avoir des statistiques pas à jour (surtout sur les bases mal gérées… ce qui est en fait très courant vue la complexité et l’opacité du logiciel). Et des statistiques pas à jour sur une base non-triviale qui contient des requêtes un peu complexes, ça peut très vite tourner à la catastrophe pour cause de plans d’exécution complètement incohérents avec les données réelles, et donc beaucoup plus lents que ce qu’ils devraient être. Par exemple, c’était un problème récurrent dans mon boulot précédent.

SpaceFox

D’après ce qu’on vient de voir, je ne serais pas étonné qu’on ait le même constat à faire avec PostgreSQL sur certains types de workloads. Quand on se renseigne un peu sur les faiblesses de PostgreSQL, le fameux VACUUM est souvent évoqué. Sur certains workloads on accumule beaucoup de dead rows (comme on les appelle), comme après des UPDATE ou des DELETE en masse par exemple, ce qui a tendance à fausser un peu les statistiques jusqu’au prochaine vacuum (qui est généralement laissé à la charge de PostgreSQL, ce qu’on appelle alors l'autovacuum, lequel peut être tuné par les DBA si besoin).

Cela dit, avec PostgreSQL j’ai l’impression que ces problèmes arrivent quand les bases de données commencent à devenir gargantuesques et sur des types de projets très précis. Je suis à peu près sûr qu’un projet comme zds-site pourrait tourner sur PostgreSQL sans que personne n’ait jamais à se soucier de savoir comment tuner l’autovacuum par exemple.

Je ne crois pas qu’InnoDB (le moteur principal de MySQL) souffre de ce problème de dead row justement, car il s’y prend autrement pour modifier les lignes in situ tout en maintenant la cohérence pour les transactions antérieurs encore en cours. Il n’y a donc pas de besoin de passer l’aspirateur de façon asynchrone et régulière sur quoi que ce soit et je suppose que ses statiques internent peuvent ainsi rester cohérentes « en temps réel », au fil des transactions du moment. Je ne préfère pas m’avancer cependant, j’ai bien moins d’expérience avec MySQL/InnoDB.

+0 -0

DBA Postgresql ici. Vaste sujet. Quelques points rapides :

  • Oui @SpaceFox, les erreurs d’estimation sous PG sont parfois un problème, comme sous Oracle : autovacuum qui ne passe pas assez souvent, estimations trop complexes, corner cases divers…

  • Quitte à taper dans les statistiques, pour un bête COUNT(*) de la table, autant aller directement dans les tables système qui servent à l’optimiseur :

-- lignes "vivantes"
SELECT n_live_tup from pg_stat_user_tables  where relname ='t_cent_mille_int'
-- autre estimation
 SELECT * from pg_class where relname ='t_cent_mille_int'

Doc : https://docs.postgresql.fr/15/row-estimation-examples.html

Mais les valeurs sont là aussi dépendantes du bon fonctionnement du VACUUM.

  • @Moté @sgble : Dans les 10 dernières années le vacuum s’est bien amélioré. Notamment il mémorise les blocs nettoyés et pas touchés depuis. Si le démon autovacuum passe assez souvent (ça se tune pour les grosses tables), alors oui, un index devient parfaitement utilisable (et recommandé) pour ce genre de décompte ; il n’y aura pas à repasser dans toute la table pour vérifier toutes les lignes. Pour les tables massivement mises à jour en permanence, y a pas de miracle…

  • @sgble : Oui, les problèmes de vacuum apparaissent surtout quand la volumétrie devient énorme. En toute rigueur, les problèmes de statistiques dépendent du passage de l’ANALYZE et non du VACUUM (même si c’est le même démon qui déclenche l’un ou l’autre). Comme dit plus haut, ça se paramètre. Il faut déjà une belle volumétrie avant de partitionner. Pour de petites/moyennes bases, les plus paranos peuvent programmer un VACUUM ANALYZE global toutes les nuits, ça tient souvent du placebo mais c’est simplissime et ça ne mange pas de pain. Effectivement, le principe du MVCC de PG est complètement différent de MySQL ou Oracle.

  • Pour revenir au sujet initial : des décomptes délibérément approximatifs mais très rapides peuvent se faire avec l’extension HLL (https://dali.bo/x2_html#hll)

Chouette billet, merci du partage ! Par curiosité, as-tu déjà été amené à utiliser une telle approche ? Je me dis que c’est bon à connaitre mais ça ne doit s’appliquer que dans des cas extrêmement spécifiques.

Migwel

Pour être honnête, ce billet traînait dans ma liste depuis plus d’un an donc j’ai dû oublier pourquoi j’avais eu besoin de m’y intéresser, depuis le temps.

Cela dit j’ai déjà eu à faire des gros comptages (distincts ou non, filtrés ou non) sur des bases à plusieurs centaines de millions de lignes (Du Percona MySQL), mais la plupart du temps il fallait l’exactitude (par exemple pour générer des rapports qui ne se contentent pas de l’approximation, même bonne). Les temps d’exécution étaient trop longs, on pouvait donc utiliser d’autres technos autour et maintenir des compteurs pour obtenir ce qu’on voulait. C’est loin d’être idéal, car il faut co-maintenir deux sources de façon cohérente et ça pose tout un tas de problème. Le cas le plus simple est sûrement de dédier une table à cela, ça a au moins l’avantage de pouvoir faire une transaction pour mettre à jour les deux sources de façon logiquement atomique, ce qu’on ne pourrait faire en utilisant une source secondaire (comme Redis ou ElasticSearch par exemple).

Si on avait été sous PostgreSQL, je pense qu’on aurait tenté d’utiliser cette méthode au moins pour faire des estimations de pré-génération de rapport (pour l’UI client, pas très sensible). Notre workload l’aurait peut-être permis.

Pour revenir au sujet initial : des décomptes délibérément approximatifs mais très rapides peuvent se faire avec l’extension HLL (https://dali.bo/x2_html#hll)

Kryysztophe

Je ne savais pas qu’il y avait une extension pour ça donc je serais parti sur l’implémentation de Redis, mais à ma connaissance l’HLL n’approxime qu’un comptage d’éléments distincts.

Quitte à taper dans les statistiques, pour un bête COUNT(*) de la table, autant aller directement dans les tables système qui servent à l’optimiseur :

-- lignes "vivantes"
SELECT n_live_tup from pg_stat_user_tables  where relname ='t_cent_mille_int'
-- autre estimation
SELECT * from pg_class where relname ='t_cent_mille_int'

Ça reste encore le plus ergonomique pour un COUNT(*) sans filtre, cette méthode est aussi « préconisée » dans le page du wiki que j’ai référencé.

+2 -0
Connectez-vous pour pouvoir poster un message.
Connexion

Pas encore membre ?

Créez un compte en une minute pour profiter pleinement de toutes les fonctionnalités de Zeste de Savoir. Ici, tout est gratuit et sans publicité.
Créer un compte