[Gros volume] Optimisations ? [Résolu]

[Grubshka]

Bonjour,

J'ai une table qui va contenir énormément de tuples, et je voudrais connaître les optimisations possibles pour ce type de table.

J'ai trouvé quelques options à modifier dans postgresql.conf sur techdocs.postgresql.org, mais il n'y a rien de très précis, et j'aurais aussi aimé avoir vos expériences en la matière.

Par exemple, avec 1 000 000 de tuples, une requête simple prenait 7 secondes, mais avec 10 000 000, cela prend 40 secondes.

La table est amenée à contenir beaucoup plus de données que ça, et j'ai besoin d'améliorer le temps de réponse.

En dehors des paramètres modifiés, j'ai fais les index nécessaires (sur deux champs que j'utilise le plus dans mes clauses where), et des vacuum analyze réguliers.

Merci de vos conseils.

[Quentin]

Bonjour,

Il serait intéressant de faire une analyse de plan des requêtes utilisées le plus fréquemment pour accéder à cette table, avec EXPLAIN. Cela permettrait de voir si les index sont correctement utilisés, entre autres.

[Grubshka]

En fait la table en question est simple (hé oui je fais des bons schémas ) :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
projetweb=# \d echantillon
              TABLE "public.echantillon"
   COLUMN    |            Type             | Modifiers
-------------+-----------------------------+-----------
 time        | timestamp without time zone | NOT NULL
 idparameter | integer                     | NOT NULL
 idsession   | integer                     | NOT NULL
 ipv         | integer                     |
 rpv         | real                        |
 spv         | character varying           |
Indexes:
    "echantillon_pkey" PRIMARY KEY, btree ("time", idparameter, idsession)
Foreign-KEY constraints:
    "echantillon_idparameter_fkey" FOREIGN KEY (idparameter, idsession) REFERENCES analysis(idparameter, idsession)

Voilà le type de requête le plus souvent appelée :

Code :

1
2
3
4
5
6
7
 
projetweb=# explain  select * from echantillon where time between TIMESTAMP '15/07/2004 15:00' and TIMESTAMP '15/07/2004 20:00' and idparameter = 156 and idsession = 157;
                                                     QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 INDEX Scan USING echantillon_pkey ON echantillon  (cost=0.00..3.77 rows=1 width=28)
   INDEX Cond: (("time" >= '2004-07-15 15:00:00'::timestamp without time zone) AND ("time" <= '2004-07-15 20:00:00'::timestamp without time zone) AND (idparameter = 156) AND (idsession = 157))
(2 rows)

Il s'agit en fait de télémesures (états, températures, tensions, en bcp d'autres) prises lors de tests, le but est d'afficher des courbes sur un site intranet.
Je pensais générer des courbes "miniatures" à l'avance, mais parfois il y aura un besoin de voir la courbe précisément, de faire des zoom, de comparer des paramètres, d'appliquer des fonctions à ces courbes, etc.

Merci

[Quentin]

Hum, a priori, le plan est correct, il passe bien par l'index de la clé primaire... Cette requête prend combien de temps avec un EXPLAIN EXECUTE ?

Une remarque, toutefois : l'utilisation d'un timestamp dans une clé primaire est assez coûteux en traitement, il est souvent plus efficace de le sortir de la clé primaire et de le remplacer par un integer.

Sinon, côté optimisation, il existe les petits papiers de SQLPro : Optimisation SGBDR

[Grubshka]

Un explain execute prend environ 5 secondes... Je connaissais pas cette commande
Je viens de me rendre compte que c'est php qui galère avec le nombre de résultats !! Je vais faire avec autre chose...

Il ya vraiment beaucoup de mesures par paramètre et par session (parfois 100 par seconde pour certains paramètres, sur une session de plusieurs heures), mettre le timestamp en clé primaire était le plus simple à mon avis, surtout que les requêtes se font toujours sur le timestamp (entre autres) et que cette clé n'est jamais utilisée en clé étrangère d'une autre table.

Je vais quand même faire des tests avec un integer en clé primaire...

Merci beaucoup de ton aide.

[Quentin]

Je t'en prie

Si tu considères le "problème" comme résolu, pense à cliquer sur le bouton du même nom en bas en gauche de la page.

[Grubshka]

Envoyé par Quentin

Je t'en prie

Si tu considères le "problème" comme résolu, pense à cliquer sur le bouton du même nom en bas en gauche de la page.

Merci, j'oublie souvent !