Forums PostgreSQL.fr

mentat

Membre

Optimisation requête (encore)

Bonjour.

J'ai trois tables pour mes tarifs :
Table: prix_nets_sap (+7 millions de lignes)

CREATE TABLE prix_nets_sap
(
  devise character varying(5),
  date_prix_1 date,
  tarif_1 double precision,
  source_tarif_1 character varying(4),
  condition_1 double precision,
  type_condition_1 character varying(5),
  prix_1 double precision,
  date_prix_2 date,
  tarif_2 double precision,
  source_tarif_2 character varying(4),
  condition_2 double precision,
  type_condition_2 character varying(5),
  prix_2 double precision,
  prsu double precision,
  qte double precision,
  montant_1 double precision,
  montant_2 double precision,
  montant_prsu double precision,
  hausse double precision,
  marge double precision,
  qte_groupe_clients numeric,
  stats_debut text,
  stats_fin text,
  type_condition_2_libelle text,
  type_condition_1_libelle text,
  marge_unitaire numeric,
  article_id integer,
  client_id integer
)
WITH (
  OIDS=FALSE
);

CREATE INDEX prix_nets_sap_idx_article_id
  ON prix_nets_sap
  USING btree
  (article_id);

CREATE INDEX prix_nets_sap_idx_client_id
  ON prix_nets_sap
  USING btree
  (client_id);

Table: articles_canal (+30000 lignes)
CREATE TABLE articles_canal
(
  organisation_commerciale text NOT NULL,
  canal text NOT NULL,
  code text NOT NULL,
  designation text,
  type_articles text,
  type_libelle text,
  groupe_articles text,
  groupe_articles_libelle text,
  article_id integer,
  CONSTRAINT articles_canal_code PRIMARY KEY (organisation_commerciale, canal, code)
)
WITH (
  OIDS=FALSE
);

CREATE INDEX articles_canal_idx_id
  ON articles_canal
  USING btree
  (article_id);

CREATE INDEX articles_canal_idx_type_articles
  ON articles_canal
  USING hash
  (type_articles);

Table: clients (+10000 lignes)

CREATE TABLE clients
(
  code character varying(10) NOT NULL,
  libelle character varying(35),
  organisation_commerciale text,
  canal text NOT NULL,
  societe text NOT NULL DEFAULT 2100,
  client_id integer,
  CONSTRAINT clients_pk PRIMARY KEY (societe, canal, code)
)
WITH (
  OIDS=FALSE
);

CREATE INDEX client_idx_canal
  ON clients
  USING hash
  (canal);

CREATE INDEX clients_idx_id
  ON clients
  USING btree
  (client_id);

Quand je lance le query :
SELECT c.canal, c.code AS client, c.libelle AS client_libelle,
  a.code AS produit, a.designation AS produit_libelle,
  p.*
   FROM prix_nets_sap p, articles_canal a, clients c
  WHERE p.article_id = a.article_id AND p.client_id = c.client_id
  and a.type_articles='ZFER' and c.canal='23'

J'obtiens :
Hash Join  (cost=18138.64..449404.38 rows=1741506 width=299) (actual time=124352.620..313052.105 rows=197539 loops=1)
  Hash Cond: (p.article_id = a.article_id)
  ->  Hash Join  (cost=1621.77..393703.61 rows=1741506 width=257) (actual time=124298.204..312206.315 rows=527043 loops=1)
        Hash Cond: (p.client_id = c.client_id)
        ->  Seq Scan on prix_nets_sap p  (cost=0.00..345768.39 rows=7706239 width=226) (actual time=0.011..309203.834 rows=7706239 loops=1)
        ->  Hash  (cost=1614.68..1614.68 rows=567 width=35) (actual time=1.691..1.691 rows=567 loops=1)
              ->  Bitmap Heap Scan on clients c  (cost=72.66..1614.68 rows=567 width=35) (actual time=0.210..1.377 rows=567 loops=1)
                    Recheck Cond: (canal = '23'::text)
                    ->  Bitmap Index Scan on clients_idx_code  (cost=0.00..72.52 rows=567 width=0) (actual time=0.171..0.171 rows=567 loops=1)
                          Index Cond: (canal = '23'::text)
  ->  Hash  (cost=16414.95..16414.95 rows=8154 width=46) (actual time=54.365..54.365 rows=8108 loops=1)
        ->  Bitmap Heap Scan on articles_canal a  (cost=1551.51..16414.95 rows=8154 width=46) (actual time=2.269..49.253 rows=8108 loops=1)
              Recheck Cond: (type_articles = 'ZFER'::text)
              ->  Bitmap Index Scan on articles_canal_idx_type_articles  (cost=0.00..1549.47 rows=8154 width=0) (actual time=1.596..1.596 rows=8108 loops=1)
                    Index Cond: (type_articles = 'ZFER'::text)
Total runtime: 313101.246 ms

Et comme à chaque fois que je regarde un explain query, c'est une tannée à comprendre ce qu'il faut faire pour améliorer les choses.
Je vois un Seq scan sur la plus grosse table, alors que j'ai deux index (integer) sur les articles et les clients.

Bref, si quelqu'un peut m'aider à traduire le klingon...
Merci d'avance

Marc Cousin

Membre

Re : Optimisation requête (encore)

Pour commencer, si vous posez des questions sur un plan d'exécution, c'est préférable d'indiquer votre version précise de PostgreSQL (l'optimiseur évolue entre chaque version).

PostgreSQL estime probablement (je ne sais pas quelle est votre version) que partir de a ou de c et faire un nested loop sur p est une très mauvaise idée. Ce qui est probablement vrai… il y a combien de prix associés au type d'article ZFER, et combien de prix associés au canal 23 ? Il est probable que PostgreSQL considère que passer par les index de la table de prix ne serait pas efficace.

Vous pouvez essayer de lui forcer la main en lui faisant croire que les parcours d'index sont moins chers (en mettant des valeurs très faibles à random_page_cost et seq_page_cost, comme 0.01). Mais bon, ce sont évidemment des valeurs fantaisistes. Par ailleurs, si vous voulez vraiment une optimisation de type star query, on peut «forcer la main» à PostgreSQL, au moins en 9.2. Pour ça, il faut diminuer comme indiqué au dessus les random_page_cost et seq_page_cost, et réécrire de la sorte:

explain analyze select * from (dim1 cross join dim2) left join facts on (dim1.a=facts.a and dim2.b=facts.b) where …

Il faut bien sûr un index multicolonnes sur facts pour arriver à ses fins. J'ai ce plan là:

---------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join  (cost=0.00..79837.89 rows=249500 width=24) (actual time=0.095..441.171 rows=375492 loops=1)
   ->  Nested Loop  (cost=0.00..3370.90 rows=249500 width=16) (actual time=0.046..53.769 rows=248502 loops=1)
         ->  Seq Scan on dim1  (cost=0.00..125.45 rows=500 width=8) (actual time=0.025..1.027 rows=499 loops=1)
               Filter: (datadim1 < 500)
               Rows Removed by Filter: 9501
         ->  Materialize  (cost=0.00..127.95 rows=499 width=8) (actual time=0.000..0.042 rows=498 loops=499)
               ->  Seq Scan on dim2  (cost=0.00..125.45 rows=499 width=8) (actual time=0.014..3.191 rows=498 loops=1)
                     Filter: (datadim2 < 500)
                     Rows Removed by Filter: 9502
   ->  Index Only Scan using idxfactab on facts  (cost=0.00..0.30 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=248502)
         Index Cond: ((a = dim1.a) AND (b = dim2.b))
         Heap Fetches: 127488
Total runtime: 456.319 ms

---

Marc.

mentat

Membre

Re : Optimisation requête (encore)

> c'est préférable d'indiquer votre version précise de PostgreSQL

Oui, pardon. J'y avais pensé, puis j'ai oublié...
Postgresql 8.4 sous windows server 2003.

> il y a combien de prix associés au type d'article ZFER, et combien de prix associés au canal 23

Prix avec ZFER: 2,8 millions
Prix avec canal 23: 500000
Au final : 200000 lignes

Et question qui n'a rien à voir, on fait comment pour insérer des sauts de lignes dans le message ?
[Modif]: Apparemment deux sauts de lignes consécutifs...

Dernière modification par mentat (07/02/2013 16:56:10)

Marc Cousin

Membre

Re : Optimisation requête (encore)

Ok, donc 200 000 lignes, par scan d'index, ça peut être très douloureux. Par ailleurs, je ne suis pas sûr que PostgreSQL 8.4 sache faire ce que j'ai fait avec la 9.2 juste au-dessus.

Autre chose qui m'étonne c'est le temps de scan de la table p. Elle fait quelle taille ? (je dirais entre 1,5 et 2Go vu le plan, mais on peut avoir des surprises). Et ça tourne sur quel genre de machine ?

---

Marc.

mentat

Membre

Re : Optimisation requête (encore)

> Elle fait quelle taille ? (je dirais entre 1,5 et 2Go vu le plan...

2,4 GO

> Et ça tourne sur quel genre de machine ?

Windows 2003, un serveur de 5 ans d'age. Processeurs Xeon E5420 à 2,5GHz (8 coeurs) et 16 GO de ram. Avec autant de mémoire, la meilleure optimisation des paramètres du serveur serait quoi ?

J'ai aussi un autre serveur linux sur lequel Postgres 8.4 tourne bien plus vite. Mais j'ai un problème d'onduleur à régler avant de faire une migration.

gleu

Administrateur

Re : Optimisation requête (encore)

Le problème est que vous ne pouvez pas donner beaucoup de mémoire au cache disque de PostgreSQL sous Windows. Mieux vaut passer sous Linux pour avoir des performances importantes.

---

Guillaume.

Marc Cousin

Membre

Re : Optimisation requête (encore)

C'est vrai. Mais un scan séquentiel d'une table de 2,5Go qui dure 300 secondes (même s'il y a deux hash join qui sont faits sur son résultat en même temps), grosso modo ça fait du 10Mo/s. C'est vraiment faible. Ça donne l'impression qu'il y a un problème sur la machine.

---

Marc.

SQLpro

Membre

Re : Optimisation requête (encore)

> Elle fait quelle taille ? (je dirais entre 1,5 et 2Go vu le plan...
2,4 GO
> Et ça tourne sur quel genre de machine ?
Windows 2003, un serveur de 5 ans d'age. Processeurs Xeon E5420 à 2,5GHz (8 coeurs) et 16 GO de ram. Avec autant de mémoire, la meilleure optimisation des paramètres du serveur serait quoi ?

32 ou 64 bits ?

Si c'est du 32 :
1) vous ne pouvez pas utiliser plus de 4 Go donc 2 sont automatiquement affectés à l'OS.
2) il est possible de faire de la pagination en mémoire haute pour utiliser les 14 Go supplamentaire, mais je ne crois pas que PostGreSQL le supporte (AWE).

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

SQLpro

Membre

Re : Optimisation requête (encore)

Bonjour.

J'ai trois tables pour mes tarifs :
Table: prix_nets_sap (+7 millions de lignes)

[...]

Et comme à chaque fois que je regarde un explain query, c'est une tannée à comprendre ce qu'il faut faire pour améliorer les choses.
Je vois un Seq scan sur la plus grosse table, alors que j'ai deux index (integer) sur les articles et les clients.

Votre table est obèse et reflète une mauvaise modélisation. En effet, les colonnes se terminant par 1 ou 2 portant le même nom, indique que vous avez dédoublé le nombre de colonne, donc multiplié le volume de données à lire par 2 !
Il aurait fallu simplement rajouter une seule colonne avec la valeur 1 ou 2 pour obtenir le même effet avec des lignes deux fois moins longues, donc un scan déjà deux fois plus rapide. Et si en plus certaines colonnes sont NULL, vous auriez évité de stocker du NULL (le NULL coute cher à stocker !).
En sus il est beaucoup plus facile d'indexer une table avec peu de colonne, qu'une table obèse puisque le nombre d'index potentiel est une factorielle du nombre de colonnes !
À me lire : http://blog.developpez.com/sqlpro/p1007 … es_petites
En sus, avez-vous besoin de toutes les colonnes de P, comme l'indique le p.* du SELECT ?

A +

Dernière modification par SQLpro (12/03/2013 23:22:18)

---

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