Vous n'êtes pas identifié(e).
Bonjour,
comment peux-ton améliorer le comportement d'une requete qui utilise dans son plan d'exécution un HashAggregate trop lent.
Je suis sur la 9.1.
Hors ligne
Bonjour,
si vous voulez qu'on vous aide il faudrait la requête ainsi qu'un explain analyze de cette requête.
Julien.
https://rjuju.github.io/
En ligne
SELECT 'AUD':: character(3),839:: bigint, null::bigint , NULL :: character varying, NULL :: bigint,
p , NULL :: integer,
(SUM(nb_of_sale) + SUM(nb_of_co) + SUM(nb_of_sale_n) + SUM(nb_of_c) + SUM(nb_of_p)) AS nbr_s,
(SUM(am_s) + SUM(am_co) + SUM(am_s_n) + SUM(am_c) + SUM(am_p)) AS s,
SUM(am_t) AS t_a,
SUM(nb_of_pw) AS n_pw,
SUM(am_pw) AS pw_am,
SUM(nbr_of_c) AS nb_c,
SUM(am_c) AS c_am,
SUM(nb_of_r) AS nb_r,
SUM(am_r) AS am_r,
SUM(nb_of_c_s) AS nb__o
FROM table aft
WHERE ag_date BETWEEN '2012-02-29 23:00:00'
AND date_trunc('day', '2012-02-29 23:59:59'::timestamp without time zone) + interval '23:59:59'
AND id_org = 839
AND id_ref= 5
AND id = ANY( '{23174,23159,23173,23158,23181,23170,23180,23169,23184,23178,
23185,23165,23161,23166,23162,23182,23171,23183,23172,23186,23179,23187,
23167,23163,23168,23160,23176,23164,23175,23177}' ) GROUP BY aft.p.Dernière modification par Postgres.0 (25/05/2012 12:05:11)
Hors ligne
HashAggregate (cost=8.90..8.93 rows=1 width=77) (actual time=4.802..4.810 rows=3 loops=1)
Output: 'AUD'::character(3), 839::bigint, NULL::bigint, NULL::character varying, NULL::bigint, pos, NULL::integer, ........
Buffers: shared hit=4474
-> Index Scan using par_ag_idx10 on aft (cost=0.00..8.84 rows=1 width=77) (actual time=0.889..4.755 rows=4 loops=1)
Output: ........... Index Cond: ((aft.ag_date >= '2012-02-29 23:00:00'::timestamp without time zone)
AND (aft.ag_date <= '2012-02-29 23:59:59'::timestamp without time zone))
Filter: ((aft.id_org = 839)
AND (aft.id_ref = 5) AND (aft.id = ANY ('{23174,23159,23173,23158,23181,23170,23180,23169,23184,23178,23185,23165,
23161,23166,23162,23182,23171,23183,23172,23186,23179,23187,23167,23163,
23168,23160,23176,23164,23175,23177}'::bigint[])))
Buffers: shared hit=4474
Total runtime: 4.980 ms.Dernière modification par Postgres.0 (25/05/2012 12:12:10)
Hors ligne
Voilà, c'est bien le Hashaggregate qui me consomme beacoups de temps.
Cette requetes met 10 seconde chez moi, j'aimerai vraiment pouvoir l'améliorer.
Merci de votre aide
Hors ligne
En fait le HashAggregate ne prend presque pas de temps sur cet explain, il a un cost de 0.03 et un temps plus que négligeable.
C'est le IndexScan qui prend presque tout le temps d'exécution.
L'explain que vous montrez ici est très rapide, quand vous dites que chez vous il prend 10 secondes, c'est sur un serveur différent ?
Julien.
https://rjuju.github.io/
En ligne
Non, c'est le meme serveur.
Sauf que j'ai fait une bourde: au fait j'ai lancé l'explain analyze après avoir executer ma requete, je mets ici le bon plan d'execution.
HashAggregate (cost=8.90..8.93 rows=1 width=77) (actual time=9869.904..9869.912 rows=3 loops=1)
Output: 'AUD'::character(3), 839::bigint, NULL::bigint, NULL::character varying,
NULL::bigint, pos, NULL::integer, ........
Buffers: shared hit=4 read=4470
-> Index Scan using par_ag_idx10 on aft (cost=0.00..8.84 rows=1 width=77)
(actual time=1775.949..9869.778 rows=4 loops=1)
Output: ...........
Index Cond: ((aft.ag_date >= '2012-02-29 23:00:00'::timestamp without time zone)
AND (aft.ag_date <= '2012-02-29 23:59:59'::timestamp without time zone))
Filter: ((aft.id_org = 839)
AND (aft.id_ref = 5) AND (aft.id = ANY ('{23174,23159,23173,23158,23181,
23170,23180,23169,23184,23178,23185,23165,
23161,23166,23162,23182,23171,23183,23172,23186,23179,23187,23167,23163,
23168,23160,23176,23164,23175,23177}'::bigint[])))
Buffers: shared hit=4 read=4470"
Total runtime: 9870.068 ms..
Dernière modification par Postgres.0 (25/05/2012 14:15:51)
Hors ligne
En fait le HashAggregate ne prend presque pas de temps sur cet explain, il a un cost de 0.03 et un temps plus que négligeable.
C'est le IndexScan qui prend presque tout le temps d'exécution.
L'explain que vous montrez ici est très rapide, quand vous dites que chez vous il prend 10 secondes, c'est sur un serveur différent ?
Qu'exst ce que je peux faire pour améliorer le comportement du index scan?
Merci
Dernière modification par Postgres.0 (25/05/2012 14:28:10)
Hors ligne
Dans un explain, le coût est exprimé par 2 valeurs : celui pour récupérer la première ligne et celui pour récupérer la dernière ligne.
Dans votre cas
HashAggregate (cost=8.90..8.93 rows=1 width=77) (actual time=9869.904..9869.912
le cout initial de la 1ere ligne est de 8.90, et le cout final 8.93. Le cout initial est hérité du IndexScan (8.84) qui s'exécute entièrement avant le HashAggregate.
Le changement du temps d'exécution est du à la présence ou non des bloc en cache. Si cela influe autant c'est probablement du à des disques trop lent, même si le delta semble un peu étonnant. Quelle est la configuration de votre serveur ?
Julien.
https://rjuju.github.io/
En ligne
et que veux dire donc
(actual time=9869.904..9869.912 ), quel est le lien avec le cout que vous avez expliquer plus haut.
Comment savez vous que
"Le cout initial est hérité du IndexScan (8.84) qui s'exécute entièrement avant le HashAggregate"
Pensez vous qu'il est mieux d'utiliser les fonctions analytiques.
Pour la configuration du serveur, je ne sais comment la trouver.
Hors ligne
Le actuel time est le temps vraiment passé a effectuer un noeud, car selon les configurations, la charge du serveur a un instant donné le temps varie alors que le nombre et la nature des opérations ne change pas.
Avez-vous accès au serveur hébergeant postgresql ? Il faudrait sinon demander à un administrateur quels sont les disques et leurs configurations (sata, sas, raid ...)
Julien.
https://rjuju.github.io/
En ligne
C'est votre ANY = qui pose problème.
Déjà récrivez le comme suit et testez avec l'index indiqué :
AND id BETWEEN 23158 ANDE 23187 -- strictement équivalent à votre requête.
Ajoutez l'index suivant s'il n'y est pas :
test 1 :
CREATE INDEX X1 ON aft (id_org, id_ref, ag_date, id)
sinon mieux : test 2
CREATE INDEX X2 ON aft (id_org, id_ref, ag_date, id, p,am_c, am_co, am_p, am_pw, am_r, am_s, am_s_n, am_t, nb_of_c, nb_of_c_s, nb_of_co, nb_of_p, nb_of_pw, nb_of_r, nb_of_sale, nb_of_sale_n, nbr_of_c)
Au final remplacez le contenu de votre ANY par une jointure avec une table en dur indexée que vous alimenterez suivant vos besoins...
A +
Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES, Expert langage SQL
Le site sur les SGBD relationnel et langage SQL : http://sqlpro.developpez.com/
Modélisation de données, conseil, expertise, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA, ISEN Toulon, CESI Aix en Provence * * * * *
Hors ligne
CREATE INDEX X2 ON aft (id_org, id_ref, ag_date, id, p,am_c, am_co, am_p, am_pw, am_r, am_s, am_s_n, am_t, nb_of_c, nb_of_c_s, nb_of_co, nb_of_p, nb_of_pw, nb_of_r, nb_of_sale, nb_of_sale_n, nbr_of_c)
A +
Overkill, non ?
Hors ligne
Très clairement overkill. Le premier index proposé aussi à mon avis. Un simple index sur la colonne id (donc CREATE INDEX index_name ON aft(id)) devrait suffire après réécriture de la requête pour utiliser un IN et non un ANY (qui a plus de chance d'être juste). Si le BETWEEN est possible, c'est encore mieux.
Guillaume.
Hors ligne
SQLpro a écrit :CREATE INDEX X2 ON aft (id_org, id_ref, ag_date, id, p,am_c, am_co, am_p, am_pw, am_r, am_s, am_s_n, am_t, nb_of_c, nb_of_c_s, nb_of_co, nb_of_p, nb_of_pw, nb_of_r, nb_of_sale, nb_of_sale_n, nbr_of_c)
A +Overkill, non ?
Le problème est que :
1) PostGreSQL ne sait pas faire de scan d'index (sauf la récente version 9.2, mais j'ai pas encore testé les limites...)
2) PostGreSQL ne propose pas de clause INCLUDE pour les index afin de les rendre couvrant, comme le fais MS SQL Server, ce qui éviterais de lire la table
3) PostGreSQL ne propose pas non plus de vue indexées afin de résoudre ce genre de cas de manière très performante
A +
Dernière modification par SQLpro (27/05/2012 17:26:57)
Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES, Expert langage SQL
Le site sur les SGBD relationnel et langage SQL : http://sqlpro.developpez.com/
Modélisation de données, conseil, expertise, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA, ISEN Toulon, CESI Aix en Provence * * * * *
Hors ligne
Le problème est que...
Proposer des solutions non adaptées à PostgreSQL sur un forum où l'utilisateur cherche une solution pour son problème sur PostgreSQL est tout simplement hors-sujet non ? je peux comprendre que ce soit ajouté dans la discussion comme une fonctionnalité non couverte mais il faut le dire directement et pas laisser Postgres.0 tester des trucs donc on sait pertinnement que cela ne marchera pas sur PostgreSQL.
Guillaume.
Hors ligne
Bonjour,
le BETWEEN n'est pas possible, je suis obligé d'enumerer tous les id.
J'ai un index sur le id, sur le id_org, j'ai aussi un index sur (id_org, id_ref, ag_date, id).
La solution qui consiste de créer un insex sur aft (id_org, id_ref, ag_date, id, p,am_c, am_co, am_p, am_pw, am_r, am_s, am_s_n, am_t, nb_of_c, nb_of_c_s, nb_of_co, nb_of_p, nb_of_pw, nb_of_r, nb_of_sale, nb_of_sale_n, nbr_of_c) a été écarter depuis longtemps.
J'avais fait des tests pour comparer les performances entre IN et ANY et je n'ai pas vu de difference.
Merci beaucoup.
Hors ligne
Bonjour,
le BETWEEN n'est pas possible, je suis obligé d'enumerer tous les id.
J'ai un index sur le id, sur le id_org, j'ai aussi un index sur (id_org, id_ref, ag_date, id).
La solution qui consiste à créer un index sur aft (id_org, id_ref, ag_date, id, p,am_c, am_co, am_p, am_pw, am_r, am_s, am_s_n, am_t, nb_of_c, nb_of_c_s, nb_of_co, nb_of_p, nb_of_pw, nb_of_r, nb_of_sale, nb_of_sale_n, nbr_of_c) a été écartée depuis longtemps.
J'avais fait des tests pour comparer les performances entre IN et ANY et je n'ai pas vu de difference.
Merci beaucoup
Dernière modification par Postgres.0 (29/05/2012 11:33:16)
Hors ligne
Qu'exst ce que je peux faire pour améliorer le comportement du index scan?
D'après les 2 explain analyze, le problème est lié à la lenteur de lecture de l'index quand les données viennent du disque (9800ms vs 5ms)
Il y a diverses hypothèses et solutions correspondantes, qui ne sont pas mutuellement exclusives:
1) l'index est trop désorganisé: faire un REINDEX sur par_ag_idx10
2) la table est trop désorganisée: faire un CLUSTER de la table sur par_ag_idx10 pour la réécrire dans le même ordre que l'index.
3) il n'y a pas assez de mémoire pour que suffisamment de données soient en cache: augmenter la RAM et shared_buffers
4) le ou les disques sont trop faibles par rapport aux quantités de données à brasser: booster le sous-système disque
@DanielVerite
http://blog-postgresql.verite.pro/
Hors ligne
Petite question :
Ces clauses WHERE peuvent changer ?
id_org = 839
id_ref= 5
id = ANY( '{ ...} ')Si la réponse est "non", il est possible de créer un index partiel (exemple avec les 2 premiers critères ):
create index on LA_TABLE (id_org,id_ref,ag_date) WHERE id_org = 839 AND id_ref= 5Hors ligne
Petite question :
Ces clauses WHERE peuvent changer ?
la reponse est oui!
Hors ligne
dverite
peus-tu expliquer un oeu plus le CLUSTER,
et quelles sont les commandes pour faire un REINDEX et un CLUSTER.
Hors ligne
Le sujet est couvert par la doc:
REINDEX: http://docs.postgresql.fr/9.1/sql-reindex.html
CLUSTER: http://docs.postgresql.fr/9.1/sql-cluster.html
@DanielVerite
http://blog-postgresql.verite.pro/
Hors ligne
Le CLUSTER ne marche pas chez moi, pourtant je suis ssur la 9.1.
Peut-être que c'est par ce que ma table est une partition!
Dernière modification par Postgres.0 (29/05/2012 15:36:15)
Hors ligne
Le problème est que...
Proposer des solutions non adaptées à PostgreSQL sur un forum où l'utilisateur cherche une solution pour son problème sur PostgreSQL est tout simplement hors-sujet non ? je peux comprendre que ce soit ajouté dans la discussion comme une fonctionnalité non couverte mais il faut le dire directement et pas laisser Postgres.0 tester des trucs donc on sait pertinnement que cela ne marchera pas sur PostgreSQL.
Pas du tout hors sujet.. Comme PG ne sait pas faire des index INCLUDE, une façon de contourner est de créer un index contenant TOUTES les colonnes utilisées par la requête... Ceci évite la double lecture index + table. C'est un vieux truc utilisé avant que les index INCLUDE arrivent.
Avez vous testé au moins avant de tirer à boulet rouge sur cette solution ???
A +
Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES, Expert langage SQL
Le site sur les SGBD relationnel et langage SQL : http://sqlpro.developpez.com/
Modélisation de données, conseil, expertise, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA, ISEN Toulon, CESI Aix en Provence * * * * *
Hors ligne