Forums PostgreSQL.fr

saigamp

Membre

mauvais choix de l'optimiseur

Bonjour,

Je tiens à signaler ce qui me semble être un problème de l'optimiseur en 9.3. Veuillez m'excuser si le forum n'est pas le bon endroit pour cela.

Dans l'exemple qui suit, j'ai pris un extrait de la table cible (0.5M lignes sur 14M).

La requête que j'exécute est la suivante:

CREATE TABLE test_RP2006_541_aggrege AS
SELECT
  SUM(valeur) AS valeur, inatc, sexe, age4, iranr, nivgeo, codgeo, SUM(secret) AS nombre_secret, CASE SUM(CASE secret WHEN 0 THEN 0 ELSE 1 END) WHEN 1 THEN 3 ELSE 0 END AS secret
FROM (
  SELECT
    data.valeur, data.inatc, data.sexe, data.age4, data.iranr, zone.nivgeo_englobe AS nivgeo, zone.codgeo_englobe AS codgeo, data.secret
  FROM
    test_RP2006_541_2 data
    INNER JOIN test_emboitement_2008 zone
  ON data.nivgeo = zone.nivgeo_zone AND data.codgeo = zone.codgeo_zone
  ) agrege
GROUP BY
  nivgeo, codgeo, inatc, sexe, age4, iranr
;

La requête effectue d'abord une jointure entre 2 tables et fait un group by sur le résultat.

Avec les stats à jour sur les 2 tables, le plan d'exécution est le suivant:

 GroupAggregate  (cost=698358.79..893400.23 rows=4876036 width=124) (actual time=592732.807..616866.907 rows=114615 loops=1)
   Buffers: shared hit=6833 read=8267, temp read=80436 written=80436
   ->  Sort  (cost=698358.79..710548.88 rows=4876036 width=124) (actual time=592732.097..606417.498 rows=4857975 loops=1)
         Sort Key: zone.nivgeo_englobe, zone.codgeo_englobe, data.inatc, data.sexe, data.age4, data.iranr
         Sort Method: external merge  Disk: 643472kB
         Buffers: shared hit=6833 read=8267, temp read=80436 written=80436
         ->  Merge Join  (cost=103360.69..156697.57 rows=4876036 width=124) (actual time=10588.579..30326.928 rows=4857975 loops=1)
               Merge Cond: ((data.codgeo = zone.codgeo_zone) AND (data.nivgeo = zone.nivgeo_zone))
               Buffers: shared hit=6833 read=8267
               ->  Sort  (cost=62759.50..64009.94 rows=500175 width=124) (actual time=6186.175..6741.332 rows=500175 loops=1)
                     Sort Key: data.codgeo, data.nivgeo
                     Sort Method: quicksort  Memory: 145147kB
                     Buffers: shared hit=2144 read=8267
                     ->  Seq Scan on test_rp2006_541_2 data  (cost=0.00..15412.75 rows=500175 width=124) (actual time=0.225..337.494 rows=500175 loops=1)
                           Buffers: shared hit=2144 read=8267
               ->  Sort  (cost=40598.89..41478.01 rows=351648 width=64) (actual time=4402.378..5298.424 rows=4857976 loops=1)
                     Sort Key: zone.codgeo_zone, zone.nivgeo_zone
                     Sort Method: quicksort  Memory: 61739kB
                     Buffers: shared hit=4689
                     ->  Seq Scan on test_emboitement_2008 zone  (cost=0.00..8205.48 rows=351648 width=64) (actual time=0.058..197.162 rows=351648 loops=1)
                           Buffers: shared hit=4689
 Total runtime: 618033.066 ms

L'exécution de la requête passe le plus clair de sont temps à trier les données de la jointure des 2 tables. work_mem étant à 1GB et considérant avoir 4.8M de lignes à traiter (ce qui est vrai), il choisit le tri/groupe et écrit donc sur disque provoquant une durée d'exécution très longue. La jointure se fait par MergeJoin et dure 30s. Avec un index à chaque table sur la condition de jointure il utilise les index pour le tri et dure 26s.

Lorsque j'exécute la même requête sur les mêmes données mais avec des stats non calculées (juste après la création de l'intégralité de la table test_RP2006_541_2, donc avant le calcul des stats), le plan d'exécution est le suivant:

 HashAggregate  (cost=68959.37..69048.23 rows=7109 width=402) (actual time=23688.391..23837.439 rows=114615 loops=1)
   Buffers: shared hit=6737 read=8363 written=8331
   ->  Hash Join  (cost=13480.20..68781.64 rows=7109 width=402) (actual time=564.889..6729.138 rows=4857975 loops=1)
         Hash Cond: ((data.nivgeo = zone.nivgeo_zone) AND (data.codgeo = zone.codgeo_zone))
         Buffers: shared hit=6737 read=8363 written=8331
         ->  Seq Scan on test_rp2006_541_2 data  (cost=0.00..11868.54 rows=145754 width=498) (actual time=0.276..490.542 rows=500175 loops=1)
               Buffers: shared hit=2048 read=8363 written=8331
         ->  Hash  (cost=8205.48..8205.48 rows=351648 width=64) (actual time=564.190..564.190 rows=351648 loops=1)
               Buckets: 65536  Batches: 1  Memory Usage: 32967kB
               Buffers: shared hit=4689
               ->  Seq Scan on test_emboitement_2008 zone  (cost=0.00..8205.48 rows=351648 width=64) (actual time=0.025..204.801 rows=351648 loops=1)
                     Buffers: shared hit=4689
 Total runtime: 23854.337 ms

Le group by se fait alors par HashAggregate et prend beaucoup moins de temps (17s contre 586s). Je suppose que ceci vient du fait qu'il estime mal le nombre de ligne à traiter dans le group by: 7109 au lieu de 4.8M. Mais le fait est qu'il fait un HashAggregate et l'exécution est plus rapide. Techniquement ça semble possible de passer par un HashAggregate plutôt que par un SortAggregate. Il me semble qu'il n'est pas possible de "forcer" le HashAggregate car ce n'est pas dans l'idéologie de PostgreSQL.

Pour la jointure, il fait un HashJoin plutôt qu'un MergeJoin et est là aussi plus rapide: 7s contre 30s. On passe quand même en tout de 23s à 10min16s; ramené à la taille cible, on perd des heures! Les développeurs font donc actuellement leurs traitements en Oracle qui lui doit bien optimiser son plan d'exécution...

Il semble alors que l'optimiseur ne soit pas adapter à ce type de requête, somme toute banale. Est-il possible néanmoins de jouer sur certains paramètres pour faire changer d'avis à l'optimiseur? Faut-il attendre la 9.4? Sans ça le passage à PostgreSQL va être dur à faire accepter.

François

Dernière modification par saigamp (25/04/2014 16:49:11)

saigamp

Membre

Re : mauvais choix de l'optimiseur

J'ajoute qu'en 9.2 avec les stats à jour il n'effectue pas le MergeJoin mais fait un HashJoin. L'optimiseur serait sur ce point là meilleur en 9.2 qu'en 9.3!

SQLpro

Membre

Re : mauvais choix de l'optimiseur

Pouvez vous récrire votre requête en isolant les parties agrégées/clef des données non clef et en rajoutant les données non clef en liant avec la clef en final ?

Si vous ne me comprenez pas, donnez le DDL de vos tables / vues et on vous la récrira.

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  * * * * *

gleu

Administrateur

Re : mauvais choix de l'optimiseur

Désactiver le tri (enable_sort à off) puis regarder le plan généré, avec des stats à jours. Il devrait changer son plan, ce qui nous donnera plus d'informations.

---

Guillaume.

saigamp

Membre

Re : mauvais choix de l'optimiseur

Pouvez vous récrire votre requête en isolant les parties agrégées/clef des données non clef et en rajoutant les données non clef en liant avec la clef en final ?

Si vous ne me comprenez pas, donnez le DDL de vos tables / vues et on vous la récrira.

A +

Comme je n'ai pas tout compris:

CREATE TABLE test_rp2006_541_2
(
  valeur numeric,
  maximum numeric,
  inatc character(20),
  sexe character(20),
  age4 character(20),
  iranr character(20),
  nivgeo character(10),
  codgeo character(20),
  secret smallint,
  est_englobante smallint
)
WITH (
  OIDS=FALSE
);

CREATE TABLE test_emboitement_2008
(
  annee numeric,
  id_zone integer,
  nivgeo_zone character(10),
  codgeo_zone character(20),
  id_englobe integer,
  nivgeo_englobe character(10),
  codgeo_englobe character(20)
)
WITH (
  OIDS=FALSE
);

saigamp

Membre

Re : mauvais choix de l'optimiseur

Désactiver le tri (enable_sort à off) puis regarder le plan généré, avec des stats à jours. Il devrait changer son plan, ce qui nous donnera plus d'informations.

J'avais fait le test avec enable_sort à off, mais le résultat était le même.

gleu

Administrateur

Re : mauvais choix de l'optimiseur

On pourrait voir le plan du EXPLAIN ANALYZE avec cette option désactivée ?

---

Guillaume.

saigamp

Membre

Re : mauvais choix de l'optimiseur

avec enable_sort = 'off':

 GroupAggregate  (cost=10000649295.98..10000844337.42 rows=4876036 width=124) (actual time=586089.874..610259.810 rows=114615 loops=1)
   Buffers: shared hit=5310492, temp read=80436 written=80436
   ->  Sort  (cost=10000649295.98..10000661486.07 rows=4876036 width=124) (actual time=586089.059..599872.303 rows=4857975 loops=1)
         Sort Key: zone.nivgeo_englobe, zone.codgeo_englobe, data.inatc, data.sexe, data.age4, data.iranr
         Sort Method: external merge  Disk: 643472kB
         Buffers: shared hit=5310492, temp read=80436 written=80436
         ->  Merge Join  (cost=31.58..107634.76 rows=4876036 width=124) (actual time=0.052..26496.616 rows=4857975 loops=1)
               Merge Cond: ((data.codgeo = zone.codgeo_zone) AND (data.nivgeo = zone.nivgeo_zone))
               Buffers: shared hit=5310492
               ->  Index Scan using index_test_rp2006_541_2_1 on test_rp2006_541_2 data  (cost=0.42..47741.97 rows=500175 width=124) (actual time=0.011..1594.075 rows=500175 loops=1)
                     Buffers: shared hit=493245
               ->  Index Scan using index_test_emboitement_2008_1 on test_emboitement_2008 zone  (cost=0.42..25750.73 rows=351648 width=64) (actual time=0.008..5622.723 rows=4857976 loops=1)
                     Buffers: shared hit=4817247
 Total runtime: 611316.395 ms

gleu

Administrateur

Re : mauvais choix de l'optimiseur

Oui, ma faute. Les deux Sort initiaux ont été logiquement remplacés par deux parcours d'index, le coût du Sort devenant prohibitif. Par contre, le troisième Sort, bien que prohibitif lui-aussi, ne peut pas être remplacé par un parcours d'index (tri de colonnes sur deux tables différentes). Or c'est lui qui est coûteux. Il y a donc bien eu un changement au niveau du plan mais pas celui que j'espérais.

À mon avis, le coût vient de l'écriture du fichier temporaire. Vous êtes sûr d'être en 9.3 ? Pouvez-vous essayer avec un work_mem plus important ? (2GB par exemple).

---

Guillaume.

saigamp

Membre

Re : mauvais choix de l'optimiseur

En fait, j'avais remis des index sur les tables entre temps, d'où les parcours d'index. Sans les index et avec enable_sort à off, il fait un HashJoin et ne met que 7s au lieu des 26s avec parcours d'index!! Donc on gagne bien avec enable_sort à off sur la jointure en retombant sur ce qui se fait lorsque les stats ne sont pas à jour ou lorsqu'on est en 9.2 (je suis bien en 9.3.4).

 GroupAggregate  (cost=10000765934.81..10000960295.97 rows=4859029 width=124) (actual time=561800.172..589355.040 rows=114615 loops=1)
   Buffers: shared hit=15100, temp read=80436 written=80436
   ->  Sort  (cost=10000765934.81..10000778082.38 rows=4859029 width=124) (actual time=561799.122..578854.863 rows=4857975 loops=1)
         Sort Key: zone.nivgeo_englobe, zone.codgeo_englobe, data.inatc, data.sexe, data.age4, data.iranr
         Sort Method: external merge  Disk: 643472kB
         Buffers: shared hit=15100, temp read=80436 written=80436
         ->  Hash Join  (cost=13480.20..226285.30 rows=4859029 width=124) (actual time=637.942..7134.940 rows=4857975 loops=1)
               Hash Cond: ((data.nivgeo = zone.nivgeo_zone) AND (data.codgeo = zone.codgeo_zone))
               Buffers: shared hit=15100
               ->  Seq Scan on test_rp2006_541_2 data  (cost=0.00..15412.75 rows=500175 width=124) (actual time=0.101..252.521 rows=500175 loops=1)
                     Buffers: shared hit=10411
               ->  Hash  (cost=8205.48..8205.48 rows=351648 width=64) (actual time=636.881..636.881 rows=351648 loops=1)
                     Buckets: 65536  Batches: 1  Memory Usage: 32967kB
                     Buffers: shared hit=4689
                     ->  Seq Scan on test_emboitement_2008 zone  (cost=0.00..8205.48 rows=351648 width=64) (actual time=0.041..209.633 rows=351648 loops=1)
                           Buffers: shared hit=4689
 Total runtime: 590458.096 ms

J'ai remarqué qu'avec un work_mem au double de ce dont il a besoin pour écrire le sort (ici, 643472kB), il effectue un HashAggregate et est rapide (comme lorsque les stats ne sont pas à jour). Par exemple avec work_mem='1300MB', il ne met plus que 24s en tout (contre 10min). Ces tests sont faits sur un extrait de la table cible de 14M de lignes. Il faudrait alors un work_mem à 37GB pour faire un HashAggregate à partir de la table cible, ce qui n'est pas une solution sachant qu'il est "capable" de le faire sans cette taille de work_mem avec des stats "fausses".

Existe-t-il une solution temporaire ou doit-on attendre la 9.4 ou plus pour ce type de requête?

saigamp

Membre

Re : mauvais choix de l'optimiseur

Pour pouvoir faire ses propres tests, je propose les 2 scripts de création de tables suivants qui permettent d'obtenir une approximation de mes tables. Pour la 2ème table, il est possible de faire varier sa taille en remplaçant 1235 par une valeur allant jusqu'à 36726.

create table t1 as select 'COM'::text nivgeo_zone, *, md5(generate_series(floor(3*random())::int,(4*random()+7)::int)::text) nivgeo_englobe, ceil(3550*exp(random())) codgeo_englobe from generate_series(1,36726) codgeo_zone;

create table t2 as select 'COM'::text as nivgeo, *, random() as valeur from (((generate_series(1,1235) codgeo inner join generate_series(1,9) iranr on 1=1) inner join generate_series(1,3) inatc on 1=1) inner join generate_series(1,5) age4 on 1=1) inner join generate_series(1,3) sexe on 1=1;

La requête que j'exécute est:

SELECT
  SUM(valeur) AS valeur, inatc, sexe, age4, iranr, nivgeo, codgeo
FROM (
  SELECT
    data.valeur, data.inatc, data.sexe, data.age4, data.iranr, zone.nivgeo_englobe AS nivgeo, zone.codgeo_englobe AS codgeo
  FROM
    t2 data
    INNER JOIN t1 zone
  ON data.nivgeo = zone.nivgeo_zone AND data.codgeo = zone.codgeo_zone
  ) agrege
GROUP BY
  nivgeo, codgeo, inatc, sexe, age4, iranr
;

Si on exécute cette requête directement à la suite de la création de la table t2 (c'est-à-dire avec des stats non calculées) la requête passe par un HashAggregate et est rapide. Si on calcule les stats, la requête fait un tri sur disque si work_mem n'est pas assez grand et est beaucoup plus lente.

Dernière modification par saigamp (29/04/2014 10:16:03)

gleu

Administrateur

Re : mauvais choix de l'optimiseur

La requête est fausse :

ERROR:  syntax error at or near ")"
LINE 7: ) agrege

J'ai supprimé ") agrege" et j'obtiens une nouvelle erreur :

ERROR:  column zone.nivgeo_zone does not exist
LINE 6: ON data.nivgeo = zone.nivgeo_zone AND data.codgeo = zone.cod...
                         ^

Bref, c'est pas ça. Par contre, j'apprécie beaucoup le self-test. Il faut juste la bonne requête

---

Guillaume.

saigamp

Membre

Re : mauvais choix de l'optimiseur

Au temps pour moi... J'ai édité la requête et la création de la première table dans le message précédent. Ça devrait fonctionner cette fois-ci.

Dernière modification par saigamp (29/04/2014 10:16:24)

gleu

Administrateur

Re : mauvais choix de l'optimiseur

Bon, j'ai testé sur mon portable. En gros, les performances sont les mêmes avant et après ANALYZE. J'ai même désactivé mon autovacuum pour être sûr qu'il ne se moquait pas de moi

Sans ANALYZE :

postgres=# explain (analyze, buffers) SELECT
  SUM(valeur) AS valeur, inatc, sexe, age4, iranr, nivgeo, codgeo
FROM (
  SELECT
    data.valeur, data.inatc, data.sexe, data.age4, data.iranr, zone.nivgeo_englobe AS nivgeo, zone.codgeo_englobe AS codgeo
  FROM
    t2 data
    INNER JOIN t1 zone
  ON data.nivgeo = zone.nivgeo_zone AND data.codgeo = zone.codgeo_zone
  ) agrege
GROUP BY
  nivgeo, codgeo, inatc, sexe, age4, iranr
;
                                                                  QUERY PLAN                                                                   
-----------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=629352.09..694481.67 rows=2170986 width=64) (actual time=26687.083..31854.412 rows=4079970 loops=1)
   Buffers: shared hit=4096 read=2989 dirtied=7085, temp read=202680 written=205255
   ->  Sort  (cost=629352.09..634779.56 rows=2170986 width=64) (actual time=26687.070..30186.325 rows=4486995 loops=1)
         Sort Key: zone.nivgeo_englobe, zone.codgeo_englobe, data.inatc, data.sexe, data.age4, data.iranr
         Sort Method: external merge  Disk: 359696kB
         Buffers: shared hit=4096 read=2989 dirtied=7085, temp read=202680 written=205255
         ->  Merge Join  (cost=115188.35..155975.69 rows=2170986 width=64) (actual time=2790.844..4995.593 rows=4486995 loops=1)
               Merge Cond: ((zone.nivgeo_zone = data.nivgeo) AND (zone.codgeo_zone = data.codgeo))
               Buffers: shared hit=4096 read=2989 dirtied=7085, temp read=6880 written=9455
               ->  Sort  (cost=52959.31..53615.15 rows=262339 width=76) (actual time=1007.896..1014.713 rows=11080 loops=1)
                     Sort Key: zone.nivgeo_zone, zone.codgeo_zone
                     Sort Method: external sort  Disk: 21328kB
                     Buffers: shared hit=2048 read=1359 dirtied=3407, temp read=91 written=2666
                     ->  Seq Scan on t1 zone  (cost=0.00..6030.39 rows=262339 width=76) (actual time=0.044..75.394 rows=330418 loops=1)
                           Buffers: shared hit=2048 read=1359 dirtied=3407
               ->  Materialize  (cost=62229.04..63884.14 rows=331020 width=60) (actual time=1782.935..2637.027 rows=4486990 loops=1)
                     Buffers: shared hit=2048 read=1630 dirtied=3678, temp read=6789 written=6789
                     ->  Sort  (cost=62229.04..63056.59 rows=331020 width=60) (actual time=1782.931..2266.112 rows=500175 loops=1)
                           Sort Key: data.nivgeo, data.codgeo
                           Sort Method: external merge  Disk: 20552kB
                           Buffers: shared hit=2048 read=1630 dirtied=3678, temp read=6789 written=6789
                           ->  Seq Scan on t2 data  (cost=0.00..6988.20 rows=331020 width=60) (actual time=0.037..122.679 rows=500175 loops=1)
                                 Buffers: shared hit=2048 read=1630 dirtied=3678
 Total runtime: 32137.151 ms
(24 rows)

Avec ANALYZE :

postgres=# explain (analyze, buffers) SELECT
  SUM(valeur) AS valeur, inatc, sexe, age4, iranr, nivgeo, codgeo
FROM (
  SELECT
    data.valeur, data.inatc, data.sexe, data.age4, data.iranr, zone.nivgeo_englobe AS nivgeo, zone.codgeo_englobe AS codgeo
  FROM
    t2 data
    INNER JOIN t1 zone
  ON data.nivgeo = zone.nivgeo_zone AND data.codgeo = zone.codgeo_zone
  ) agrege
GROUP BY
  nivgeo, codgeo, inatc, sexe, age4, iranr
;
                                                               QUERY PLAN                                                               
----------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=1225036.29..1362919.44 rows=4596105 width=65) (actual time=24481.894..29912.238 rows=4094145 loops=1)
   Buffers: shared hit=4124 read=2963, temp read=203526 written=203464
   ->  Sort  (cost=1225036.29..1236526.55 rows=4596105 width=65) (actual time=24481.883..28299.933 rows=4511700 loops=1)
         Sort Key: zone.nivgeo_englobe, zone.codgeo_englobe, data.inatc, data.sexe, data.age4, data.iranr
         Sort Method: external merge  Disk: 361680kB
         Buffers: shared hit=4124 read=2963, temp read=203526 written=203464
         ->  Hash Join  (cost=14905.53..150891.26 rows=4596105 width=65) (actual time=201.674..1993.739 rows=4511700 loops=1)
               Hash Cond: ((data.nivgeo = zone.nivgeo_zone) AND (data.codgeo = zone.codgeo_zone))
               Buffers: shared hit=4124 read=2963, temp read=6121 written=6059
               ->  Seq Scan on t2 data  (cost=0.00..8679.75 rows=500175 width=32) (actual time=0.056..109.649 rows=500175 loops=1)
                     Buffers: shared hit=2076 read=1602
               ->  Hash  (cost=6715.61..6715.61 rows=330661 width=49) (actual time=201.146..201.146 rows=330661 loops=1)
                     Buckets: 2048  Batches: 32  Memory Usage: 941kB
                     Buffers: shared hit=2048 read=1361, temp written=2959
                     ->  Seq Scan on t1 zone  (cost=0.00..6715.61 rows=330661 width=49) (actual time=0.016..77.980 rows=330661 loops=1)
                           Buffers: shared hit=2048 read=1361
 Total runtime: 30168.335 ms
(17 rows)

32 secondes contre 30 secondes. Kif kif. Pour infos, mon postgresql.conf n'est pas modifié.

---

Guillaume.

saigamp

Membre

Re : mauvais choix de l'optimiseur

J'ai fait le test avec le postgresql.conf.sample et le résultat est toujours lent. J'en déduit que j'ai surtout un problème de matériel. Pendant les 10 min de l'exécution, ma cpu est à 100%, je vais creuser par là.

J'ai critiqué un peu vite l'optimiseur avant de mettre en cause mon matériel

Merci pour avoir pris le temps de tester tout ceci

arthurr

Membre

Re : mauvais choix de l'optimiseur

Pas grand chose à faire : il doit trier 4 486 995 lignes sur 6 colonnes (zone.nivgeo_englobe, zone.codgeo_englobe, data.inatc, data.sexe, data.age4, data.iranr) -> le group by
et vu la taille, il est obligé de passer par un fichier sur disque (360Mo), les données ne tenant pas dans le work_mem.
Donc : c'est du cpu et du disque

arthurr

Membre

Re : mauvais choix de l'optimiseur

Pas grand chose à faire : il doit trier 4 486 995 lignes sur 6 colonnes (zone.nivgeo_englobe, zone.codgeo_englobe, data.inatc, data.sexe, data.age4, data.iranr) -> le group by
et vu la taille, il est obligé de passer par un fichier sur disque (360Mo), les données ne tenant pas dans le work_mem.
Donc : c'est du cpu et du disque

j'ai répondu trop vite (je n'ai lu que votre dernier post et pas depuis le début) : ma réponse ne va pas aider beaucoup .... désolé

arthurr

Membre

Re : mauvais choix de l'optimiseur

Peut être une solution à creuser : une requête CTE.
Elle va prendre 9 sec (contre 6 sec pour votre requête mais avec un work_mem = 1GB) sur mon simple PC mais ne nécessite "que" 64MB de work_mem pour fonctionner.
Avec un work_mem de 32MB elle monte à 12sec

test=# set work_mem to '64MB';
SET
Time: 0,166 ms
test=# explain(analyse, verbose) WITH toto as (select distinct nivgeo_zone,codgeo_zone,nivgeo_englobe,codgeo_englobe from t1)
SELECT
sum(valeur),
inatc,
sexe,
age4,
iranr,
nivgeo_englobe,
codgeo_englobe
from
t2 data join toto on (data.nivgeo = toto.nivgeo_zone AND data.codgeo = toto.codgeo_zone)
group by 2,3,4,5,6,7;
                                                                            QUERY PLAN                                                                            
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=40376.15..41106.14 rows=72999 width=64) (actual time=7817.584..9237.119 rows=4104270 loops=1)
   Output: sum(data.valeur), data.inatc, data.sexe, data.age4, data.iranr, toto.nivgeo_englobe, toto.codgeo_englobe
   Group Key: data.inatc, data.sexe, data.age4, data.iranr, toto.nivgeo_englobe, toto.codgeo_englobe
   CTE toto
     ->  HashAggregate  (cost=10004.24..10364.73 rows=36049 width=49) (actual time=189.425..296.358 rows=330062 loops=1)
           Output: t1.nivgeo_zone, t1.codgeo_zone, t1.nivgeo_englobe, t1.codgeo_englobe
           Group Key: t1.nivgeo_zone, t1.codgeo_zone, t1.nivgeo_englobe, t1.codgeo_englobe
           ->  Seq Scan on public.t1  (cost=0.00..6703.62 rows=330062 width=49) (actual time=0.002..19.243 rows=330062 loops=1)
                 Output: t1.nivgeo_zone, t1.codgeo_zone, t1.nivgeo_englobe, t1.codgeo_englobe
   ->  Merge Join  (cost=3449.89..28733.94 rows=72999 width=64) (actual time=2414.251..5089.454 rows=4492665 loops=1)
         Output: data.inatc, data.sexe, data.age4, data.iranr, data.valeur, toto.nivgeo_englobe, toto.codgeo_englobe
         Merge Cond: ((data.nivgeo = toto.nivgeo_zone) AND (data.codgeo = toto.codgeo_zone))
         ->  Index Scan using t2_nivgeo_codgeo_idx on public.t2 data  (cost=0.42..21506.11 rows=500175 width=32) (actual time=0.016..131.288 rows=500175 loops=1)
               Output: data.nivgeo, data.codgeo, data.iranr, data.inatc, data.age4, data.sexe, data.valeur
         ->  Sort  (cost=3449.47..3539.59 rows=36049 width=76) (actual time=2414.227..2636.629 rows=4492666 loops=1)
               Output: toto.nivgeo_englobe, toto.codgeo_englobe, toto.nivgeo_zone, toto.codgeo_zone
               Sort Key: toto.nivgeo_zone, toto.codgeo_zone
               Sort Method: quicksort  Memory: 55778kB
               ->  CTE Scan on toto  (cost=0.00..720.98 rows=36049 width=76) (actual time=189.428..392.457 rows=330062 loops=1)
                     Output: toto.nivgeo_englobe, toto.codgeo_englobe, toto.nivgeo_zone, toto.codgeo_zone
 Planning time: 0.185 ms
 Total runtime: 9389.032 ms
(22 rows)

Time: 9393,596 ms

saigamp

Membre

Re : mauvais choix de l'optimiseur

Merci pour cette réécriture de la requête qui améliore grandement les choses de mon côté: je suis tombé à 32s (même plan d'exécution). La plus grande partie de l'amélioration se situe dans l'utilisation du HashAggregate final (comme avec des stats fausses). Avec la table de 14M de lignes, la requête ne met plus que 14min au lieu de plusieurs heures.

Cette solution nous dépanne temporairement, tant il semble que nous ayons un problème de matériel qui nous faut régler...

saigamp

Membre

Re : mauvais choix de l'optimiseur

Je relance cette conversation car après avoir testé plusieurs types de matériels, je reproduis toujours le même problème: le tri sur disque lors du GroupAggregate est très lent. Étant en environnement virtuel, et ayant toujours entendu dire que le virtuel et les SGBD ne faisaient pas bon ménage, j'ai cru que la virtualisation était la source de mon problème. Or sur un PC la lenteur se reproduit (même configuration logicielle, postgresql.conf par défaut). Peut-être est-ce dû là à la lenteur des disques durs classiques. Mais sur votre portable ça fonctionne. Est-ce dû à la fréquence de mes processeurs (1.6GHz en virtuel et 1Ghz sur le PC)? Sur un serveur AiX sur Power6 avec SAN: idem.

Quelqu'un arrive-t-il à reproduire mon problème? Je suis sur Debian Wheezy avec PostgreSQL 9.3.4. La configuration de mes paramètres système est:

kernel.shmall = 134217728
kernel.shmmax = 549755813888
vm.dirty_background_ratio = 1
vm.dirty_ratio = 2
vm.swappiness = 10
vm.zone_reclaim_mode = 0
vm.overcommit_memory = 2
vm.overcommit_ratio = 90

Les disques sont formatés en ext4 et montés avec les options defaults,noatime,nodiratime.

arthurr

Membre

Re : mauvais choix de l'optimiseur

pour moi, le HashAggregate va utiliser du CPU et des IO sur les disques.
Faire un tri avec un CPU 1Ghz ne va pas donner les mêmes résultats qu'avec un cpu à 3.2Ghz (vous devez observer un CPU à 100% pendant le traitement de la requête).
PostgreSQL ne sait pas utiliser plusieurs CPU pour le moment, donc plus la vitesse d'un core va être rapide plus ça va aller vite.
Pour le disque, pas de mystère : si il doit écrire pour lire sur un fichier de N Go, il faut des disques rapides (SSD, RAID10 en 15k, ...).

saigamp

Membre

Re : mauvais choix de l'optimiseur

La CPU est bien à 100% pendant l'exécution de la requête. Mon vCPU est cadencé à 1.6GHz, le tri sur disque met 652s. Ce même tri met 26s sur le portable de gleu. La vitesse de tri ne doit pas être proportionnelle à la fréquence du processeur, car si on omet l'utilisation du disque gleu doit avoir un processeur à 40GHz!! Peut-être exponentielle?

Concernant l'écriture sur disque, il n'y a jamais que 350Mo à écrire. Il ne faut pas plus de quelques seconde au pire pour le faire. Par contre je n'ai pas compris le "écrire pour lire". Quel est le mécanisme mis en œuvre? Mon disque virtuel est sur un datastore du SAN.

Je dois avoir une mauvaise combinaison CPU+disque.

arthurr

Membre

Re : mauvais choix de l'optimiseur

les données temporaires (qui ne tiennent pas dans le work_mem) sont écrite sur disque puis lues pour un tri par exemple

gleu

Administrateur

Re : mauvais choix de l'optimiseur

Vous avez un soucis soit au niveau de la supervision, soit au niveau des disques. Je ne voisd pas d'autres explications. Avez-vous testé les performances disques avec un bête dd ? par exemple :

$ time dd if=/dev/zero of=tests bs=8192 count=50000
50000+0 records in
50000+0 records out
409600000 bytes (410 MB) copied, 0.528054 s, 776 MB/s

---

Guillaume.

saigamp

Membre

Re : mauvais choix de l'optimiseur

J'avais fait des tests avec sysbench sans savoir à quels chiffres les comparer. Le test avec dd me renvoie 290MB/s. Couplé avec un processeur de faible fréquence, ceci doit expliquer cela!

Merci pour votre aide!