Vous n'êtes pas identifié(e).
Bonjour,
Je travaille avec PostgreSQL 8.4 et je tente de mettre au point une table partitionnée par héritage. La table mère appelée 'spdpdata' comporte une colonne 'seconds'. Les 168 tables filles ont chacune une contrainte vérifiant que la valeur 'seconds' divisée par 3600 modulo 168 est égale à un entier de l'intervalle [0, 167]
CREATE TABLE spdpdata_0( CHECK (((seconds / 3600)%168) = 0)) INHERITS (spdpdata);
CREATE TABLE spdpdata_1( CHECK (((seconds / 3600)%168) = 1)) INHERITS (spdpdata);
CREATE TABLE spdpdata_2( CHECK (((seconds / 3600)%168) = 2)) INHERITS (spdpdata);
CREATE TABLE spdpdata_3( CHECK (((seconds / 3600)%168) = 3)) INHERITS (spdpdata);
...
CREATE TABLE spdpdata_167( CHECK (((seconds / 3600)%168) = 167)) INHERITS (spdpdata);
J'ai placé des index sur les tables
CREATE INDEX fki_spdpdata_seconds_0 ON spdpdata_0 USING btree (seconds);
CREATE INDEX fki_spdpdata_seconds_1 ON spdpdata_1 USING btree (seconds);
...
CREATE INDEX fki_spdpdata_seconds_167 ON spdpdata_167 USING btree (seconds);
J'ai placé un trigger pour l'insertion et j'ai aussi mis le paramètre constraint_exclusive à 'on'.
Pourtant, je ne constate aucune amélioration de mes requêtes ne concernant qu'une table fille et lorsque je les analyses (EXPLAIN ou EXPLAIN ANALYZE), je constate que toutes les tables sont parcourues. Exemple pour une requête de type
select * from spdpdata where seconds=1350293068
Result (cost=0.00..2191.34 rows=21883 width=85)
-> Append (cost=0.00..2191.34 rows=21883 width=85)
-> Seq Scan on spdpdata (cost=0.00..16.45 rows=2 width=158)
Filter: ((seconds >= 1350446401) AND (seconds < 1350457198))
-> Bitmap Heap Scan on spdpdata_0 spdpdata (cost=4.27..9.62 rows=2 width=158)
Recheck Cond: ((seconds >= 1350446401) AND (seconds < 1350457198))
-> Bitmap Index Scan on fki_spdpdata_seconds_0 (cost=0.00..4.27 rows=2 width=0)
Index Cond: ((seconds >= 1350446401) AND (seconds < 1350457198))
-> Bitmap Heap Scan on spdpdata_1 spdpdata (cost=4.27..9.62 rows=2 width=158)
Recheck Cond: ((seconds >= 1350446401) AND (seconds < 1350457198))
-> Bitmap Index Scan on fki_spdpdata_seconds_1 (cost=0.00..4.27 rows=2 width=0)
Index Cond: ((seconds >= 1350446401) AND (seconds < 1350457198))
-> Bitmap Heap Scan on spdpdata_2 spdpdata (cost=4.27..9.62 rows=2 width=158)
Recheck Cond: ((seconds >= 1350446401) AND (seconds < 1350457198))
-> Bitmap Index Scan on fki_spdpdata_seconds_2 (cost=0.00..4.27 rows=2 width=0)
Index Cond: ((seconds >= 1350446401) AND (seconds < 1350457198))
-> Bitmap Heap Scan on spdpdata_3 spdpdata (cost=4.27..9.62 rows=2 width=158)
....
J'ai vérifié que les requêtes suivantes renvoient toujours des entiers quelle que soit la valeur de seconds: select ((seconds / 3600) % 168);
Finalement, en adoptant un partitionnement plus classique par plage de valeur (exemple: seconds < val1, val1 <= seconds < val2, ...), l'explain plan de la requête montre bien qu'il n'analyse qu'une seule table. Mais cet héritage plus simple n'est pas adapté fonctionnellement à mon cas.
J'ai donc l'impression que PostgreSQL ne détecte pas que mes contraintes sont exclusives et qu'il est obligé de passer malgré tout dans toutes les tables.
Avez-vous des idées ?
Merci,
Mathieu
P.S.: je vais tenter une installation d'une version plus récente pour voir si cela résoud le problème
Hors ligne
Bonjour,
le problème vient de votre filtre where : ((seconds >= 1350446401) AND (seconds < 1350457198))
Le planificateur n'est pas capable de mettre en rapport ce filtre avec les contraintes de vérification (((seconds / 3600)%168) = x)) et parcourt donc toutes les tables. Il y a encore un certain nombre de limitations avec le partitionnement sous postgres, dont ce type d'utilisation. Voir la doc: http://docs.postgresqlfr.org/9.1/ddl-partitioning.html:
"les contraintes de partitionnement doivent rester simples. Dans le cas contraire, le planificateur peut rencontrer des difficultés à déterminer les partitions qu'il n'est pas nécessaire de parcourir. Des conditions simples d'égalité pour le partitionnement de liste ou des tests d'échelle simples lors de partitionnement d'échelle sont recommandées, comme cela est illustré dans les exemples précédents. Une bonne règle consiste à s'assurer que les comparaisons entre colonnes de partitionnement et constantes utilisées par les contraintes de partitionnement se fassent uniquement à l'aide d'opérateurs utilisables par les index B-tree."
Julien.
https://rjuju.github.io/
Hors ligne
Merci,
Il faut donc un filtre identique à la contrainte pour obtenir une optimisation. Je vais essayer de voir s'il est possible de modifier ma requête de sélection afin d'employer '(seconds/3600)%168'.
Hors ligne
Pas forcément identique, mais suffisamment simple pour que le planificateur puisse en déduire les bonnes partitions.
Guillaume.
Hors ligne
