Vous n'êtes pas identifié(e).
Pages : 1
bonjour,
Ceci est mon premier post sur ce forum.
j'espère ne pas me tromper de sujet.
j'ai une requête assez imposante qui initialise une vue. Cette requête est assez longue.
je fais un explain et j'obtient une série de seq scan qui je pense sont a l'origine des lenteurs.
le pb c'est que mes ajouts d'index n'ont aucune influence ni sur la performance de la requete ni sur le plan d'execution.
Je joins la requete son plan ainsi que les deux indexs que j'ai essayé de mettre en place.
si qq1 peut m'aider j'avou que ca m'enleverrais une epine du pied.
Merci d'avance
voici mes questions :
- il y a t'il une manip pour que les index soient pris en compte (un vacuum analyse de la table n'a aucun effet)
- Suis-je un debil et je ne sais pas creer des index efficace
Pour info voici la requête
SELECT (year*100)+week AS id, year||':'||week AS name, year, week,
SUM(i.quantity) AS quantity,
SUM(i.untaxed_total) AS turnover,
CAST(SUM(t.list_price*i.quantity) AS NUMERIC(12,2)) AS catalog,
CAST(SUM(t.standard_price*i.quantity*uo.factor/ui.factor) AS NUMERIC(12,2)) AS value
FROM ( SELECT DATE_PART('WEEK',i.create_date) as week,
DATE_PART('YEAR',i.create_date) AS year,
i.product,
SUM(i.quantity) AS quantity,
SUM(i.untaxed_total) AS untaxed_total
FROM fu_cart_item i
JOIN fu_cart c ON i.cart=c.id
WHERE DATE_TRUNC('WEEK',i.create_date)=DATE_TRUNC('WEEK',NOW()-INTERVAL '1 WEEK') AND c.state!='cancelled' AND i.product IS NOT NULL
GROUP BY DATE_PART('WEEK',i.create_date), DATE_PART('YEAR',i.create_date),i.product ) AS i
JOIN product_product p ON i.product=p.id
JOIN product_template t ON t.id=p.product_tmpl_id
JOIN product_uom ui ON ui.id=t.uom_po_id
JOIN product_uom uo ON uo.id=t.uos_id
GROUP BY year, week
et le explain :
HashAggregate (cost=220714.16..220791.44 rows=1189 width=110)
-> Hash Join (cost=220001.12..220535.90 rows=11884 width=110)
Hash Cond: (i.product = p.id)
-> HashAggregate (cost=219700.79..219938.47 rows=11884 width=27)
-> Hash Join (cost=17748.06..219404.98 rows=23665 width=27)
Hash Cond: (i.cart = c.id)
-> Seq Scan on fu_cart_item i (cost=0.00..201050.18 rows=25176 width=31)
Filter: ((product IS NOT NULL) AND (date_trunc('WEEK'::text, create_date) = date_trunc('WEEK'::text, (now() - '7 days'::interval))))
-> Hash (cost=12632.30..12632.30 rows=409261 width=4)
-> Seq Scan on fu_cart c (cost=0.00..12632.30 rows=409261 width=4)
Filter: ((state)::text <> 'cancelled'::text)
-> Hash (cost=276.22..276.22 rows=1929 width=34)
-> Hash Join (cost=99.55..276.22 rows=1929 width=34)
Hash Cond: (t.uom_po_id = ui.id)
-> Hash Join (cost=96.98..247.12 rows=1929 width=31)
Hash Cond: (t.uos_id = uo.id)
-> Hash Join (cost=94.40..218.02 rows=1929 width=28)
Hash Cond: (t.id = p.product_tmpl_id)
-> Seq Scan on product_template t (cost=0.00..94.55 rows=1955 width=28)
-> Hash (cost=70.29..70.29 rows=1929 width=8)
-> Seq Scan on product_product p (cost=0.00..70.29 rows=1929 width=8)
-> Hash (cost=1.70..1.70 rows=70 width=11)
-> Seq Scan on product_uom uo (cost=0.00..1.70 rows=70 width=11)
-> Hash (cost=1.70..1.70 rows=70 width=11)
-> Seq Scan on product_uom ui (cost=0.00..1.70 rows=70 width=11)
Enfin, les indexs que j'ai mis en place :
create index fu_cart_item_create_date_idx on fu_cart_item (create_date);
create index fu_cart_item_product_idx on fu_cart_item (product);
Hors ligne
Vu la requête, je ne vois pas vraiment comment les deux index dont tu parles pourraient être utilisés. Un index sur fu_cart.state aurait plus de chances. De toute façon, les tables n'ont pas l'air très grosses. Les estimations données pour un seqscan de product_uom donnent 70 lignes, pour product_product 1929 lignes. Seule fu_cart se dégage du lot avec 409261 lignes, mais même là, c'est pas énorme.
Un index sur fu_cart_item.product (et product.id s'il n'en a pas déjà un) pourrait apporter un gain.
Qu'est-ce que tu entends par « assez longue » ? peux-tu nous fournir un explain analyze ? as-tu configuré ton postgresql.conf ? (notamment shared_buffers)
Guillaume.
Hors ligne
je suis d'accord sur les volumes, mais le temps d'execution est d'au moins 10 Sec et cette vue est interpellée très souvent.
je pensais qu'il etait possible d'eviter un seq scan sur la partie fu_cart_item.
Hors ligne
Le problème est que tu ne testes pas rapport à la valeur de la colonne mais par rapport à la valeur du résultat d'une fonction dont un des arguments est cette colonne. L'index brut ne sert à rien dans ce cas. Si tu testais le résultat de cette fonction avec un nombre connu, tu pourrais faire un index sur le résultat de la fonction. Mais là, tu testes que le résultat d'une fonction est égal au résultat d'une autre. Pas d'index possible à ma connaissance.
Réponds aux questions précédemment posées, on verra si on peux descendre le temps d'exécution.
Guillaume.
Hors ligne
Désolé, il faut que je pense a lire de manièe plus posée les réponses aux questions que je pose.
Le temps précis d'exécution est de 18 sec.
pour le postgresql.conf, voila les valeurs modifiees :
work_mem = 128 MB
maintenance_work_mem = 128 MB
shared_buffer = 24MB
Voici l'explain analyse ( au fait qu'elle est la différence) :
HashAggregate (cost=221168.82..221323.26 rows=2376 width=110) (actual time=26921.622..26921.629 rows=1 loops=1)
-> Hash Join (cost=219743.46..220812.48 rows=23756 width=110) (actual time=26917.635..26918.813 rows=429 loops=1)
Hash Cond: (i.product = p.id)
-> HashAggregate (cost=219456.13..219931.25 rows=23756 width=27) (actual time=26903.674..26904.219 rows=429 loops=1)
-> Hash Join (cost=17672.46..219159.18 rows=23756 width=27) (actual time=10141.068..26637.840 rows=94945 loops=1)
Hash Cond: (i.cart = c.id)
-> Seq Scan on fu_cart_item i (cost=0.00..200878.91 rows=25147 width=31) (actual time=9422.517..25647.737 rows=95379 loops=1)
Filter: ((product IS NOT NULL) AND (date_trunc('WEEK'::text, create_date) = date_trunc('WEEK'::text, (now() - '7 days'::interval))))
-> Hash (cost=12531.30..12531.30 rows=411293 width=4) (actual time=718.259..718.259 rows=410021 loops=1)
-> Seq Scan on fu_cart c (cost=0.00..12531.30 rows=411293 width=4) (actual time=0.045..438.845 rows=410021 loops=1)
Filter: ((state)::text <> 'cancelled'::text)
-> Hash (cost=263.22..263.22 rows=1929 width=34) (actual time=13.931..13.931 rows=1930 loops=1)
-> Hash Join (cost=98.55..263.22 rows=1929 width=34) (actual time=3.083..12.155 rows=1930 loops=1)
Hash Cond: (t.uom_po_id = ui.id)
-> Hash Join (cost=95.98..234.12 rows=1929 width=31) (actual time=2.955..9.708 rows=1930 loops=1)
Hash Cond: (t.uos_id = uo.id)
-> Hash Join (cost=93.40..205.02 rows=1929 width=28) (actual time=2.828..7.323 rows=1930 loops=1)
Hash Cond: (t.id = p.product_tmpl_id)
-> Seq Scan on product_template t (cost=0.00..82.55 rows=1955 width=28) (actual time=0.006..1.069 rows=1956 loops=1)
-> Hash (cost=69.29..69.29 rows=1929 width=8) (actual time=2.794..2.794 rows=1930 loops=1)
-> Seq Scan on product_product p (cost=0.00..69.29 rows=1929 width=8) (actual time=0.007..1.542 rows=1930 loops=1)
-> Hash (cost=1.70..1.70 rows=70 width=11) (actual time=0.100..0.100 rows=70 loops=1)
-> Seq Scan on product_uom uo (cost=0.00..1.70 rows=70 width=11) (actual time=0.004..0.053 rows=70 loops=1)
-> Hash (cost=1.70..1.70 rows=70 width=11) (actual time=0.110..0.110 rows=70 loops=1)
-> Seq Scan on product_uom ui (cost=0.00..1.70 rows=70 width=11) (actual time=0.012..0.061 rows=70 loops=1)
Total runtime: 26923.415 ms
(26 rows)
Hors ligne
work_mem = 128 MB
maintenance_work_mem = 128 MB
shared_buffer = 24MB
Attention, work_mem est très élevé. Je ne sais pas combien vaut max_connections mais s'il a la valeur par défaut et que tu as tous tes clients connectés et exécutant une requête avec un tri en même temps, tu vas consommer jusqu'à 12 Go de RAM. Bref, diminue-le.
shared_buffers me semble bien petit. Le grossir peut être intéressant, surtout vu que tu es sous Unix.
Voici l'explain analyse ( au fait qu'elle est la différence) :
La différence est simple. PostgreSQL exécute réellement la requête et récupère les statistiques exactes. Par exemple, le SeqScan sur product_product. Les permières infos entre parenthèses indiquent l'estimation du planificateur (1929 lignes récupérées), la deuxième partie dans l'autre groupe de parenthèse indiquent les valeurs vraies une fois exécutées (1930 lignes). La différence entre estimation et réalité peut expliquer de mauvais plans d'exécution (comme la non utilisation d'un index). Ce qui ne semble pas être ton cas.
... contenu du explain analyze... la partie intéressante est là:
-> Seq Scan on fu_cart_item i (cost=0.00..200878.91 rows=25147 width=31) (actual time=9422.517..25647.737 rows=95379 loops=1)
Filter: ((product IS NOT NULL) AND (date_trunc('WEEK'::text, create_date) = date_trunc('WEEK'::text, (now() - '7 days'::interval))))
Parce que c'est la partie la plus coûteuse. Sur les 27 secondes d'exécution, le parcours séquentiel de cette table prends 25 secondes. Donc, en effet, il serait bien d'éviter un SeqScan sur fu_cart_item. Je viens de relire ta requête. « DATE_TRUNC('WEEK',NOW()-INTERVAL '1 WEEK') » est une constante, donc tu peux faire un index sur l'autre partie. Donc voici ce qu'il faudrait que tu testes :
* crée l'index suivant : CREATE INDEX idxtest ON fu_cart_item(DATE_TRUNC('WEEK',create_date));
* fais un ANALYZE : ANALYZE;
* teste ta requête seule
* teste ta requête avec un EXPLAIN ANALYZE
Et poste tout ça ici pour discussion.
Et si tu pouvais répondre à deux questions que je me pose :
* quelle est le nombre de lignes de cette table ?
* quelle est le nombre de lignes renvoyées par ce filtre ((product IS NOT NULL) AND (date_trunc('WEEK'::text, create_date) = date_trunc('WEEK'::text, (now() - '7 days'::interval)))) ? à priori 95379
Guillaume.
Hors ligne
bonjour,
La creation de l'index a reduit la duree d'execution de 27 Sec a 1,5 Sec
Pour les deux questions précedentes, la requête ne retourne qu'une seule ligne.
la table contient 5 042 596 de lignes au total et le filtre retourne bien 95379 lignes.
Merci pour tout. Je ne savais pas que l'on pouvait créer des index avec des fonctions. Je pensais que j'etais limiter uniquement sur les noms de champs.
Je réglerais les paramètres serveurs lundi matin (seule période ou je peux faire un restart serveur sans risque).
c'est super. j'ai pas mal appris sur ce coup là.
Merci a tous.
voici le explain plan analyse apres creation index
HashAggregate (cost=66286.76..66683.19 rows=6099 width=110) (actual time=1509.450..1509.456 rows=1 loops=1)
-> Hash Join (cost=62627.78..65372.01 rows=60983 width=110) (actual time=1505.080..1506.389 rows=429 loops=1)
Hash Cond: (i.product = p.id)
-> HashAggregate (cost=62340.10..63559.76 rows=60983 width=27) (actual time=1490.544..1491.217 rows=429 loops=1)
-> Hash Join (cost=17714.02..60813.85 rows=122100 width=27) (actual time=747.899..1225.569 rows=94945 loops=1)
Hash Cond: (i.cart = c.id)
-> Index Scan using idxtest on fu_cart_item i (cost=0.01..39975.36 rows=129298 width=31) (actual time=0.339..189.369 rows=95379 loops=1)
Index Cond: (date_trunc('WEEK'::text, create_date) = date_trunc('WEEK'::text, (now() - '7 days'::interval)))
Filter: (product IS NOT NULL)
-> Hash (cost=12558.95..12558.95 rows=412405 width=4) (actual time=747.299..747.299 rows=410654 loops=1)
-> Seq Scan on fu_cart c (cost=0.00..12558.95 rows=412405 width=4) (actual time=0.033..455.287 rows=410654 loops=1)
Filter: ((state)::text <> 'cancelled'::text)
-> Hash (cost=263.51..263.51 rows=1933 width=34) (actual time=14.490..14.490 rows=1933 loops=1)
-> Hash Join (cost=98.64..263.51 rows=1933 width=34) (actual time=3.192..12.623 rows=1933 loops=1)
Hash Cond: (t.uom_po_id = ui.id)
-> Hash Join (cost=96.07..234.36 rows=1933 width=31) (actual time=3.057..10.132 rows=1933 loops=1)
Hash Cond: (t.uos_id = uo.id)
-> Hash Join (cost=93.49..205.21 rows=1933 width=28) (actual time=2.929..7.689 rows=1933 loops=1)
Hash Cond: (t.id = p.product_tmpl_id)
-> Seq Scan on product_template t (cost=0.00..82.59 rows=1959 width=28) (actual time=0.006..1.142 rows=1959 loops=1)
-> Hash (cost=69.33..69.33 rows=1933 width=8) (actual time=2.886..2.886 rows=1933 loops=1)
-> Seq Scan on product_product p (cost=0.00..69.33 rows=1933 width=8) (actual time=0.007..1.600 rows=1933 loops=1)
-> Hash (cost=1.70..1.70 rows=70 width=11) (actual time=0.101..0.101 rows=70 loops=1)
-> Seq Scan on product_uom uo (cost=0.00..1.70 rows=70 width=11) (actual time=0.005..0.052 rows=70 loops=1)
-> Hash (cost=1.70..1.70 rows=70 width=11) (actual time=0.112..0.112 rows=70 loops=1)
-> Seq Scan on product_uom ui (cost=0.00..1.70 rows=70 width=11) (actual time=0.014..0.065 rows=70 loops=1)
Total runtime: 1511.664 ms
Hors ligne
Trois types d'index avec PostgreSQL : complet (l'index normal en fait), partiel (en fournissant une clause WHERE), et fonctionnel (en fournissant le résultat d'une fonction comme ici).
Guillaume.
Hors ligne
bonjour gleu et encore merci pour ton aide.
petite info sur mon postgresql.conf. tu me dit que l'occupation memoire peu atteindre 12Go en fonction du nombre de conntectés et des requêtes lancée.
Le serveur qui fait tourner la base est un BI Quad Core avec 16 go de RAM.
Il fait notamment tourner un ERP open source (OpenErp pour ne pas le nommer).
Est-il vraiment utile de baisser les paramètres ?
il y a t'il des paramètres qui me permettrais d'optimiser le fonction de la base sur ce serveur.
Pour le moment les paramètres modifier sont : work_mem, maintenance_work_mem et share_buffer.
Apparemment, le serveur n'a pas l'air trop soliciter par postgresql.
C'est plus le serveur python qui prend de la ressource.
Hors ligne
Je dirais que tant que tout va bien, autant ne pas trop modifier le fichier de confs. Mais il y a quand même des paramètres importants comme max_connections, shared_buffers, work_mem, maintenance_work_mem, max_fsm_pages, max_fsm_relations, wal_buffers, checkpoint_segments, sans parler de ceux de log. Il faut au moins avoir fait une passe dessus.
Guillaume.
Hors ligne
Pages : 1
