Vous n'êtes pas identifié(e).
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 msL'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 msLe 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)
Hors ligne
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!
Hors ligne
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 * * * * *
Hors ligne
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.
Hors ligne
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
);Hors ligne
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.
Hors ligne
On pourrait voir le plan du EXPLAIN ANALYZE avec cette option désactivée ?
Guillaume.
Hors ligne
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 msHors ligne
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.
Hors ligne
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 msJ'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?
Hors ligne
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)
Hors ligne
La requête est fausse :
ERROR: syntax error at or near ")"
LINE 7: ) agregeJ'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.
Hors ligne
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)
Hors ligne
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.
Hors ligne
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 
Hors ligne
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
Hors ligne
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é
Hors ligne
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 msHors ligne
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...
Hors ligne
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 = 90Les disques sont formatés en ext4 et montés avec les options defaults,noatime,nodiratime.
Hors ligne
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, ...).
Hors ligne
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.
Hors ligne
les données temporaires (qui ne tiennent pas dans le work_mem) sont écrite sur disque puis lues pour un tri par exemple
Hors ligne
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/sGuillaume.
Hors ligne
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!
Hors ligne