Discussion :

[Sebajuste]

Bonjour,

Nous rencontrons un problème de performance sur une requête SQL de type "pour chaque type de valeur x retourne moi la plus proche inférieur d'une date y".
En effet, malgré l'utilisation d'un index qui me semble construit correctement, il semble que le moteur de requêtes ne puisse l'optimiser, et scan entièrement l'index (ou tout du moins, une partie conséquente).

Le contexte est le suivant: la base de données stock des valeurs d'appareils.
Les valeurs sont stockées dans la table T_DEVICE_VALUE
Les tables T_DEVICE et T_TYPE_VALUE ne sont pas fournies ici, car elle ne sont pas nécéssaires à la compréhension du problème. T_DEVICE contient la liste des appareils, et T_TYPE_VALUE la liste de tous les types de valeur (une liste de capteurs).

En production, la table T_DEVICE_VALUE contient près de 350 millions de valeurs, pour 400 type (T_TYPE_VALUE) de valeurs différents.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
CREATE TABLE T_DEVICE_VALUE (
  ID_DEVICE INTEGER NOT NULL,
  ID_TYPE INTEGER NOT NULL,
  VALUE_DATE TIMESTAMP NOT NULL,
  VAL CHARACTER VARYING(32) NOT NULL,
  PRIMARY KEY (ID_DEVICE, ID_TYPE, VALUE_DATE),
  FOREIGN KEY(ID_DEVICE) REFERENCES T_DEVICE(ID),
  FOREIGN KEY(ID_TYPE) REFERENCES T_TYPE_VALUE(ID)
);

La clef primaire de la table T_DEVICE_VALUE étant composite, celle-ci sert également d'index. L'ordre me semble correct pour permettre des requètes "sargable".



La requête initiale doit permettre de rechercher, pour une installation définie, le dernier état de toutes les valeurs à une date données.

Nous utilisons pour cela deux requêtes imbriquées. La première récupère dans l'index la liste des résultats formant les clef primaires (les couples ID_DEVICE, ID_TYPE, VALUE_DATE correspondant aux résultats souhautés), la seconde va récupérer dans la table la valeur associée pour chaque clef primaire.

Nous rencontrons des problèmes pour la première requête, celle devant récupérer toutes les clef primaires correspondant au résultat voulu.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT
	MAX( VALUE_DATE ), ID_DEVICE, ID_TYPE
FROM T_DEVICE_VALUE
WHERE ID_DEVICE = 1
	AND VALUE_DATE <= '2015-01-10 07:00:00'
GROUP BY ID_DEVICE, ID_TYPE

Voici le résulat de l'explain analyse:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
HashAggregate  (cost=832107.83..832107.84 rows=1 width=12) (actual time=58062.328..58062.392 rows=266 loops=1)
  ->  Bitmap Heap Scan on t_device_value  (cost=7760.43..830607.30 rows=300105 width=12) (actual time=673.857..57611.936 rows=991766 loops=1)
        Recheck Cond: ((id_device = 584) AND (value_date <= '2015-01-10 07:00:00'::timestamp without time zone))
        ->  Bitmap Index Scan on t_device_value_pkey  (cost=0.00..7685.40 rows=300105 width=0) (actual time=589.813..589.813 rows=992466 loops=1)
              Index Cond: ((id_device = 584) AND (value_date <= '2015-01-10 07:00:00'::timestamp without time zone))
Total runtime: 58063.747 ms

J'interprète le résultat ainsi: même si la requête ne cherche que dans l'index, celui-ci est entièrement scanné. Ou tout du moins, puisque c'est un index Btree, toutes les valeurs de la profondeur de (id_type, value_date).
Par contre, je ne comprends pas pourquoi il fait la requête en deux temps, avec une revérification des contraintes de sélection.




Pour y voir plus clair, j'ai donc simplifié la requète pour rechercher un type précis:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT MAX( VALUE_DATE )
	FROM T_DEVICE_VALUE
WHERE ID_DEVICE = 1
	AND ID_TYPE = 1
	AND VALUE_DATE <= '2015-01-10 07:00:00'

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Result  (cost=3.65..3.66 rows=1 width=0) (actual time=0.109..0.109 rows=1 loops=1)
  InitPlan 1 (returns $0)
    ->  Limit  (cost=0.00..3.65 rows=1 width=8) (actual time=0.106..0.106 rows=1 loops=1)
          ->  Index Only Scan Backward using t_device_value_pkey on t_device_value  (cost=0.00..623.59 rows=171 width=8) (actual time=0.103..0.103 rows=1 loops=1)
                Index Cond: ((id_device = 584) AND (id_type = 50) AND (value_date IS NOT NULL) AND (value_date <= '2015-01-10 07:00:00'::timestamp without time zone))
                Heap Fetches: 1
Total runtime: 0.165 ms

Cette fois, la requête est capable de rechercher correctement l'information, sans scan complet de l'index. On remarque l'utilisation du scan de l'index en "Backward".
Le temps de réponse me semble tout à fait correct pour une recherche aussi simple.






La même requête, qui retourne le même résultat, à la différence qu'un GROUP BY est ajouté:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT MAX( VALUE_DATE )
	FROM T_DEVICE_VALUE
WHERE ID_DEVICE = 1
	AND ID_TYPE = 1
	AND VALUE_DATE <= '2015-01-10 07:00:00'
GROUP BY ID_TYPE

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
GroupAggregate  (cost=0.00..624.03 rows=1 width=12) (actual time=18.165..18.165 rows=0 loops=1)
  ->  Index Only Scan using t_device_value_pkey on t_device_value  (cost=0.00..623.16 rows=171 width=12) (actual time=18.161..18.161 rows=0 loops=1)
        Index Cond: ((id_device = 584) AND (id_type = 50) AND (value_date <= '2015-01-10 07:00:00'::timestamp without time zone))
        Heap Fetches: 0
Total runtime: 18.235 ms

Et là le temps de recherche explose de nouveau... Alors même que logiquement, le GROUP BY ne devrait avoir aucun effet.





Je manque totalement de connaissances et d'expérience pour trouver une solution à ce problème.
Y a t'il un moyen d'améliorer la première requête présentée ici, afin d'avoir des performances decentes ?

Autrement, j'ai comme seule solution d'exécuter, coté application, 400 fois la deuxième requête pour réaliser manuellement le group by dans des performances acceptables. 400 * 0.165 ~= 66 ms
Même avec les allés retour SQL <=> application, ça sera toujours plus rapide. Mais qu'est ce que c'est moche...

[Waldar]

Je n'arrive pas à reproduire le comportement sur SQLFiddle :
http://sqlfiddle.com/#!12/e4fcb/2/0

Les données :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE TABLE T_DEVICE_VALUE (
  ID_DEVICE INTEGER NOT NULL,
  ID_TYPE INTEGER NOT NULL,
  VALUE_DATE TIMESTAMP NOT NULL,
  VAL CHARACTER VARYING(32) NOT NULL,
  PRIMARY KEY (ID_DEVICE, ID_TYPE, VALUE_DATE)
);
 
insert into T_DEVICE_VALUE values
(1, 1, '2015-01-10 05:00:00'::timestamp without time zone, 'A1'),
(1, 1, '2015-01-10 06:00:00'::timestamp without time zone, 'A2'),
(1, 1, '2015-01-10 07:00:00'::timestamp without time zone, 'A3'),
(1, 1, '2015-01-10 08:00:00'::timestamp without time zone, 'A4'),
(1, 2, '2015-01-10 05:00:00'::timestamp without time zone, 'B1'),
(1, 2, '2015-01-10 06:00:00'::timestamp without time zone, 'B2'),
(1, 2, '2015-01-10 07:00:00'::timestamp without time zone, 'B3'),
(1, 2, '2015-01-10 08:00:00'::timestamp without time zone, 'B4'),
(2, 1, '2015-01-10 05:00:00'::timestamp without time zone, 'C1'),
(2, 1, '2015-01-10 06:00:00'::timestamp without time zone, 'C2'),
(2, 1, '2015-01-10 07:00:00'::timestamp without time zone, 'C3'),
(2, 1, '2015-01-10 08:00:00'::timestamp without time zone, 'C4'),
(2, 2, '2015-01-10 05:00:00'::timestamp without time zone, 'D1'),
(2, 2, '2015-01-10 06:00:00'::timestamp without time zone, 'D2'),
(2, 2, '2015-01-10 07:00:00'::timestamp without time zone, 'D3'),
(2, 2, '2015-01-10 08:00:00'::timestamp without time zone, 'D4');

La requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
explain
SELECT
	MAX( VALUE_DATE ), ID_DEVICE, ID_TYPE
FROM T_DEVICE_VALUE
WHERE ID_DEVICE = 1
	AND VALUE_DATE <= '2015-01-10 07:00:00'::timestamp without time zone
GROUP BY ID_DEVICE, ID_TYPE;
 
GroupAggregate (cost=0.00..8.31 rows=1 width=16)
-> Index Only Scan using t_device_value_pkey on t_device_value (cost=0.00..8.29 rows=1 width=16)
Index Cond: ((id_device = 1) AND (value_date <= '2015-01-10 07:00:00'::timestamp without time zone))

Dans le premier plan, on voit bien que PG lit l'index puis va lire la table, ce qui est vraiment étonnant car il a déjà toutes les données nécessaires dans l'index.
Vous êtes sûr qu'il ne manque pas un VAL dans le select, quelque part ?

Pouvez-vous vérifier le script de l'index adossé à la clef primaire ?


Sinon, avez-vous essayé la clause DISTINCT ON spécifique à PostgreSQL ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
  select distinct on (ID_DEVICE, ID_TYPE)
         VALUE_DATE, ID_DEVICE, ID_TYPE, VAL
    from T_DEVICE_VALUE
   WHERE ID_DEVICE = 1
     AND VALUE_DATE <= '2015-01-10 07:00:00'::timestamp without time zone
order by ID_DEVICE, ID_TYPE, VALUE_DATE desc;

[Sebajuste]

Merci de votre réponse.

Je n'ai pas pensé à comparer les résultats des EXPLAIN avec SQLFiddle, et effectivement les résultats sont différents ! J'ai bien revérifié, et je confirme que seules les colonnes de l'index sont présentes dans le SELECT.

L'explication serait un défaut d'optimisation sur les statistiques de la table.
En effet, l'explain indique une double lecture, de l'index, puis de la table pour une recherche sur le ID_TYPE = 10.
Mais il fait une lecture de l'index seul pour ID_TYPE = 50.

Évidemment, la répartition de mes données n’est pas homogène. Par exemple, pour le device avec lequel je viens de réaliser ce test, j'ai 2 millions de lignes pour ID_TYPE = 10, et seulement 50 pour ID_TYPE = 50.

Même après l'exécution d'un ANALYSE sur la table, il n'y a pas de changement de comportement.
Je vais tenter un VACUUM FULL sur la table.

Voici la requête de la création de l'index:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CREATE UNIQUE INDEX t_device_value_pkey ON t_device_value USING btree (id_device, id_type, value_date)

Quant à DISTINCT ON, je ne connaissais pas. Même après avoir lu la doc, je ne comprends pas trop la différence avec un DISTINCT "classique" associé à un GROUP BY.
Apparemment, ça ne s'utilise pas avec la fonction d’agrégat MAX (en tout cas, je n'y arrive pas), qui est indispensable dans mon cas pour ne pas retourner en résultat des millions de lignes.

[SQLpro]

L'explication serait un défaut d'optimisation sur les statistiques de la table.

Oui, c'est évident.... Lorsque vous créez un index multicolonne, ce qui est le cas de votre PK, les statistiques collectées par PostGreSQL ne concernent que la première colonne. Il n'est pas possible d'avoir des statistiques portant sur la 1ere + la 2e colonne ou le vecteur des 3 colonnes (1 +2 + 3).

Certains SGBDR pour pallier ce problème propose des statistiques de colonnes indépendante des statistiques d'index. C'est le cas de SQL Server... Et ceci permet de contourner ce défaut de stats sur la mutualisation des colonnes de la clef d'index.

Seule solution pour PostGreSQL, faire des PK composées d'une seule colonne et rajouter divers index pour satisfaire chaque requête....

Autres solutions :

• voir s'il est possible de faire un index sur fonction en calculant un hachage sur l'ensemble des valeurs de la clef d'index et utiliser ce hachage dans la requête
• utiliser les vues matérialisées... mais c'est pas encore très au point chez PG !

Sinon, compte tenu de votre volumétrie... passer à un SGBDR plus costaud ! par exemple avec une vue indexée (synchrone) chez MS SQL Server votre résultat serait absolument instantané...

A +

[Sebajuste]

Ceci explique donc cela.

Merci d'avoir pris le temps de vous pencher sur mon problème.

Je vais étudier les différentes propositions pour essayer de trouver un compromis acceptable.

[aieeeuuuuu]

Bonjour,

La même requête, qui retourne le même résultat, à la différence qu'un GROUP BY est ajouté:

Alors ce n'est plus la même requête !
D'ailleurs, elle ne renvoient pas forcément le même résultat. Si vous spécifiez un ID_DEVICE ou un ID_TYPE qui n'existe pas, l'une renverra une ligne avec NULL, l'autre ne renverra aucune ligne.
Cela dit, l'optimiseur semble quand même montrer dans votre cas quelque faiblesses.

Je n'arrive pas à reproduire le comportement sur SQLFiddle :

En effet, j'ai eu le même problème. Mais avec une volumétrie plus importante, le problème survient.

Pour en revenir au problème initial, il serait intéressant de tester d'y répondre par l'utilisation de fonctions fenêtrées, cela éviterait la sous requete.
Mais de la même manière, je ne suis pas certain que PG arrive a tirer le meilleur parti des indexs dans ce cas...

[Sebajuste]

Un dernier petit message pour faire part de la solution "la moins pire" que nous avons élue cet après-midi : Les fonctions de postgresql.

La fonction exécute une première requête sur la table T_TYPE_VALUE pour récupérer tous les types possibles. Elle boucle ensuite sur tous les type retournés pour exécuter la requète de selection des clef primaire. Comme il n'y a pas de GROUP BY, celle-ci est quasiement instantannée ( ~0.5 ms). Une petite jointure est faite pour aller récupérer la valeur associée à la PK, dans la table.

L'ensemble des résultats est agrégé par un RETURN NEXT;

Alors certes, de nombreuses requêtes SQL sont exécutées au lieu d'une seule mais :
1) Il n'y a qu'un seul appel entre l'application et PostgreSQL
2) L'ensemble de la fonction s’exécute en quelques dizaines de millisecondes seulement ! Pour l'utilisateur final, la fonctionnalité sera instantanée.