HashAggregate to slow

Postgres.0 · 25/05/2012 11:06:57

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.

rjuju · 25/05/2012 11:36:32

Bonjour,
si vous voulez qu'on vous aide il faudrait la requête ainsi qu'un explain analyze de cette requête.

Postgres.0 · 25/05/2012 11:58:34

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)

Postgres.0 · 25/05/2012 12:11:24

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)

Postgres.0 · 25/05/2012 12:13:29

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

rjuju · 25/05/2012 12:38:59

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 ?

Postgres.0 · 25/05/2012 14:14:44

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)

Postgres.0 · 25/05/2012 14:19:57

rjuju a écrit :

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)

rjuju · 25/05/2012 14:47:18

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 ?

Postgres.0 · 25/05/2012 15:09:26

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.

rjuju · 25/05/2012 15:29:26

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 ...)

SQLpro · 25/05/2012 16:24:42

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 +

arthurr · 25/05/2012 16:56:43

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 ?

gleu · 26/05/2012 19:13:18

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.

SQLpro · 27/05/2012 17:26:47

arthurr a écrit :

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)

gleu · 29/05/2012 08:55:45

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.

Postgres.0 · 29/05/2012 11:04:50

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.

Postgres.0 · 29/05/2012 11:32:50

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)

dverite · 29/05/2012 14:04:35

Postgres.0 a écrit :

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

arthurr · 29/05/2012 14:15:35

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= 5

Postgres.0 · 29/05/2012 14:57:50

arthurr a écrit :

Petite question :
Ces clauses WHERE peuvent changer ?

la reponse est oui!

Postgres.0 · 29/05/2012 15:01:01

dverite

peus-tu expliquer un oeu plus le CLUSTER,

et quelles sont les commandes pour faire un REINDEX et un CLUSTER.

dverite · 29/05/2012 15:21:33

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

Postgres.0 · 29/05/2012 15:34:51

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)

SQLpro · 29/05/2012 21:00:06

gleu a écrit :

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 +

#1 25/05/2012 11:06:57

HashAggregate to slow

#2 25/05/2012 11:36:32

Re : HashAggregate to slow

#3 25/05/2012 11:58:34

Re : HashAggregate to slow

#4 25/05/2012 12:11:24

Re : HashAggregate to slow

#5 25/05/2012 12:13:29

Re : HashAggregate to slow

#6 25/05/2012 12:38:59

Re : HashAggregate to slow

#7 25/05/2012 14:14:44

Re : HashAggregate to slow

#8 25/05/2012 14:19:57

Re : HashAggregate to slow

#9 25/05/2012 14:47:18

Re : HashAggregate to slow

#10 25/05/2012 15:09:26

Re : HashAggregate to slow

#11 25/05/2012 15:29:26

Re : HashAggregate to slow

#12 25/05/2012 16:24:42

Re : HashAggregate to slow

#13 25/05/2012 16:56:43

Re : HashAggregate to slow

#14 26/05/2012 19:13:18

Re : HashAggregate to slow

#15 27/05/2012 17:26:47

Re : HashAggregate to slow

#16 29/05/2012 08:55:45

Re : HashAggregate to slow

#17 29/05/2012 11:04:50

Re : HashAggregate to slow

#18 29/05/2012 11:32:50

Re : HashAggregate to slow

#19 29/05/2012 14:04:35

Re : HashAggregate to slow

#20 29/05/2012 14:15:35

Re : HashAggregate to slow

#21 29/05/2012 14:57:50

Re : HashAggregate to slow

#22 29/05/2012 15:01:01

Re : HashAggregate to slow

#23 29/05/2012 15:21:33

Re : HashAggregate to slow

#24 29/05/2012 15:34:51

Re : HashAggregate to slow

#25 29/05/2012 21:00:06

Re : HashAggregate to slow

Pied de page des forums

À propos

Nous contacter

Groupes Locaux

Réseaux Sociaux