J'ai plusieurs grands tableaux de séries chronologiques contenant beaucoup de valeurs nulles (chaque tableau peut avoir jusqu'à 300 colonnes), par exemple:
Tableau de séries chronologiques
col_name | first_time | last_time ---------+---------------------+-------------------- a | 2016-05-15 13:35:36 | 2016-05-15 13:37:36 b | 2016-05-15 13:35:36 | 2016-05-15 13:37:36 c | 2016-05-15 13:50:56 | 2016-05-15 13:45:32 d | 2016-05-15 13:47:56 | 2016-05-15 13:35:36
Je souhaite optimiser les requêtes pour rechercher la première et la dernière valeur de chaque colonne, c'est-à-dire:
select MIN(time), MAX(time) from TS where a is not null
(Ces requêtes peuvent être exécutées pour plusieurs minutes)
Je prévois de créer une table de métadonnées contenant les noms de colonnes et pointant vers le premier et le dernier horodatage:
Table de métadonnées
time | a | b | c | d --------------------+---------+----------+---------+--------- 2016-05-15 00:08:22 | | | | 2016-05-15 13:50:56 | | | 26.8301 | 2016-05-15 01:41:58 | | | | 2016-05-15 00:01:37 | | | | 2016-05-15 01:45:18 | | | | 2016-05-15 13:45:32 | | | 26.9688 | 2016-05-15 00:01:48 | | | | 2016-05-15 13:47:56 | | | | 27.1269 2016-05-15 00:01:22 | | | | 2016-05-15 13:35:36 | 26.7441 | 29.8398 | | 26.9981 2016-05-15 00:08:53 | | | | 2016-05-15 00:08:30 | | | | 2016-05-15 13:14:59 | | | | 2016-05-15 13:33:36 | 27.4277 | 29.7695 | | 2016-05-15 13:36:36 | 27.4688 | 29.6836 | | 2016-05-15 13:37:36 | 27.1016 | 29.8516 | |
De cette façon, aucune recherche Null ne se produira pendant la requête et j'accéderai simplement à la valeur dans le premier et le dernier horodatage.
Mais je veux éviter la nécessité de mettre à jour la table de métadonnées à chaque modification des données de séries chronologiques. Au lieu de cela, je souhaite créer une fonction de déclenchement générique vers laquelle mettre à jour les colonnes first_time et last_time de la table de métadonnées sur chaque table d'insertion, de mise à jour ou de suppression dans la série temporelle. La fonction de déclenchement doit comparer les horodatages existants dans la table de métadonnées aux lignes insérées / supprimées.
Une idée s'il est possible de créer une fonction de déclenchement générique qui ne contiendra pas les noms de colonne exacts de la table de séries chronologiques?
Merci
3 Réponses :
Vous pouvez annuler le pivot avec union all . Je suggérerais d'utiliser une vue au lieu d'utiliser un déclencheur. Cela présente l'avantage d'être beaucoup plus flexible, plus simple à maintenir et de ne pas ralentir vos instructions DML:
ts(a, time) ts(b, time) ts(c, time) ts(d, time)
Pour les performances, vous voulez les index suivants:
create view metadata_view as select 'a' col_name, min(time) first_time, max(time) last_time from ts where a is not null union all select 'b', min(time), max(time) from ts where b is not null union all select 'c', min(time), max(time) from ts where c is not null union all select 'd', min(time), max(time) from ts where d is not null
Que a , b , c , d dans la liste des colonnes projetées devrait mieux être dans ' s, si j'ai bien compris l'intention du PO. En l'état, cette requête devrait générer une erreur car ils ne sont pas dans un GROUP BY .
J'essaie de comprendre l'idée - une requête comme sélectionnez 'b', min (temps), max (temps) à partir de ts où b n'est pas nul peut fonctionner pendant plusieurs minutes. Pour autant que je sache, avoir une vue standard en plus de ces requêtes ne créera aucune optimisation lorsque j'essaie de trouver rapidement la première et la dernière valeur. Vous proposez donc de créer une vue matérialisée?
@Miro: ok, vous n'avez pas initialement mentionné que ces requêtes étaient lentes. Avant d'opter pour une vue matérialisée, veuillez vous assurer que vous disposez des index que je viens d'ajouter à ma réponse.
@Miro Et: avant de vous soucier des performances, peut-être reconsidérer la conception de votre base de données?
Et si j'ai 300 colonnes? Êtes-vous sûr que les performances d'insertion seront toujours raisonnables avec 300 index? Je travaille avec des données Time-Series, donc je ne sais pas comment reconsidérer la conception de la base de données ...
@Miro: avez-vous vraiment 300 colonnes? Quoi qu'il en soit, je pense qu'il n'y a pas de réponse générique à votre question, il faudrait aller la tester ...
En ce qui concerne la conception de bases de données, je suppose que @wildplasser voulait dire: diviser votre table en différentes tables, une fois par colonne a, b, ... existante afin que vous puissiez éviter ces lacunes dans votre ensemble de données.
Malheureusement, j'ai ces chiffres (désolé de ne pas l'avoir mentionné au départ). Les mettre dans différentes tables n'est pas non plus une option, car les données sont interrogées (et agrégées) ensemble et avoir beaucoup de jointures n'est pas une bonne idée ...
Il peut être préférable de le faire en utilisant plusieurs colonnes:
select v.*
from (select min(time) filter (where a is not null) as a_min,
max(time) filter (where a is not null) as a_max,
min(time) filter (where b is not null) as b_min,
max(time) filter (where b is not null) as b_max,
min(time) filter (where c is not null) as c_min,
max(time) filter (where c is not null) as c_max,
min(time) filter (where d is not null) as d_min,
max(time) filter (where d is not null) as d_max,
from metadata
) x cross join lateral
(values ('a', min_a, max_a),
('b', min_b, max_b),
('c', min_c, max_c),
('d', min_d, max_d)
) v(which, min_val, max_val);
Vous pouvez ensuite annuler le pivot après cette étape:
select min(time) filter (where a is not null) as a_min,
max(time) filter (where a is not null) as a_max,
min(time) filter (where b is not null) as b_min,
max(time) filter (where b is not null) as b_max,
min(time) filter (where c is not null) as c_min,
max(time) filter (where c is not null) as c_max,
min(time) filter (where d is not null) as d_min,
max(time) filter (where d is not null) as d_max,
from t;
Au lieu de en créant un déclencheur, j'opterais pour des index, qui peuvent être utilisés avec l'approche de GMB.
Peut-être même créer des indices partiels?
Qu'entendez-vous par indices partiels dans ce cas? Merci
@Miro. . . Je pense que les index sur la colonne time où chacune des quatre autres colonnes est NULL (quatre index séparés).
La création d'une requête dynamique dans une fonction de déclenchement est possible, voir cet exemple de comment-implémenter-dynamic-sql-in-postgresql-10
CREATE OR REPLACE FUNCTION car_portal_app.get_account (predicate TEXT) RETURNS SETOF car_portal_app.account AS $$ BEGIN RETURN QUERY EXECUTE 'SELECT * FROM car_portal_app.account WHERE ' || predicate; END; $$ LANGUAGE plpgsql;
Le format est également utile pour créer la chaîne de requête.
Vous pouvez implémentez un déclencheur qui se déclenche une fois par instruction (pas pour chaque ligne): la documentation postgres a un excellent exemple: regardez "Exemple 43.7. Audit avec des tables de transition" dans 43.10. Fonctions de déclenchement
Cela fonctionnera très bien pour les insertions.
Mais lorsque le min / max d'une colonne est mis à jour / supprimé, vous devez vérifier à nouveau toutes les lignes pour trouver le nouveau min / max. Et si cela prend plusieurs minutes, cela ne devrait pas être fait dans le déclencheur.
Désolé si ce n'était pas clair - la question était de savoir comment écrire une fonction de déclenchement qui met à jour les horodatages min / max dans une table séparée, en fonction de l'horodatage disponible dans les données à insérer / lignes disponibles à supprimer. (et pas comment écrire une fonction de déclenchement générique).
@Miro: copié directement à partir de votre question: Toute idée s'il est possible de créer une fonction de déclenchement générique .. : D
peut-être ai-je mal compris votre réponse ou vous l'avez fait ..... Je ne vois aucune relation entre la fonction que vous avez publiée et la question posée. Je ne sais pas pourquoi le déclencheur fonctionnera plusieurs minutes car il est censé parcourir uniquement les données insérées. Pourtant, "Transition Tables" est une approche intéressante .....
@Miro ma réponse est uniquement destinée à vous guider dans la bonne direction, pas à fournir la solution exacte. En utilisant la fonction format , vous pouvez créer une requête dynamique qui peut tout faire. Vous pouvez obtenir les noms de colonne de vos tables à partir du information_schema . La partie "plusieurs minutes" se réfère uniquement à la mise à jour / suppression. Pour les inserts, tout ira bien.
Essayez plutôt de mettre des index sur
(a asc, time asc)et(a asc, time desc)(et de même pourb, < code> c etd). Si vous le souhaitez, vous pouvez opter pour une vue "métadonnées". Mieux vaut éviter de créer de la redondance.Faites-vous référence à une vue "matérialisée" qui sera périodiquement rafraîchie? Je ne vois aucune optimisation dans une vue standard ...
Non. Juste une vue "normale". L'optimisation sont les index.
Je ne l'ai pas mentionné au départ, mais je peux avoir jusqu'à 300 colonnes dans le tableau TS. Je soupçonne que le fait d'avoir 300 index affectera les performances d'insertion plus que les fonctions de déclenchement ...
Je m'attendrais également à ce que les index soient plus lents puis se déclenchent car le déclencheur n'aura besoin de mettre à jour une ligne que lorsqu'une nouvelle valeur min ou max est insérée. Alors que les index devront être mis à jour pour chaque valeur insérée. BTW, vous pourriez être tenté d'écrire du code dynamique dans votre déclencheur qui boucle sur toutes les colonnes, mais selon mon expérience, il est préférable d'écrire un script qui génère le déclencheur avec un code spécifique pour chaque colonne.
@Eelke, c'est exactement ce que je pensais et planifiais. Pourtant, je pensais à «écrire un code dynamique». Que veux-tu dire exactement? Créer un déclencheur pour chaque colonne de données et faire la distinction entre les appels de déclencheur par "QUAND" (ce qui signifie que si j'insère des valeurs
aetb- 2 déclencheurs différents seront appelés)? TnxPersonne ne déclenche, mais écrivez des conditions pour chaque colonne qui testent le changement de valeur, puis mettent à jour les métadonnées si nécessaire. L'avantage est que le code statique est analysé une fois lors de la création de la fonction de déclenchement tandis que le code dynamique doit être analysé à chaque appel.