utilisation non pertinente d'un index au lieu d'un autre

jacques · 17/10/2013 19:03:40

Bonjour,

j'ai un comportement que je ne comprends pas.
Sous PostgreSQL 8.4.5,
J'ai 2 tables TOTO_V1 et TOTO_V2 qui ont les caractéristiques suivantes :

TOTO_V1 a pour taille avec les index (une clef primaire sur (DAT, NUM_MAILLE) et un index sur NUM_MAILLE) :
34 Go (dont 23 pour la table seule)
Le nombre de lignes :
199.482.072

TOTO_V2 a pour taille avec les index (une clef primaire sur (DAT, NUM_MAILLE) et un index sur NUM_MAILLE) :
39 Go (dont 29 pour la table seule)
Le nombre de lignes :
199.333.692

En fait, les tables diffèrent essentiellement par la taille des lignes, la table TOTO_V2 comporte des colonnes supplémentaires à TOTO_V1

Je cherche à comprendre pourquoi j'ai le comportement suivant sur TOTO_V2 :
Remarque préliminaire : j'ai fait un " ANALYZE TOTO_V2 "

bdcp=> explain analyze select to_char(dat,'YYYYMMDDHH24MISS'),SWI from TOTO_V2 where dat between to_timestamp('20080501000000','YYYYMMDDHH24MISS') and to_timestamp('20080531000000','YYYYMMDDHH24MISS') and num_maille=34 order by dat, num_maille;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Sort (cost=5250.53..5250.63 rows=38 width=14) (actual time=52.938..52.948 rows=31 loops=1)
Sort Key: dat
Sort Method: quicksort Memory: 27kB
-> Index Scan using i_toto_v2_num_maille on toto_v2 (cost=0.00..5249.54 rows=38 width=14) (actual time=44.844..52.900 rows=31 loops=1)
Index Cond: (num_maille = 34)
Filter: ((dat >= to_timestamp('20080501000000'::text, 'YYYYMMDDHH24MISS'::text)) AND (dat <= to_timestamp('20080531000000'::text, 'YYYYMMDDHH24MISS'
::text)))

=> ce qui me donne un temps de traitement de l'ordre de 30 s (PostgreSQL n'utilise que l'index sur NUM_MAILLE i_toto_v2_num_maille) !
=> 100 fois plus long qu'avec TOTO_V1

alors qu'avec TOTO_V1, PostgreSQL utilise bien l'index de la clef primaire toto_v1_pkey :
bdcp=> explain analyze select to_char(dat,'YYYYMMDDHH24MISS'),SWI from TOTO_V1 where dat between to_timestamp('20080501000000','YYYYMMDDHH24MISS') and to_timestamp('20080531000000','YYYYMMDDHH24MISS') and num_maille=34 order by dat, num_maille;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Index Scan using toto_v1_pkey on toto_v1 (cost=0.01..5972.57 rows=28 width=14) (actual time=65.045..368.505 rows=31 loops=1)
Index Cond: ((dat >= to_timestamp('20080501000000'::text, 'YYYYMMDDHH24MISS'::text)) AND (dat <= to_timestamp('20080531000000'::text, 'YYYYMMDDH
H24MISS'::text)) AND (num_maille = 34))

=> ce qui me donne un temps de traitement bien inférieur à 1 s (PostgreSQL n'utilise que l'index sur NUM_MAILLE ) !

Remarque : avec TOTO_V2, je retrouve le comportement souhaité (utilisation de l'index de la clé primaire toto_v2_pkey) si je demande plusieurs NUM_MAILLE :

bdcp=> explain analyze select to_char(dat,'YYYYMMDDHH24MISS'),SWI from toto_v2 where dat between to_timestamp('20080501000000','YYYYMMDDHH24MISS') and to_timestamp('20080531000000','YYYYMMDDHH24MISS') and num_maille in (34,35) order by dat, num_maille;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Sort (cost=5905.17..5905.32 rows=59 width=14) (actual time=190.915..190.931 rows=62 loops=1)
Sort Key: dat, num_maille
Sort Method: quicksort Memory: 29kB
-> Bitmap Heap Scan on toto_v2 (cost=5784.19..5903.43 rows=59 width=14) (actual time=173.615..190.837 rows=62 loops=1)
Recheck Cond: ((num_maille = ANY ('{34,35}'::integer[])) AND (dat >= to_timestamp('20080501000000'::text, 'YYYYMMDDHH24MISS'::text)) AND (
dat <= to_timestamp('20080531000000'::text, 'YYYYMMDDHH24MISS'::text)))
-> BitmapAnd (cost=5784.19..5784.19 rows=59 width=0) (actual time=173.534..173.534 rows=0 loops=1)
-> Bitmap Index Scan on i_toto_v2_num_maille (cost=0.00..528.63 rows=39627 width=0) (actual time=27.307..27.307 rows=40302 loops=
1)
Index Cond: (num_maille = ANY ('{34,35}'::integer[]))
-> Bitmap Index Scan on toto_v2_pkey (cost=0.00..5255.29 rows=295968 width=0) (actual time=141.559..141.559 rows=306652 loops=1)
Index Cond: ((dat >= to_timestamp('20080501000000'::text, 'YYYYMMDDHH24MISS'::text)) AND (dat <= to_timestamp('20080531000000'
::text, 'YYYYMMDDHH24MISS'::text)))

Comment faire en sorte que la première requête SQL sur TOTO_V2 utilise l'index toto_v2_pkey et non pas i_toto_v2_num_maille ?
Comment expliquer la différence de comportement entre TOTO_V1 et TOTO_V2 ?

Merci par avance pour vos éventuelles explications.

jacques · 17/10/2013 19:58:08

Comportement identique sur une version PostgreSQL 9.2.4 :

bdcp=> explain analyze select to_char(dat,'YYYYMMDDHH24MISS')::bigint,SWI from TOTO_V1 where num_maille=1234
and (dat between to_date('20080501','YYYYMMDD') and to_date('20080531','YYYYMMDD')) order by dat, num_maille;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using toto_v1_pkey on toto_v1 (cost=0.01..6428.06 rows=31 width=14) (actual time=0.066..35.801 rows=31 loops=1)
Index Cond: ((dat >= to_date('20080501'::text, 'YYYYMMDD'::text)) AND (dat <= to_date('20080531'::text, 'YYYYMMDD'::text)) AND (num_maille = 1234))

bdcp=> explain analyze select to_char(dat,'YYYYMMDDHH24MISS')::bigint,SWI from TOTO_V2 where num_maille=1234
and (dat between to_date('20080501','YYYYMMDD') and to_date('20080531','YYYYMMDD')) order by dat, num_maille;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=1829.20..1829.27 rows=26 width=14) (actual time=75.792..75.793 rows=31 loops=1)
Sort Key: dat
Sort Method: quicksort Memory: 27kB
-> Index Scan using i_toto_v2_num_maille on toto_v2 (cost=0.00..1828.59 rows=26 width=14) (actual time=67.444..75.729 rows=31 loops=1)
Index Cond: (num_maille = 1234)
Filter: ((dat >= to_date('20080501'::text, 'YYYYMMDD'::text)) AND (dat <= to_date('20080531'::text, 'YYYYMMDD'::text)))
Rows Removed by Filter: 20120

rjuju · 17/10/2013 20:13:37

Bonjour,

d'après votre explain analyze, la 1ère requête dure 53 ms, pas 30 secondes, contre 368 ms pour la seconde, ce qui est plus performant.

N'avez-vous pas eu des soucis de verrouillage sur la table plutôt ?

jacques · 18/10/2013 07:42:34

Bonjour,

Non, je n'ai a priori pas de verrou sur la table puisqu'elle répond toutefois et rapidement lorsque j'enrichis le prédicat (si je passe par exemple de " num_maille = 1234 " à " num_maille = 1234 or num_maille = 1235 ").
Par ailleurs, ma table est récente et n'est accédée que par moi.
De plus, j'ai le même comportement sur deux bases distinctes.

En ce qui concerne le temps de réponse, il me semble que, par un " explain analyze ", le SGBD ne donne pas le temps réel total de la requête SQL.
A priori, l'optimiseur ne choisit pas le bon scenario car justement il sous-estime le temps réel de la requête SQL.
J'ai fait plusieurs tests d'exécution réel de la requête en changeant bien sûr les valeurs.

Où voyez-vous le temps réel total (avec les filtres et tri) ?

gleu · 18/10/2013 17:37:52

Julien a pourtant raison. La ligne suivante indique une durée d'exécution de 52 ms :

Sort (cost=5250.53..5250.63 rows=38 width=14) (actual time=52.938..52.948 rows=31 loops=1)

alors que vous dites "ce qui me donne un temps de traitement de l'ordre de 30 s".

La requête qui a ce Sort a duré 52 ms, pas 30 s.

Quant à "En ce qui concerne le temps de réponse, il me semble que, par un " explain analyze ", le SGBD ne donne pas le temps réel total de la requête SQL.", tout dépend de ce que vous prenez en compte. La durée donnée par le EXPLAIN ANALYZE sur la première ligne correspond à la durée d'exécution de la requête sur le serveur. Par contre, il n'intègre pas la durée d'envoi des données sur le réseau au client.

jacques · 18/10/2013 17:51:06

Pour ceux que cela pourrait intéresser, j'ai résolu le problème en alimentant la table TOTO_V2 différemment.
Les données insérées sont les mêmes mais elles ont été entrées d'une autre façon.
De plus, il a été créé les mêmes index après insertion (dans un cas comme dans l'autre).

Lorsque les données sont insérées par NUM_MAILLE, la requête SELECT avec un prédicat sur une plage de DAT et un seul NUM_MAILLE prend 100 fois plus de temps que lorsque ces mêmes données sont alimentées de façon tout venant (a priori plutôt par DAT).

PostgreSQL doit trop privilégier certains index (dans mon cas, i_toto_v2_num_maille avant résolution du problème) dans certaines circonstances.
Cela parait surprenant.

Il pourrait être intéressant de le signaler aux développeurs de l'optimiseur si vous les connaissez.

gleu · 18/10/2013 18:32:26

Ce sont les statistiques sur les données qui sont différentes, ce qui a un impact sur le plan, et donc sur la durée d'exécution.

jacques · 21/10/2013 12:15:27

Bonjour,

je dirais que ce qui est en cause, c'est soit la prise des statistiques, soit l'utilisation des statistiques, et donc le choix du plan retenu, soit encore l'exécution du plan choisi par l'optimiseur (par un blocage quelconque).
En ce qui concerne le blocage, j'ai indiqué les raisons pour lesquelles je ne retenais pas cette explication (différentes bases avec comportement identique et performances différentes avec prédicats et donc plans différents).

En ce qui concerne le temps réel d'exécution des requêtes SQL, je confirme mes propos précédents. C'est un constat.
Lorsque j'ai fait les tests sur deux machines différentes avec chacune un PostgreSQL de version différente, les temps de réponse des requêtes SQL qui m'intéressaient sur TOTO_V2 étaient excessifs (bien supérieurs à 10 secondes, aux environs de 30 s sur une machine et moins sur l'autre car plus puissante).

Les tests ont été faits et multipliés en changeant la valeur de NUM_MAILLE et la plage temporelle (DAT) pour éviter d'avoir les valeurs en cache et les temps de réponses étaient du même ordre de grandeur.

Lorsqu'une une requête du type suivante était lancée :
select to_char(dat,'YYYYMMDDHH24MISS'),SWI from TOTO_V2 where dat between to_timestamp('20080501000000','YYYYMMDDHH24MISS') and to_timestamp('20080531000000','YYYYMMDDHH24MISS') and num_maille=34 order by dat, num_maille
;
elle mettait bien plus de 10 s à répondre (en la mesurant avec " \timing on " ) et ce n'était pas à cause du réseau.
J'ai validé cet état de fait en changeant la requête et en ramenant plus de données, c'est à dire les mêmes avec des données supplémentaires, le plan changeait et la requête ( num_maille=34 remplacé par num_maille in (34,35,36) ) s'exécutait dans un temps raisonnable (bien moins d'1 seconde) car le plan changeait :
il utilisait alors l'index qui porte sur les 2 critères (conditions logiques du prédicat) DAT et NUM_MAILLE.

Informations sur le contenu de la table TOTO_V2 :
Dans la table, il y a 20151 lignes pour chaque NUM_MAILLE et 9892 NUM_MAILLE différentes.
Autrement dit, il y a 20151 DAT distincts et 9892 NUM_MAILLE distincts.

1ère machine (avec PostgreSQL 8.4.5):
============
bdcp=> explain analyze select to_char(dat,'YYYYMMDDHH24MISS'),SWI from TOTO_V2 where dat between to_timestamp('20080501000000','YYYYMMDDHH24MISS') and to_timestamp('20080531000000','YYYYMMDDHH24MISS') and num_maille=34 order by dat, num_maille;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Sort (cost=5250.53..5250.63 rows=38 width=14) (actual time=52.938..52.948 rows=31 loops=1)
Sort Key: dat
Sort Method: quicksort Memory: 27kB
-> Index Scan using i_toto_v2_num_maille on toto_v2 (cost=0.00..5249.54 rows=38 width=14) (actual time=44.844..52.900 rows=31 loops=1)
Index Cond: (num_maille = 34)
Filter: ((dat >= to_timestamp('20080501000000'::text, 'YYYYMMDDHH24MISS'::text)) AND (dat <= to_timestamp('20080531000000'::text, 'YYYYMMDDHH24MISS'::text)))

2ème machine (avec PostgreSQL 9.2.4):
============
bdcp=> explain analyze select to_char(dat,'YYYYMMDDHH24MISS')::bigint,SWI from TOTO_V2 where num_maille=1234
and (dat between to_date('20080501','YYYYMMDD') and to_date('20080531','YYYYMMDD')) order by dat, num_maille;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=1829.20..1829.27 rows=26 width=14) (actual time=75.792..75.793 rows=31 loops=1)
Sort Key: dat
Sort Method: quicksort Memory: 27kB
-> Index Scan using i_toto_v2_num_maille on toto_v2 (cost=0.00..1828.59 rows=26 width=14) (actual time=67.444..75.729 rows=31 loops=1)
Index Cond: (num_maille = 1234)
Filter: ((dat >= to_date('20080501'::text, 'YYYYMMDD'::text)) AND (dat <= to_date('20080531'::text, 'YYYYMMDD'::text)))
Rows Removed by Filter: 20120

On aurait l'impression que l'optimiseur se base sur un échantillon non représentatif, donc il ne pourrait pas donner le temps réel.
En passant, savez-vous si (ou comment faire en sorte que) l'analyse ( ANALYZE TABLE) scrute la totalité de la table ?

Toutefois, avec la 2ème machine, PostgreSQL indique bien que le filtre retire 20120 lignes pour ne conserver que les 31 lignes quotidiennes du mois.
Mais force est de constater que le temps réel d'exécution est bien différent des 75 (ou 76) ms indiqué lorsque l'on lance la requête SQL et non pas le EXPLAIN de la requête SQL.
La 2ème machine est plus puissante et le temps soit-disant réel indiqué est plus grand : 75 (ou 76) ms.
Bizarre, non ?

Par ailleurs, il me semble un peu étrange que sur la 1ère machine, le temps soit-disant réel pour filtrer plus de 20.000 ne prenne pas plus de 53 ms.
J'ai l'impression que le temps d'accès aux données n'est pas pris en compte.

Il me semble que le but de l'optimiseur n'est pas de réaliser les requêtes mais d'évaluer un coût pour les différents scenarii et de retenir celui qui a le moindre coût (estimé donc pas forcément réel).

Ou bien, mais cela m'étonnerait fortement, c'est parce qu'à chaque fois que j'ai lancé un EXPLAIN, les données étaient en cache et l'optimiseur le prenait en compte.
Mais cela ne me parait pas être ce que l'on demande à un " EXPLAIN ANALYZE " d'une requête SQL.

Dernière modification par jacques (21/10/2013 12:20:38)

gleu · 23/10/2013 00:12:07

> savez-vous si (ou comment faire en sorte que) l'analyse ( ANALYZE TABLE) scrute la totalité de la table ?

Non, ça ne parcourt pas la table entière. Le seul moyen de le faire serait d'augmenter très fortement default_statistics_target. Évidemment, ça a un coup monstrueux sur l'exécution de ANALYZE. Ça a aussi un coût fort sur l'exécution des requêtes.

> Mais force est de constater que le temps réel d'exécution est bien différent des 75 (ou 76) ms indiqué lorsque l'on lance la requête SQL et non pas le EXPLAIN de la requête SQL.
> La 2ème machine est plus puissante et le temps soit-disant réel indiqué est plus grand : 75 (ou 76) ms.
> Bizarre, non ?

Bizarre et jamais constaté chez tous les clients où j'ai pu aller en six ans de travail chez Dalibo.

> Par ailleurs, il me semble un peu étrange que sur la 1ère machine, le temps soit-disant réel pour filtrer plus de 20.000 ne prenne pas plus de 53 ms.

Ça ne me paraît pas étrange si l'index est déjà en mémoire.

> Il me semble que le but de l'optimiseur n'est pas de réaliser les requêtes mais d'évaluer un coût pour les différents scenarii et de retenir celui qui a le moindre coût (estimé donc pas forcément réel).

Pas sûr de ce que vous entendez par là. Le but de l'optimiseur est de trouver le plan d'exécution optimum pour une exécution la plus rapide possible. Il va donc évaluer tous les plans possibles (ou seulement un ensemble s'il y en a trop), calculer le coût de chaque plan généré, et ne conserver que le moins cher.

> Ou bien, mais cela m'étonnerait fortement, c'est parce qu'à chaque fois que j'ai lancé un EXPLAIN, les données étaient en cache et l'optimiseur le prenait en compte.

Le planificateur ne sait pas les données qui sont en cache et celles qui ne le sont pas. Par contre, il estime la probabilité qu'elles le soient et cela entre en compte dans le calcul du coût d'un plan. L'exécuteur bénéficie évidemment de la présence des données en cache (et donc la vitesse d'exécution d'un EXPLAIN ANALYZE dépend principalement de cette présence).

jacques · 23/10/2013 15:04:03

Bonjour,

Tout d'abord, merci bien pour les renseignements fournis.

En ce qui concerne votre remarque sur le temps pris pour filtrer plus de 20.000 lignes,
si j'interprète bien le plan:
Index Scan using i_toto_v2_num_maille on toto_v2
PostgreSQL utilise l'index i_toto_v2_num_maille (qui avait une taille de 4,2 Go et) qui ne porte que sur la colonne NUM_MAILLE (Index Cond: (num_maille = xxx)), donc il ne peut pas être utilisé pour filter les valeurs de DAT.
Il doit donc ramener 20151 lignes correspondantes à la condition logique num_maille = xxx
PostgreSQL indique aussi qu'il filtre sur les DAT sans donner d'indication sur le moyen de les filtrer donc je pense qu'il parcourt les lignes une à une :
Filter: ((dat >= to_date('20080501'::text, 'YYYYMMDD'::text)) AND (dat <= to_date('20080531'::text, 'YYYYMMDD'::text)))
Il retire (filtre) 20120 lignes pour n'en garder que 31 :
Rows Removed by Filter: 20120
S'il ne les parcourait pas une à une pour les filtrer, il devrait soit utiliser l'index de la clef primaire sur (DAT, NUM_MAILLE) mais dans ce cas pourquoi ne pas l'utiliser au début, soit les trier mais cela prend du temps et il ne l'indique pas au niveau du " Rows Removed by Filter ".
Je pense que la ligne :
Sort Key: dat
correspond à la clause " order by dat, num_maille "
En effet, il n'a pas besoin de trier sur NUM_MAILLE puisqu'il n'y a qu'une seule valeur de NUM_MAILLE.

Donc, les 20120 lignes filtrées doivent être a priori lues sur disque (vous dites vous-même " Le planificateur ne sait pas les données qui sont en cache et celles qui ne le sont pas "), donc cela prend un certain temps, a priori plus que 53 ms (ou même 76 ms).
A moins que ce temps est la durée que PostgreSQL met pour choisir le " bon " plan et non pas pour exécuter la requête SQL associée, mais cela ne présenterait pas d'intérêt.

gleu · 24/10/2013 00:04:07

> PostgreSQL indique aussi qu'il filtre sur les DAT sans donner d'indication sur le moyen de les filtrer donc je pense qu'il parcourt les lignes une à une :

Il a récupéré les adresses des lignes dans la table, donc il lit ces lignes et fait le filtre "manuellement" (ie, sans index).

> En effet, il n'a pas besoin de trier sur NUM_MAILLE puisqu'il n'y a qu'une seule valeur de NUM_MAILLE.

Oui, c'est ça.

> Donc, les 20120 lignes filtrées doivent être a priori lues sur disque

Ce n'est pas parce que le planificateur ne sait pas si elles sont en cache que l'exécuteur ne les lit pas dans le cache. Et vu la durée d'exécution, il les récupère dans le cache. Pour le savoir, il faudrait utiliser la clause BUFFERS. Inutile de le faire, il vous faut au moins une version 9.0 pour pouvoir l'utiliser.

> A moins que ce temps est la durée que PostgreSQL met pour choisir le " bon " plan et non pas pour exécuter la requête SQL associée, mais cela ne présenterait pas d'intérêt.

La durée indiquée par la partie "(actual ...)" est la durée réelle chronométrée à l'exécution.

jacques · 24/10/2013 13:53:23

Bonjour,

Vous écrivez « Ce n'est pas parce que le planificateur ne sait pas si elles sont en cache que l'exécuteur ne les lit pas dans le cache. Et vu la durée d'exécution, il les récupère dans le cache » :
je le conçois bien, mais vu que j'avais multiplié les tests en obtenant le même ordre de grandeur pour les temps de réponses, à savoir quelques dizaines de millisecondes indiqué en « actual time » et quelques dizaines de secondes avant d'avoir la réponse, je m'interroge toujours.
Par ailleurs, avec la multiplications des tests, il serait étrange qu'à chaque fois, les données à filtrer étaient en cache (la table fait toutefois 29 Go hors index).

« Pour le savoir, il faudrait utiliser la clause BUFFERS. Inutile de le faire, il vous faut au moins une version 9.0 » :
j'ai aussi une base en 9.2.4.
Comme indiqué précédemment, j'ai contourné le problème en rechargeant complètement la table, donc je n'ai plus de temps d'accès excessif et je ne peux plus actuellement refaire les tests.
Mais, comme cela m'intrigue, je vais essayer de reproduire la table qui répondait mal (c'est à dire avec l'ancienne méthode de chargement) sur la base PostgreSQL 9.2.4.

jacques · 24/10/2013 16:17:19

Dans l'ordre, les opérations effectuées sur une instance PostgreSQL 9.2.4 :
création de la table (que l'on appelera TOTO_V2) sans clef primaire ni index
chargement des données : 30 Go
création de la clef primaire avec index sur (DAT, NUM_MAILLE) : 5,4 Go
création de l'index sur NUM_MAILLE : 4,2 Go
ANALYZE de la table

Je retrouve le même comportement, c'est à dire utilisation de l'index sur NUM_MAILLE au lieu de celui sur (DAT, NUM_MAILLE) donc explosion du temps de traitement,
mais les durées réelles semblent bien correctes cette fois-ci.

Je dois donc me résoudre à penser que j'avais fait les tests un peu dans le désordre et que parfois j'avais des données en cache sans en être conscient.

Tests et résultats :
==================
psql=> \timing on
Chronométrage activé.
psql=> explain analyze verbose select to_char(dat,'YYYYMMDDHH24MISS')::bigint,SWI from TOTO_V2 where num_maille = 1234 and ( dat >=to_date('20090101','YYYYMMDD') and dat <=to_date('20090131','YYYYMMDD')) order by dat, num_maille;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=1834.41..1834.48 rows=29 width=14) (actual time=16491.427..16491.430 rows=31 loops=1)
Output: ((to_char(toto_v2.dat, 'YYYYMMDDHH24MISS'::text))::bigint), toto_v2.swi, toto_v2.dat, toto_v2.num_maille
Sort Key: toto_v2.dat
Sort Method: quicksort Memory: 27kB
-> Index Scan using i_toto_v2_num_maille on grille.toto_v2 (cost=0.00..1833.70 rows=29 width=14) (actual time=13882.626..16491.322 rows=31 loops=1)
Output: (to_char(toto_v2.dat, 'YYYYMMDDHH24MISS'::text))::bigint, toto_v2.swi, toto_v2.dat, toto_v2.num_maille
Index Cond: (toto_v2.num_maille = 1234)
Filter: ((toto_v2.dat >= to_date('20090101'::text, 'YYYYMMDD'::text)) AND (toto_v2.dat <= to_date('20090131'::text, 'YYYYMMDD'::text)))
Rows Removed by Filter: 20142
Total runtime: 16491.653 ms
(10 lignes)

Temps : 16506,967 ms

bdcp=> explain analyze select to_char(dat,'YYYYMMDDHH24MISS')::bigint,SWI from TOTO_V2 where num_maille = 22 and ( dat >=to_date('20050101','YYYYMMDD') and dat <=to_date('20050131','YYYYMMDD')) order by dat, num_maille;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=1834.41..1834.48 rows=29 width=14) (actual time=13197.399..13197.406 rows=31 loops=1)
Sort Key: toto_v2.dat
Sort Method: quicksort Memory: 27kB
-> Index Scan using i_toto_v2_num_maille on toto_v2 (cost=0.00..1833.70 rows=29 width=14) (actual time=11301.490..13197.359 rows=31 loops=1)
Index Cond: (num_maille = 22)
Filter: ((dat >= to_date('20050101'::text, 'YYYYMMDD'::text)) AND (dat <= to_date('20050131'::text, 'YYYYMMDD'::text)))
Rows Removed by Filter: 20142
Total runtime: 13197.451 ms
(8 lignes)

Temps : 13198,762 ms

=> c'est lent (il utilise l'index NON pertinent) !

Sur une autre base PostgreSQL (en 8.4.2 mais cela réagissait de la même façon avec 9.2.4), avec une méthode différente de chargement des mêmes données :
bdcp=> explain analyze select to_char(dat,'YYYYMMDDHH24MISS')::bigint,SWI from TOTO_V2 where num_maille = 22 and ( dat >=to_date('20050101','YYYYMMDD') and dat <=to_date('20050131','YYYYMMDD')) order by dat, num_maille;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using toto_v2_pkey on toto_v2 (cost=0.01..7022.15 rows=29 width=14) (actual time=53.990..442.746 rows=31 loops=1)
Index Cond: ((dat >= to_date('20050101'::text, 'YYYYMMDD'::text)) AND (dat <= to_date('20050131'::text, 'YYYYMMDD'::text)) AND (num_maille = 22))
Total runtime: 442.947 ms
(3 rows)

=> c'est rapide (il utilise l'index pertinent) !

Vous aviez indiqué que " Ce sont les statistiques sur les données qui sont différentes, ce qui a un impact sur le plan, et donc sur la durée d'exécution. "
On pôurrait penser que le planificateur est pris en défaut.

rjuju · 24/10/2013 21:07:44

Postgres estime ce plan plus efficace (coût de 1834.48 contre 7022.15). La vraie question est de savoir pourquoi postgres met 16 secondes pour lire environ 20000 lignes. Même s'il s'agit d'accès aléatoire, c'est quand même vraiment très lent (moins de 20 Ko/s). Quel est le système disque utilisé, y a-t-il des problèmes disque ou autres ? (dmesg, /var/log/messages ...).

jacques · 28/10/2013 12:41:45

Bonjour,

Pour ma part, je ne pense pas qu'il y ait de problème d'accès disques puisque le phénomène se ou s'est produit sur deux machines distinctes (celle en 8.4.2 et celle en 9.2.4).
Par ailleurs, ce sont des machines qui abritent d'autres instances PostgreSQL et je n'ai pas eu connaissance de problème de lenteur d'accès disque.
Je vais toutefois essayer de me renseigner.

Par ailleurs, je ne retrouve pas votre débit de 20 ko/s (mais plutôt 200 sauf erreur de ma part) :
30 Go pour 200 millions de lignes environ, soit environ 150 octets par ligne
pour 20.000 lignes, on a : 3 Mo
et 3 Mo en 13 ou 16s donne environ 200 ko/s

Mais, je pense qu'il faut davantage raisonner en nombre de lignes qu'en volume :
l'accès d'une ligne se faisant en moins de 1 ms (une milliseconde), ce qui ne me semble pas complètement mauvais.

jacques · 06/11/2013 17:14:19

Bonjour,

Juste pour confirmer que les capacités des disques ne semblent pas être en cause :
les données sont situées sur une baie SAN de disques SAS (Serial Attached SCSI) 15.000 tours/min de 450 Go, a priori accédée par fibre optique.

SQLpro · 07/11/2013 16:46:26

jacques a écrit :

Bonjour,

Pour ma part, je ne pense pas qu'il y ait de problème d'accès disques puisque le phénomène se ou s'est produit sur deux machines distinctes (celle en 8.4.2 et celle en 9.2.4).
Par ailleurs, ce sont des machines qui abritent d'autres instances PostgreSQL et je n'ai pas eu connaissance de problème de lenteur d'accès disque.
Je vais toutefois essayer de me renseigner.

Par ailleurs, je ne retrouve pas votre débit de 20 ko/s (mais plutôt 200 sauf erreur de ma part) :
30 Go pour 200 millions de lignes environ, soit environ 150 octets par ligne
pour 20.000 lignes, on a : 3 Mo
et 3 Mo en 13 ou 16s donne environ 200 ko/s

Mais, je pense qu'il faut davantage raisonner en nombre de lignes qu'en volume :
l'accès d'une ligne se faisant en moins de 1 ms (une milliseconde), ce qui ne me semble pas complètement mauvais.

Les SGBDR ne travaillent pas en ligne mais en page pour les lectures et écritures physiques. Et dans PG les pages font toujours 8 Ko...

A +

jacques · 08/11/2013 19:08:05

Bonjour,

Oui, effectivement, les données de plusieurs lignes peuvent être regroupées (ou pas) dans des mêmes blocs ou pages.

Que vous inspire la valeur de 200 ko/s ? Est-elle si mauvaise que cela pour PostgreSQL dans une architecture physique correcte ?

Le calcul pour obtenir le débit en volume de 200 ko/s a été fait par une règle de trois (règle de proportionnalité) en partant du nombre de lignes ramenées.
C'est un calcul théorique qui ne prend pas en compte la réalité du nombre de pages ramenées.

Si chaque (ou pratiquement chaque) ligne ramenée est dans une page différente (ce qui me semble loin d'être impossible en fonction du type d'alimentation), l'accès d'une ligne (c'est à dire l'accès d'une page de 8 ko) en moins de 1 ms (une milliseconde) est-il un signe d'accès disque lent ?

Y-a-t-il un moyen de savoir combien de pages sont réellement chargées (ou impactées) au cours d'une requête particulière ?

Pour choisir le plan d'exécution réelle de la requête SQL, l'optimiseur ou planificateur PostgreSQL prend-il en compte le nombre réel de pages qu'il devra charger ?

SQLpro · 11/11/2013 11:07:21

jacques a écrit :

Bonjour,
Oui, effectivement, les données de plusieurs lignes peuvent être regroupées (ou pas) dans des mêmes blocs ou pages.
Que vous inspire la valeur de 200 ko/s ? Est-elle si mauvaise que cela pour PostgreSQL dans une architecture physique correcte ?

Difficile de vous repondre car cela dépend de la méthode de calcul...

Le calcul pour obtenir le débit en volume de 200 ko/s a été fait par une règle de trois (règle de proportionnalité) en partant du nombre de lignes ramenées.
C'est un calcul théorique qui ne prend pas en compte la réalité du nombre de pages ramenées.

C'est bien cela le hic. Le seul moyen de prouver la chose est de faire un test sur une base maquette avec une table préremplie dont vous connaissez très exactement le nombre de pages et de lignes concernées.

Si chaque (ou pratiquement chaque) ligne ramenée est dans une page différente (ce qui me semble loin d'être impossible en fonction du type d'alimentation), l'accès d'une ligne (c'est à dire l'accès d'une page de 8 ko) en moins de 1 ms (une milliseconde) est-il un signe d'accès disque lent ?

Cela dépend beaucoup de la nature de vos disque (SAS, SATA... - 7 500, 10 000, 15 000 rpm... SSD) votre organisation de disques (RAID 1, 5, 10...).

Y-a-t-il un moyen de savoir combien de pages sont réellement chargées (ou impactées) au cours d'une requête particulière ?

Ce n'est pas aussi simple que sous SQL Server ou vous avez SET STATISTICS IO ON qui permet de tracer toutes les IO de toutes les tables. mais vous pouvez utiliser une combinaison de
EXPLAIN (ANALYZE ON, BUFFERS ON) MaRequête... avec les vue systèmes pg_statio_* (http://www.postgresql.org/docs/current/ … stats.html)

Pour choisir le plan d'exécution réelle de la requête SQL, l'optimiseur ou planificateur PostgreSQL prend-il en compte le nombre réel de pages qu'il devra charger ?

Non, il estime le nombre de lignes en fonction des statistiques, mais à partir du moment ou vous avez les bons index - les statistiques n'étant disponible que sous les indes. Sinon, il n'y a pas comme sous SQL Server de statistiques de colonnes de tables.
Cependant vous pouvez tenter d'augmenter le nombre d'intervalle dans l'histogramme statistiques qui par défaut est un peu faible dans PG (default_statistics_target / ALTER TABLE SET STATISTICS).

A +

gleu · 12/11/2013 01:14:29

Non, il estime le nombre de lignes en fonction des statistiques, mais à partir du moment ou vous avez les bons index - les statistiques n'étant disponible que sous les indes. Sinon, il n'y a pas comme sous SQL Server de statistiques de colonnes de tables.

Vous voulez dire l'inverse, non ? PostgreSQL calcule des statistiques sur le contenu de chaque colonne de chaque table. Les index n'ont rien à voir là-dedans.

#1 17/10/2013 19:03:40

utilisation non pertinente d'un index au lieu d'un autre

#2 17/10/2013 19:58:08

Re : utilisation non pertinente d'un index au lieu d'un autre

#3 17/10/2013 20:13:37

Re : utilisation non pertinente d'un index au lieu d'un autre

#4 18/10/2013 07:42:34

Re : utilisation non pertinente d'un index au lieu d'un autre

#5 18/10/2013 17:37:52

Re : utilisation non pertinente d'un index au lieu d'un autre

#6 18/10/2013 17:51:06

Re : utilisation non pertinente d'un index au lieu d'un autre

#7 18/10/2013 18:32:26

Re : utilisation non pertinente d'un index au lieu d'un autre

#8 21/10/2013 12:15:27

Re : utilisation non pertinente d'un index au lieu d'un autre

#9 23/10/2013 00:12:07

Re : utilisation non pertinente d'un index au lieu d'un autre

#10 23/10/2013 15:04:03

Re : utilisation non pertinente d'un index au lieu d'un autre

#11 24/10/2013 00:04:07

Re : utilisation non pertinente d'un index au lieu d'un autre

#12 24/10/2013 13:53:23

Re : utilisation non pertinente d'un index au lieu d'un autre

#13 24/10/2013 16:17:19

Re : utilisation non pertinente d'un index au lieu d'un autre

#14 24/10/2013 21:07:44

Re : utilisation non pertinente d'un index au lieu d'un autre

#15 28/10/2013 12:41:45

Re : utilisation non pertinente d'un index au lieu d'un autre

#16 06/11/2013 17:14:19

Re : utilisation non pertinente d'un index au lieu d'un autre

#17 07/11/2013 16:46:26

Re : utilisation non pertinente d'un index au lieu d'un autre

#18 08/11/2013 19:08:05

Re : utilisation non pertinente d'un index au lieu d'un autre

#19 11/11/2013 11:07:21

Re : utilisation non pertinente d'un index au lieu d'un autre

#20 12/11/2013 01:14:29

Re : utilisation non pertinente d'un index au lieu d'un autre

Pied de page des forums

À propos

Nous contacter

Groupes Locaux

Réseaux Sociaux