Forums PostgreSQL.fr

Postgres.0

Membre

Optimisation d'une requête PG 9.2

Bonjour,

je sollicite votre aide pour optimiser cette requête.
J'ai augmenter le work_mem jusqu'à 3 GB et rien n'a changé.
Le shared_buffers est de 4GB et la RAM est de 16 GB.
La table point a 172721268 enregistrements alors que la table equipement a 4971
id est la clef primaire de la table equipement (la colonne zone_usage n'est pas indexée)
Pour la table point, j'ai la liste d'index suivante:

    "point_pkey" PRIMARY KEY, btree (date_, id_equipement, id_type_donnee, id_site)
    "idx_optim_elec_conso" UNIQUE, btree (id_equipement, date_)
    "idx_conso_site" btree (id_site, date_)
    "point_cons_fk_equipement" btree (id_equipement)

EXPLAIN ANALYZE  SELECT pec.id_site,
    eq.zone_usage,
    date_trunc('hour'::text, pec.date_ - '00:05:00'::interval) AS date_,
    sum(pec.valeur_conso_hc) AS somme_valeur_conso_hc,
    sum(pec.valeur_conso_hp_base) AS somme_valeur_conso_hp_base,
    sum(pec.indicateur_index_courant) AS indicateur_index_courant,
    count(*) AS cnt,
    count(pec.valeur_conso_hc) AS cnt_valeur_conso_hc,
    count(pec.valeur_conso_hp_base) AS cnt_valeur_conso_hp_base,
    count(pec.indicateur_index_courant) AS cnt_indicateur_index_courant,
    count(pec.id_site) AS cnt_id_site,
    count(pec.date_consolidation) AS cnt_date_,
    count(eq.zone_usage) AS cnt_zone_usage
   FROM point pec,
    equipement eq
  WHERE pec.indicateur >= 0 AND eq.id = pec.id_equipement
  GROUP BY pec.id_site, eq.zone_usage, date_trunc('hour'::text, pec.date_ - '00:05:00'::interval);

                                                                          QUERY PLAN                                           
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=42164787.08..49865424.13 rows=118471340 width=36) (actual time=1018575.485..1376888.584 rows=12211544 loops=1)
   ->  Sort  (cost=42164787.08..42587899.01 rows=169244770 width=36) (actual time=1018575.429..1182980.697 rows=160104057 loops=1)
         Sort Key: pec.id_site, eq.zone_usage, (date_trunc('hour'::text, (pec.date_ - '00:05:00'::interval)))
         Sort Method: external merge  Disk: 10324472kB
         ->  Hash Join  (cost=23688.77..9778075.78 rows=169244770 width=36) (actual time=8715.238..436374.256 rows=160104057 loops=1)
               Hash Cond: (pec.id_equipement = eq.id)
               ->  Seq Scan on point pec  (cost=0.00..5311711.80 rows=169244770 width=42) (actual time=0.024..311807.117 rows=160104057 loops=1)
                     Filter: (indicateur >= 0)
                     Rows Removed by Filter: 2985255
               ->  Hash  (cost=17716.12..17716.12 rows=477812 width=10) (actual time=8714.963..8714.963 rows=9323 loops=1)
                     Buckets: 65536  Batches: 1  Memory Usage: 421kB
                     ->  Seq Scan on equipement eq  (cost=0.00..17716.12 rows=477812 width=10) (actual time=0.020..8711.093 rows=9323 loops=1)
 Total runtime: 1379592.078 ms



schemaname             | public
tablename              | point
attname                | indicateur
inherited              | f
null_frac              | 0
avg_width              | 2
n_distinct             | 5
most_common_vals       | {0,1,2,-1,-2}
most_common_freqs      | {0.708667,0.2434,0.0293333,0.0184333,0.000166667}
histogram_bounds       |
correlation            | 0.681645
most_common_elems      |
most_common_elem_freqs |
elem_count_histogram   |


schemaname             | public
tablename              | point
attname                | id_equipement
inherited              | f
null_frac              | 0
avg_width              | 8
n_distinct             | 2432
most_common_vals       | {1387,4042,30,610,81,95,611,256,506,595,1288,1398,74,1182,231,471,591,32,72,505,590,2546,162,181,4041,73,232,654,788,31,100,129,1270,98,102,250,2505,35,101,130,1388,2115,77,245,255,1432,4019,273,1127,1273,1282,1390,1421,1592,1593,1674,2085,4017,93,126,209,246,249,272,285,365,395,1221,2106,2547,94,182,366,411,476,515,1582,2444,575,576,125,396,596,1220,1553,1583,105,208,284,346,520,1181,2551,2586,620,1435,2587,4090,265,410}
most_common_freqs      | {0.00243333,0.0024,0.0023,0.0023,0.00226667,0.00226667,0.00216667,0.00213333,0.00213333,0.0021,0.00206667,0.00206667,0.00203333,0.00203333,0.002,0.002,0.002,0.00196667,0.00196667,0.00196667,0.00196667,0.00196667,0.0019,0.0019,0.0019,0.00186667,0.00186667,0.00186667,0.00186667,0.00183333,0.00183333,0.00183333,0.00183333,0.0018,0.0018,0.0018,0.0018,0.00176667,0.00176667,0.00176667,0.00176667,0.00176667,0.00173333,0.00173333,0.00173333,0.00173333,0.00173333,0.0017,0.0017,0.0017,0.0017,0.0017,0.0017,0.0017,0.0017,0.0017,0.0017,0.0017,0.00166667,0.00166667,0.00166667,0.00166667,0.00166667,0.00166667,0.00166667,0.00166667,0.00166667,0.00166667,0.00166667,0.00166667,0.00163333,0.00163333,0.00163333,0.00163333,0.00163333,0.00163333,0.00163333,0.00163333,0.0016,0.0016,0.00156667,0.00156667,0.00156667,0.00156667,0.00156667,0.00156667,0.00153333,0.00153333,0.00153333,0.00153333,0.00153333,0.00153333,0.00153333,0.00153333,0.0015,0.0015,0.0015,0.0015,0.00146667,0.00146667}
histogram_bounds       | {9,49,116,203,335,375,470,653,1094,1265,1324,1433,1530,1608,1757,2103,2483,2613,2829,4001,4028,4050,4068,4106,4150,4209,4253,4312,4353,4382,4443,4514,4609,4679,4694,4714,4736,4750,4763,4776,4795,4822,4858,4887,4919,4971,5006,5035,5068,5106,5152,5224,5287,5340,5394,5469,5499,5530,5561,5597,5664,5706,5760,5816,5863,5937,6006,6039,6111,7787,9221,13689,16377,18771,21716,25325,30038,31777,36059,36657,42331,46399,53610,56959,60507,63859,72433,78750,82894,86880,91328,108088,110853,112707,120967,122677,122802,122912,123045,123229,132770}
correlation            | 0.624238
most_common_elems      |
most_common_elem_freqs |
elem_count_histogram   |

Pour la table point la colonne date_ a  139670 valeurs distinctes e la colonne correlation est de 0.953307

PS:
je ne suis pas celui qui a créé ces index

Merci bcp

Dernière modification par Postgres.0 (15/12/2014 20:21:52)

ruizsebastien

Membre

Re : Optimisation d'une requête PG 9.2

Bonjour,

Rapidement en passant 4 idées que vous pouvez tester :

- Le work_mem à 3 GB est trop élevé. Combien avez vous de connexion potentiellement simultanées à votre base ?

- il n'y a pas d'index sur la colonne pec.indicateur

- les statistiques sont-elles calculées ?

- peut être pourriez-vous tenter de faire la jointure avant le prédicat comme ceci :

SELECT pec.id_site,
    eq.zone_usage,
    date_trunc('hour'::text, pec.date_ - '00:05:00'::interval) AS date_,
    sum(pec.valeur_conso_hc) AS somme_valeur_conso_hc,
    sum(pec.valeur_conso_hp_base) AS somme_valeur_conso_hp_base,
    sum(pec.indicateur_index_courant) AS indicateur_index_courant,
    count(*) AS cnt,
    count(pec.valeur_conso_hc) AS cnt_valeur_conso_hc,
    count(pec.valeur_conso_hp_base) AS cnt_valeur_conso_hp_base,
    count(pec.indicateur_index_courant) AS cnt_indicateur_index_courant,
    count(pec.id_site) AS cnt_id_site,
    count(pec.date_consolidation) AS cnt_date_,
    count(eq.zone_usage) AS cnt_zone_usage
   FROM point pec
    inner join equipement eq on eq.id = pec.id_equipement
  WHERE pec.indicateur >= 0
  GROUP BY pec.id_site, eq.zone_usage, date_trunc('hour'::text, pec.date_ - '00:05:00'::interval);

Cordialement,

---

Cordialement,

Sébastien.

Postgres.0

Membre

Re : Optimisation d'une requête PG 9.2

Bonjour Sébastien,

pour cette requête, je n'ai qu'une seule connexion.
J'ai agrandi le work_mem à 3 GB car le fichier temporaire peut dépasser 1GB.
Il n'ya pas d'index sur la colonne indicateur.
Les statistiques sont calculées  une fois tous les soirs.
Normalement, postgres est capable de savoir si le plan d’exécution est meilleurs avec la jointure avant le predicat.
Je vais tester cela.

PS:

ne pensez vous pas qu'il y a des index en trop sur la table point?

ruizsebastien

Membre

Re : Optimisation d'une requête PG 9.2

pour les index en trop vous pouvez toujours voir avec cette requête :
SELECT * FROM pg_stat_all_indexes
WHERE idx_scan=0 AND schemaname NOT LIKE 'pg_%' order by 6 desc;

Au bout d'un certain temps d'activité si des index sont toujours avec idx_scan=0, il faut envisager des les supprimer.

---

Cordialement,

Sébastien.

gleu

Administrateur

Re : Optimisation d'une requête PG 9.2

Pour revenir à la question initiale, sur une requête de 23 minutes, 12 minutes sont passées sur le tri, et 5 minutes sont passés sur le parcours séquentiel de la table point. PostgreSQL n'a aucun moyen de faire autrement qu'un parcours séquentiel de cette table (seules 3 millions de lignes sont filtrées sur 160 millions de lignes, jamais PostgreSQL n'utilisera un index pour ce filtre).

Quant au tri, il est obligatoire pour faire le GROUP BY. PostgreSQL aurait pu passer par un hachage, mais ça revient au même. Le nombre de lignes est énorme. Les résultats intermédiaires du tri ou du hachage dépendent de la valeur du work_mem. Mais avec le nombre de lignes que vous avez, il est illusoire de croire que vous pouvez avoir une configuration du work_mem suffisante pour ça.

Les statistiques sur la table equipement sont très mauvaises, mais je ne crois pas que de meilleures statistiques auraient améliorées les choses.

À mon avis, le seul moyen d'améliorer ça, c'est de coller les fichiers temporaires sur un disque très rapide, ou mieux en mémoire.

---

Guillaume.

Postgres.0

Membre

Re : Optimisation d'une requête PG 9.2

Merci beaucoup!

Je reviens sur cette histoire d'index:

est-ce-qu'il n'est pas mieux que je remplace les index de la table point par:

"point_pkey" PRIMARY KEY, btree (date_, id_equipement, id_type_donnee, id_site)
    "idx_optim_elec_conso" UNIQUE, btree (id_equipement, date_)
    idx_site" btree (id_site)

J'ai l'impression qu'il y a de la redondance dans les index de cette table.   
On essaye aussi de gagner de l'espace en supprimant quelques index.

Dernière modification par Postgres.0 (16/12/2014 19:08:22)

rjuju

Administrateur

Re : Optimisation d'une requête PG 9.2

L'index  "point_cons_fk_equipement" est effectivement en trop, l'index "idx_optim_elec_conso" pouvant déjà être utilisé. Et comme l'a dit Guillaume, la majorité du temps de cette  requête est passé à faire le tri, celui-ci occupant presque 10 Go sur disque.

---

Julien.
https://rjuju.github.io/