Certains types de requêtes sont condamnées au seq scan (parcours de table complet), par exemple des requêtes décisionnelles portant sur un historique assez long. Mais on n’a pas forcément envie de parcourir tout l’historique non plus, et les index ne peuvent pas tout. Les tables énormes sont également peu maniables (VACUUM FULL impossible, difficulté à les répartir sur des disques différents…). Pour accélérer malgré tout ces requêtes, tout en se simplifiant l’administration, il est plus confortable de tronçonner la table en partitions, stockées parfois dans différents tablespaces, par exemple en données mensuelles que l’on pourra au besoin parcourir intégralement.
C’est le but du tout nouveau partitionnement déclaratif de PostgreSQL 10. Il y avait déjà un système de partitionnement par héritage, mais peu pratique et peu utilisé.
Mais jusqu’où peut-on monter dans le nombre de partitions ? (Spoiler : pas trop haut).
Petit test :
Config
Mon portable raisonnablement récent a 2 cœurs 4 threads et SSD fait tourner un PostgreSQL 10.3 avec shared_buffers = 512 Mo ; et pour figer les choses :
SET maxparallelworkers to 4 ; SET maxparallelworkerspergather to 4 ; SET work_mem to '128MB' ; SET effectiveioconcurrency TO 100; SET randompagecost TO 0.1 ; SET seqpagecost TO 0.1 ;
Enfin, ce paramètre ne vise qu’à accélérer les tests ; ne faites pas en prod sans savoir ce que cela implique :
SET synchronous_commit TO off;
Sans partition
Premier exemple avec 500 millions de petites lignes dans une table monolithique de 2 212 390 blocs et 17 Go, sans index :
CREATE TABLE bigtable (id int, x int);
INSERT INTO bigtable SELECT i, mod (i, 777777) FROM generate_series (1, 500*1000*1000) i;
\d+ Liste des relations Schéma | Nom | Type | Propriétaire | Taille | Description public | bigtable | table | postgres | 17 GB |
Ne pas oublier les statistiques, par principe après chaque insertion massive (ça ne prend qu’un quart d’heure ici, après tout) :
VACUUM VERBOSE ANALYZE bigtable ;
Dans ce qui suit, le manque d’index est délibéré, je ne recherche que des parcours de table complets.
On commence petit, en sommant juste 100 lignes en plein milieu de la table. Je ne m’intéresse pas au contenu mais à la manière dont PostgreSQL va se débrouiller, donc je demande le plan avec tous les détails :
EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT sum(x)FROM bigtable WHERE id BETWEEN 199000001 AND 199000100 ;
Et on n’est pas déçu : 33 secondes passées à scanner intégralement la table, 4 workers partageant avec le processus maître la lecture et le filtrage des lignes, puis la somme.
Finalize Aggregate (cost=2097239.49..2097239.50 rows=1 width=8) (actual time=33015.147..33015.148 rows=1 loops=1)
Output: sum(x)
Buffers: shared hit=14373 read=430763 dirtied=936 written=11
I/O Timings: read=21404.854 write=0.075
-> Gather (cost=2097239.07..2097239.48 rows=4 width=8) (actual time=33015.001..33015.142 rows=5 loops=1)
Output: (PARTIAL sum(x))
Workers Planned: 4
Workers Launched: 4
Buffers: shared hit=14373 read=430763 dirtied=936 written=11
I/O Timings: read=21404.854 write=0.075
-> Partial Aggregate (cost=2096239.07..2096239.08 rows=1 width=8) (actual time=32990.839..32990.839 rows=1 loops=5)
Output: PARTIAL sum(x)
Buffers: shared hit=55304 read=2157086 dirtied=5855 written=30
I/O Timings: read=107067.211 write=0.200
Worker 0: actual time=32989.611..32989.612 rows=1 loops=1
Buffers: shared hit=12089 read=426532 dirtied=1361 written=19
I/O Timings: read=21461.396 write=0.125
Worker 1: actual time=32986.118..32986.118 rows=1 loops=1
Buffers: shared hit=6723 read=416321 dirtied=1310
I/O Timings: read=21437.665
Worker 2: actual time=32974.608..32974.608 rows=1 loops=1
Buffers: shared hit=8438 read=441747 dirtied=1278
I/O Timings: read=21433.336
Worker 3: actual time=32989.476..32989.476 rows=1 loops=1
Buffers: shared hit=13681 read=441723 dirtied=970
I/O Timings: read=21329.960
-> Parallel Seq Scan on public.bigtable (cost=0.00..2096239.00 rows=29 width=4) (actual time=26784.787..32990.833 rows=20 loops=5)
Output: x
Filter:  AND (bigtable.id <= 199000100)
Rows Removed by Filter: 99999980
Buffers: shared hit=55304 read=2157086 dirtied=5855 written=30
I/O Timings: read=107067.211 write=0.200
Worker 0: actual time=17474.477..32989.605 rows=6 loops=1
Buffers: shared hit=12089 read=426532 dirtied=1361 written=19
I/O Timings: read=21461.396 write=0.125
Worker 1: actual time=17471.005..32986.104 rows=94 loops=1
Buffers: shared hit=6723 read=416321 dirtied=1310
I/O Timings: read=21437.665
Worker 2: actual time=32974.603..32974.603 rows=0 loops=1
Buffers: shared hit=8438 read=441747 dirtied=1278
I/O Timings: read=21433.336
Worker 3: actual time=32989.472..32989.472 rows=0 loops=1
Buffers: shared hit=13681 read=441723 dirtied=970
I/O Timings: read=21329.960
Planning time: 0.584 ms
Execution time: 33034.340 ms
Et encore, ce pourrait être pire sans le parallélisme (on passerait à 45 secondes). Dans l’autre sens, PostgreSQL utiliserait 8 workers si maxworkersper_gather le permettait (mais je n’ai que 4 threads sur cette machine, il y en a même un de trop ici).
Notez le temps de planification ridicule d’une demi-milliseconde.
3 partitions
Avant d’en créer plus, j’ai voulu tester avec juste 3 partitions, dont une ne représentant qu’un centième de la volumétrie, soit 5 millions de lignes. On recrée la table :
DROP TABLE bigtable ; CREATE TABLE bigtable (id int, x int) PARTITION BY RANGE (id);
Le partitionnement nouveau style (par range ici) est tout bête avec juste 3 partitions ; il faut savoir que les bornes supérieures sont exclues :
CREATE TABLE bigtable_grande1 PARTITION OF bigtable FOR VALUES FROM (1) TO (199000000) ;
CREATE TABLE bigtable_petite PARTITION OF bigtable FOR VALUES FROM (199000000) TO (200000001) ;
CREATE TABLE bigtable_grande2 PARTITION OF bigtable FOR VALUES FROM (200000001) TO (500000001) ;
L’insertion de données se fait de la même manière. Qu’il suffise de dire que le temps d’insertion est à peine plus élevé (autour de 10 minutes contre 9 et demi), ce qui est un des plus gros progrès par rapport à l’ancienne méthode de partionnement.
INSERT INTO bigtable SELECT i, mod (i, 777777) FROM generate_series (1, 500*1000*1000) i; VACUUM VERBOSE ANALYZE bigtable ;
Chaque table (la partitionnée et ses filles) apparaissent comme des tables indépendantes :
Schéma | Nom | Type | Propriétaire | Taille | Description public | bigtable | table | postgres | 0 bytes | public | bigtable_grande1 | table | postgres | 6915 MB | public | bigtable_grande2 | table | postgres | 10200 MB | public | bigtable_petite | table | postgres | 173 MB |
Si je relance un SELECT sur 100 lignes de la petite partition centrale, PostgreSQL ignore totalement les deux grosses partitions, et ne lance que 3 workers supplémentaires pour faire le filtrage et la somme (la partition n’est pas assez grande pour en justifier un quatrième). On descend à 529 ms (62 fois mieux qu’auparavant). Notez le temps de planification toujours bien inférieur à la milliseconde.
EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT sum(x)FROM bigtable WHERE id BETWEEN 200000000 AND 200000000+100 ;
Aggregate (cost=27406.06..27406.07 rows=1 width=8) (actual time=527.984..527.984 rows=1 loops=1)
Output: sum(bigtable_petite.x)
Buffers: shared read=5881
I/O Timings: read=344.078
-> Gather (cost=1000.00..27406.05 rows=1 width=4) (actual time=3.473..527.966 rows=101 loops=1)
Output: bigtable_petite.x
Workers Planned: 3
Workers Launched: 3
Buffers: shared read=5881
I/O Timings: read=344.078
-> Append (cost=0.00..26405.95 rows=1 width=4) (actual time=383.565..514.677 rows=25 loops=4)
Buffers: shared hit=32 read=22092
I/O Timings: read=1332.761
Worker 0: actual time=510.606..510.606 rows=0 loops=1
Buffers: shared hit=10 read=5752
I/O Timings: read=328.896
Worker 1: actual time=511.069..511.069 rows=0 loops=1
Buffers: shared hit=10 read=4978
I/O Timings: read=325.920
Worker 2: actual time=510.807..510.807 rows=0 loops=1
Buffers: shared hit=12 read=5481
I/O Timings: read=333.869
-> Parallel Seq Scan on public.bigtable_petite (cost=0.00..26405.95 rows=1 width=4) (actual time=383.564..514.674 rows=25 loops=4)
Output: bigtable_petite.x
Filter:  AND (bigtable_petite.id <= 200000100)
Rows Removed by Filter: 1249975
Buffers: shared hit=32 read=22092
I/O Timings: read=1332.761
Worker 0: actual time=510.606..510.606 rows=0 loops=1
Buffers: shared hit=10 read=5752
I/O Timings: read=328.896
Worker 1: actual time=511.069..511.069 rows=0 loops=1
Buffers: shared hit=10 read=4978
I/O Timings: read=325.920
Worker 2: actual time=510.806..510.806 rows=0 loops=1
Buffers: shared hit=12 read=5481
I/O Timings: read=333.869
Planning time: 0.728 ms
Execution time: 529.829 ms
Une petite partie des blocs était dans les shared buffers (j’avais pourtant pris des données « au milieu » pour éviter cet effet). Répétée souvent, la requête finit par n’utiliser que des blocs en cache et peut descendre en-dessous de 200 ms.
Mais on ne fait pas des partitions pour des requêtes de 100 lignes, il y a les index pour ça. Si nous voulons l’intégralité de la partition centrale :
EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT sum(x)FROM bigtable WHERE id BETWEEN 200000000 AND 205000000 ;
Le résultat est à peine plus lent (607 ms), même sans lire uniquement dans le cache de PostgreSQL (mais le cache de l’OS a pu jouer). Remarquez que le plan est subtilement différent et rajoute un nœud Partial Aggregate entre le Append et le Gather car le Append ramène plus de ligne que ci-dessus ; et on a un nœud Finalize Aggregate tout en haut (détails : https://docs.postgresql.fr/10/paral…) :
Finalize Aggregate (cost=31437.72..31437.73 rows=1 width=8) (actual time=605.167..605.167 rows=1 loops=1)
Output: sum(bigtable_petite.x)
Buffers: shared hit=66 read=5522
I/O Timings: read=13.272
-> Gather (cost=31437.41..31437.72 rows=3 width=8) (actual time=605.158..605.164 rows=4 loops=1)
Output: (PARTIAL sum(bigtable_petite.x))
Workers Planned: 3
Workers Launched: 3
Buffers: shared hit=66 read=5522
I/O Timings: read=13.272
-> Partial Aggregate (cost=30437.41..30437.42 rows=1 width=8) (actual time=600.835..600.835 rows=1 loops=4)
Output: PARTIAL sum(bigtable_petite.x)
Buffers: shared hit=288 read=21836
I/O Timings: read=53.247
Worker 0: actual time=599.521..599.521 rows=1 loops=1
Buffers: shared hit=77 read=5420
I/O Timings: read=12.596
Worker 1: actual time=598.198..598.198 rows=1 loops=1
Buffers: shared hit=72 read=5533
I/O Timings: read=14.573
Worker 2: actual time=600.653..600.653 rows=1 loops=1
Buffers: shared hit=73 read=5361
I/O Timings: read=12.805
-> Append (cost=0.00..26405.95 rows=1612581 width=4) (actual time=0.021..434.163 rows=1250000 loops=4)
Buffers: shared hit=288 read=21836
I/O Timings: read=53.247
Worker 0: actual time=0.021..432.354 rows=1242322 loops=1
Buffers: shared hit=77 read=5420
I/O Timings: read=12.596
Worker 1: actual time=0.026..429.925 rows=1266730 loops=1
Buffers: shared hit=72 read=5533
I/O Timings: read=14.573
Worker 2: actual time=0.019..437.050 rows=1228061 loops=1
Buffers: shared hit=73 read=5361
I/O Timings: read=12.805
-> Parallel Seq Scan on public.bigtable_petite (cost=0.00..26405.95 rows=1612581 width=4) (actual time=0.020..300.729 rows=1250000 loops=4)
Output: bigtable_petite.x
Filter:  AND (bigtable_petite.id <= 205000000)
Buffers: shared hit=288 read=21836
I/O Timings: read=53.247
Worker 0: actual time=0.021..298.749 rows=1242322 loops=1
Buffers: shared hit=77 read=5420
I/O Timings: read=12.596
Worker 1: actual time=0.025..298.769 rows=1266730 loops=1
Buffers: shared hit=72 read=5533
I/O Timings: read=14.573
Worker 2: actual time=0.019..302.519 rows=1228061 loops=1
Buffers: shared hit=73 read=5361
I/O Timings: read=12.805
Planning time: 0.116 ms
Execution time: 607.095 ms
Noter que le temps de planification reste en-dessous de la milliseconde dans toutes ces requêtes.
100 partitions
C’est plus réaliste, mais cette fois il va falloir un peu scripter pour créer les partitions (non, la création automatique de partitions n’est pas (encore ?) prévue) ; et merci au passage à Corey Huinker pour \gexec :
SELECT 'CREATE TABLE bigtable_'||p||'
PARTITION OF bigtable
FOR VALUES FROM ('||p*5000000+1||') TO ('||(p+1)*5000000+1||') ;'
FROM generate_series (0,99) p
\gexec
Le reste de la création est identique, y compris le temps d’insertion similaire. Au final on obtient 100 partitions d’1 million de lignes et 173 Mo :
Schéma | Nom | Type | Propriétaire | Taille | Description public | bigtable | table | postgres | 0 bytes | public | bigtable_0 | table | postgres | 173 MB | public | bigtable_1 | table | postgres | 173 MB | public | bigtable_10 | table | postgres | 173 MB | ... public | bigtable_98 | table | postgres | 173 MB | public | bigtable_99 | table | postgres | 173 MB |
On pourrait se dire que le SELECT de 100 lignes va ramener la même chose que ci-dessus puisqu’il porte sur une partition de même taille :
EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT sum(x)FROM bigtable WHERE id BETWEEN 199000001 AND 199000100 ;
C’est effectivement à peu près le cas, aux erreurs de mesure près :
Finalize Aggregate (cost=27406.34..27406.35 rows=1 width=8) (actual time=379.974..379.974 rows=1 loops=1)
Output: sum(bigtable_39.x)
Buffers: shared read=6097
I/O Timings: read=240.967
-> Gather (cost=27406.02..27406.33 rows=3 width=8) (actual time=379.890..379.968 rows=4 loops=1)
Output: (PARTIAL sum(bigtable_39.x))
Workers Planned: 3
Workers Launched: 3
Buffers: shared read=6097
I/O Timings: read=240.967
-> Partial Aggregate (cost=26406.02..26406.03 rows=1 width=8) (actual time=353.810..353.810 rows=1 loops=4)
Output: PARTIAL sum(bigtable_39.x)
Buffers: shared read=22124
I/O Timings: read=998.870
Worker 0: actual time=345.610..345.610 rows=1 loops=1
Buffers: shared read=4596
I/O Timings: read=252.704
Worker 1: actual time=345.636..345.636 rows=1 loops=1
Buffers: shared read=7394
I/O Timings: read=238.834
Worker 2: actual time=345.647..345.647 rows=1 loops=1
Buffers: shared read=4037
I/O Timings: read=266.366
-> Append (cost=0.00..26405.95 rows=28 width=4) (actual time=336.382..353.804 rows=25 loops=4)
Buffers: shared read=22124
I/O Timings: read=998.870
Worker 0: actual time=345.608..345.608 rows=0 loops=1
Buffers: shared read=4596
I/O Timings: read=252.704
Worker 1: actual time=345.634..345.634 rows=0 loops=1
Buffers: shared read=7394
I/O Timings: read=238.834
Worker 2: actual time=345.646..345.646 rows=0 loops=1
Buffers: shared read=4037
I/O Timings: read=266.366
-> Parallel Seq Scan on public.bigtable_39 (cost=0.00..26405.95 rows=28 width=4) (actual time=336.381..353.801 rows=25 loops=4)
Output: bigtable_39.x
Filter:  AND (bigtable_39.id <= 199000100)
Rows Removed by Filter: 1249975
Buffers: shared read=22124
I/O Timings: read=998.870
Worker 0: actual time=345.607..345.607 rows=0 loops=1
Buffers: shared read=4596
I/O Timings: read=252.704
Worker 1: actual time=345.634..345.634 rows=0 loops=1
Buffers: shared read=7394
I/O Timings: read=238.834
Worker 2: actual time=345.646..345.646 rows=0 loops=1
Buffers: shared read=4037
I/O Timings: read=266.366
Planning time: 10.324 ms
Execution time: 382.220 ms
Notez que le temps de planification a bondi d’un facteur 10. C’est reproductible. En fait, ça l’est même si les partitions sont vides :
Aggregate (cost=34.93..34.94 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=1)
Output: sum(bigtable_39.x)
-> Append (cost=0.00..34.90 rows=11 width=4) (actual time=0.008..0.008 rows=0 loops=1)
-> Seq Scan on public.bigtable_39 (cost=0.00..34.90 rows=11 width=4) (actual time=0.007..0.007 rows=0 loops=1)
Output: bigtable_39.x
Filter:
Planning time: 12.979 ms
Execution time: 0.095 ms
1000 partitions
On tente avec 1000 partitions de 17 Mo. Après tout, je peux avoir besoin de beaucoup moins de lignes que les 5 millions de l’exemple précédent.
SELECT 'CREATE TABLE bigtable_'||p||'
PARTITION OF bigtable
FOR VALUES FROM ('||p*500000+1||') TO ('||(p+1)*500000+1||') ;'
FROM generate_series (0,999) p
\gexec
Schéma | Nom | Type | Propriétaire | Taille | Description public | bigtable | table | postgres | 0 bytes | public | bigtable_0 | table | postgres | 17 MB | public | bigtable_1 | table | postgres | 17 MB | ... public | bigtable_998 | table | postgres | 17 MB | public | bigtable_999 | table | postgres | 17 MB |
Le plan pour 100 lignes se simplifie : un seul worker suffit pour parcourir une petite partition, le temps d’exécution est descendu à 52 ms.
Finalize Aggregate (cost=5633.22..5633.23 rows=1 width=8) (actual time=49.966..49.966 rows=1 loops=1)
Output: sum(bigtable_398.x)
Buffers: shared read=1444
I/O Timings: read=21.991
-> Gather (cost=5633.10..5633.21 rows=1 width=8) (actual time=49.959..49.962 rows=2 loops=1)
Output: (PARTIAL sum(bigtable_398.x))
Workers Planned: 1
Workers Launched: 1
Buffers: shared read=1444
I/O Timings: read=21.991
-> Partial Aggregate (cost=4633.10..4633.11 rows=1 width=8) (actual time=38.506..38.506 rows=1 loops=2)
Output: PARTIAL sum(bigtable_398.x)
Buffers: shared read=2213
I/O Timings: read=38.675
Worker 0: actual time=30.326..30.326 rows=1 loops=1
Buffers: shared read=769
I/O Timings: read=16.685
-> Append (cost=0.00..4633.06 rows=16 width=4) (actual time=15.263..38.495 rows=50 loops=2)
Buffers: shared read=2213
I/O Timings: read=38.675
Worker 0: actual time=30.323..30.323 rows=0 loops=1
Buffers: shared read=769
I/O Timings: read=16.685
-> Parallel Seq Scan on public.bigtable_398 (cost=0.00..4633.06 rows=16 width=4) (actual time=15.262..38.489 rows=50 loops=2)
Output: bigtable_398.x
Filter:  AND (bigtable_398.id <= 199000100)
Rows Removed by Filter: 249950
Buffers: shared read=2213
I/O Timings: read=38.675
Worker 0: actual time=30.322..30.322 rows=0 loops=1
Buffers: shared read=769
I/O Timings: read=16.685
Planning time: 32.769 ms
Execution time: 52.248 ms
On pourrait se dire « les micro-partitions c’est chouette, abusons-en » !
Mais le temps de planification a encore augmenté (et il est similaire sur une table vide). En fait il atteint à présent le même ordre de grandeur que l’exécution elle-même.
10 000 partitions
Pourquoi s’arrêter en si bon chemin ? Créons 10 000 partitions de 1,8 Mo et découvrons un petit phénomène pénible entre la création de la première et celle de la dernière :
CREATE TABLE Temps : 3,512 ms ... CREATE TABLE Temps : 57,067 ms
Le catalogue de PostgreSQL n’est manifestement pas taillé pour manipuler des dizaines de milliers de tables, ce qui est un peu gênant. Certes, on ne perd qu’une poignée de minutes dans un flux d’alimentation normalement beaucoup plus lourd.
L’insertion des 500 millions de lignes n’est pas plus longue que d’habitude, le VACUUM ANALYZE a un temps similaire.
Si je veux récupérer 100 lignes (sur 2 partitions dans cet exemple) :
Aggregate (cost=1544.87..1544.88 rows=1 width=8) (actual time=10.389..10.389 rows=1 loops=1)
Output: sum(bigtable_3999.x)
Buffers: shared hit=222 read=222
I/O Timings: read=4.094
-> Append (cost=0.00..1544.40 rows=188 width=4) (actual time=7.094..10.369 rows=200 loops=1)
Buffers: shared hit=222 read=222
I/O Timings: read=4.094
-> Seq Scan on public.bigtable_3999 (cost=0.00..772.20 rows=43 width=4) (actual time=7.093..7.099 rows=50 loops=1)
Output: bigtable_3999.x
Filter:  AND (bigtable_3999.id <= 200000150)
Rows Removed by Filter: 49950
Buffers: shared read=222
I/O Timings: read=4.094
-> Seq Scan on public.bigtable_4000 (cost=0.00..772.20 rows=145 width=4) (actual time=0.015..3.254 rows=150 loops=1)
Output: bigtable_4000.x
Filter:  AND (bigtable_4000.id <= 200000150)
Rows Removed by Filter: 49850
Buffers: shared hit=222
Planning time: 164.257 ms
Execution time: 10.883 ms
Quelques millisecondes pour parcourir une table ridicule… et 16 fois plus à planifier le bon plan d’exécution ! Je n’ai pas cherché à creuser un éventuel effet de cache sur le catalogue. Et, là encore, la planification avec 10 000 partitions vides est aussi longue.
J’ai testé la récupération du contenu de 10 partitions sur 10 000, vides ou pleines : le temps de planification est similaire.
Exclusion de contraintes
Et si vous voulez tenter de simplifier la tâche à l’optimiseur en lui disant de ne pas chercher à optimiser en fonction des contraintes des tables (SET constraint_exclusion TO off ; au lieu du défaut partition), vous augmenterez encore ce temps de planification car il devra considérer toutes les partitions, et les parcourra toutes, pour un temps global catastrophique :
Planning time: 235.568 ms Execution time: 53080.031 ms
Jointure
Quelque dixièmes de secondes de planification, ce n’est pas énorme comparé au temps d’exécution d’une requête traditionnelle classique, pourrait-on se dire. C’est tout de même énorme par rapport aux partitionnements plus légers vus précédemment. C’est aussi oublier que les tables sont rarement prises isolément. Dans le cas le pire, les tables jointes sont également partitionnées. En testant une autojointure sur la même table :
EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT sum(b.x) FROM bigtable a INNER JOIN bigtable b ON (a.id=b.id) WHERE a.id BETWEEN 300000001 AND 300000001+100 ;
Je vous épargne le plan, qui, faute d’index, consiste à faire un hash join entre la partition bigtable_6000 (a) et les 10 000 partitions (b). Le temps d’exécution de 100 s n’est pas étonnant. Mais il y a aussi ça :
Planning time: 7031.163 ms
…qui est un peu inquiétant.
BRIN
La requête précédente bénéficierait bien d’un index. Je ne veux pas sacrifier encore plusieurs Go : un index BRIN (une nouveauté 9.5), bien compact, fera donc l’affaire. Nos données sont ici triées, c’est souvent le cas dans des tables de fait, mais ce n’est pas le plus important dans notre cas.
Malheureusement PostgreSQL 10 ne gère pas les index couvrant plusieurs partitions, il faudra donc générer 10 000 index à la main :
SELECT 'CREATE INDEX '||relname||'_brin ON ' ||relname ||' USING brin(id);' FROM pgclass where relname like 'bigtable%' \gexec
…et cela fera donc 10 000 objets de plus à gérer.
En pratique la simple somme sur 100 lignes est bien accélérée, mais cela n’a au final aucun intérêt puisqu’on passe 100 fois plus de temps à planifier la requête qu’à l’exécuter complètement :
Aggregate (cost=397.79..397.80 rows=1 width=8) (actual time=3.287..3.287 rows=1 loops=1)
-> Append (cost=0.36..397.56 rows=93 width=4) (actual time=0.108..3.277 rows=101 loops=1)
-> Bitmap Heap Scan on bigtable_8000 (cost=0.36..397.56 rows=93 width=4) (actual time=0.108..3.271 rows=101 loops=1)
Recheck Cond:  AND (id <= 400000101)
Rows Removed by Index Recheck: 28827
Heap Blocks: lossy=128
-> Bitmap Index Scan on bigtable8000brin (cost=0.00..0.33 rows=25000 width=0) (actual time=0.094..0.094 rows=1280 loops=1)
Index Cond:
Planning time: 591.540 ms
Execution time: 3.948 ms
Et en v11 (bêta 2)?
Ce qui précède a été mené avec une 10.3. J’ai eu envie de voir ce que donnait la version en développement, à paraître à l’automne.
La v11 (bêta 2) m’a craché quelques insultes, que je n’avais pas en v10, vite disparues après augmentation du paramètre indiqué :
2018-04-22 16:45:07.721 CEST 2801 ERROR: out of shared memory 2018-04-22 16:45:07.721 CEST 2801 HINT: You might need to increase maxlocksper_transaction.
Le partition pruning, une nouveauté, remplace en pratique la classique exclusion par contrainte.
Pour la recherche simple sur 100 lignes dans 10 000 partitions (sans index) :
Planning Time: 672.276 ms Execution Time: 3.969 ms
Pour la requête avec autojointure (sans index) :
Planning Time: 25156.639 ms Execution Time: 96427.987 ms
Bref, du délire.
Et en v11 (celle qui sortira) ?
Cette explosion des temps de planification pour un grand nombre de partitions a été reproduit et remonté par mon collègue Thomas sur hackers. Je n’ai pas cherché à comprendre le détail des discussions. Une nouvelle fonction n’était pas optimisée parce que cela ne semblait pas important à l’époque. L’important est que le problème a été reconnu, et en bonne partie corrigé il y a quelques jours.
D’après les différents essais peu scientifiques que j’ai faits, les ralentissements restent sensibles pour les très petits temps de planification (c’est sans grande importance), ou ceux déjà énormes (> 1 s). À revoir de manière plus rigoureuse avec des jeux de test plus réalistes et la version finale.
Conclusion
Dixit la doc :
Partitioning using these techniques will work well with up to perhaps a hundred partitions; don’t try to use many thousands of partitions.
Cela reste donc valable à cause de ce temps de planification délirant au-delà de 1 000 partitions. Cela dit, nombre de requêtes décisionnelles de plusieurs minutes se contrefichent de quelques secondes de planification. Il y a donc peut-être quelques cas où on acceptera ce prix pour réduire la taille des partitions. À tester soigneusement.