Vous n'êtes pas identifié(e).
Bonjour,
Merci pour la réponse.
Je pensais que c'était le comportement originel qui consiste à ne pas vraiment bien vérifier les paramètres, mais que maintenant ce comportement avait été abandonné au profit d'une fonction davantage " cadrée " :
SELECT to_timestamp('20191121','yyyymmddddd') ;
ERROR: source string too short for "ddd" formatting field
DÉTAIL : Field requires 3 characters, but only 2 remain.
ASTUCE : If your source string is not fixed-width, try using the "FM" modifier.
ou bien :
SELECT to_timestamp('20191199','yyyymmdd') ;
ERROR: date/time field value out of range: "20191199"
-> qui auparavant renvoyait un résultat (me semble-t-il)
Donc, je pense que la fonction a été améliorée mais pas pour tous les cas.
Bonjour,
Avec PostgreSQL version 10.6, j'ai noté le résultat étrange avec la fonction to_timestamp(text, text) lorsque les paramètres sont inversés :
SELECT to_timestamp('yyyymmdd','20191121') ;
to_timestamp
---------------------------
0001-01-01 00:00:00+00 BC
(1 ligne)
Selon moi, c'est un bug, n'est-ce pas ?
A priori, inet_server_addr() ne fonctionne pas pour un accès local (ce qui n'est pas forcément étonnant).
J'avais déjà testé cette fonction inet_server_addr() qui n'a pas fonctionné dans certains cas.
Et, d'après ce que j'ai pu lire ici ou là, il semble effectivement que la fonction ne renvoie rien dans certains cas.
Par contre, je ne connaisais pas cette extension hostname qui contient la fonction hostname().
Il faudrait que je l'essaye.
Merci bien.
Bonjour,
Savez-vous comment connaître (interrogation SQL) le nom du serveur (machine) hébergeant le SGBD PostgreSQL sur lequel une session est connectée (en sachant que la connexion au serveur ne se fait pas forcément en local) ?
Cordialement
Bonjour,
Les sources ont été modifiées par l'auteur et les tests réalisés avec l'exécutable construit à partir de ces sources sont positifs.
Bonjour,
La réponse de la part de Michael Meskes :
"
> Why the indicator equals 0 (is not strictly positive) and the value is
> truncated (modified) ?
Because according to standard C types it is not truncated. The string does
have more digits tha a flot can handle, but they are all valid, i.e. a
convesion does correctly eat them all but the internal representation is not
able to store them all.
You bring up an interesting point here, the data is not exactly truncated, it
is just rounded. Not sure what (if anything) the standard says.
> Why this error message whereas an example is given in the PostgreSQL 9.4
> documentation (see above) ?
I guess the documentation is wrong, but I have to look into it. Normally the
varchar type needs a length (as an array of char) or it will become a pointer
to char.
"
Bonjour,
Dans le cadre de tests avec ecpg (PostgreSQL 9.4.0) 4.10.0, j'ai rencontré deux difficultés :
===> Primo :
test sur valeur de l'indicateur (variable indicatrice de marqueur NULL) d'une variable float
Le programme ecpg :
=================
...
connexion();
EXEC SQL BEGIN DECLARE SECTION;
float ff;
int i_ff;
EXEC SQL END DECLARE SECTION;
EXEC SQL SELECT 1256898899999885888855424885522.125666666558855555555555555555555555555554458955 INTO :ff :i_ff ;
if (i_ff == 0) printf("indicateur 0 ; résultat valeur ind : %f %d \n", ff, i_ff);
if (i_ff > 0) printf("indicateur positif (valeur tronquee) ; résultat valeur ind : %f %d \n", ff, i_ff);
return 0;
Le résultat du lancement de l'exécutable :
========================================
indicateur 0 ; résultat valeur ind : 1256898867335759245102806990848.000000 0
La question :
===========
Pourquoi l'indicateur vaut 0 (n'est donc pas strictement positif) alors que la valeur est tronquée (modifiée) ?
===> Secundo :
test sur variable varchar
Selon http://docs.postgresqlfr.org/9.4/ecpg-variables.html, paragrahe " 33.4.6. Indicateurs " :
"
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR val;
int val_ind;
EXEC SQL END DECLARE SECTION:
...
EXEC SQL SELECT b INTO :val :val_ind FROM test1;
"
Le programme ecpg :
=================
...
connexion();
EXEC SQL BEGIN DECLARE SECTION;
varchar vv;
int i_vv;
EXEC SQL END DECLARE SECTION;
EXEC SQL SELECT 'ceci est une chaine de caracteres' INTO :vv :i_vv ;
return 0;
A la pré-compilation ecpg :
=========================
ERREUR : les pointeurs sur des chaînes de caractères (varchar) ne sont pas implantés
Question :
========
Pourquoi ce message d'erreur alors qu'un exemple est donné dans la doc. PostgreSQL 9.4 citée ci-avant ?
Merci par avance.
Bonjour,
avec une table t créée de la sorte :
SQL>
CREATE TABLE t(a integer, b timestamp without time zone, c real);
un fichier de commandes pgloader_commandes.txt :
LOAD CSV
FROM './donnees_a_charger.csv' (a, b, c)
INTO postgresql://anonymous:anonymous@mamachine:5432/mabase?t(a,b,c)
WITH fields terminated by ';'
;
un fichier de données donnees_a_charger.csv :
100;2015-01-01 00:00:00;-6
101;2015-01-02 00:00:00;-2.1
102;2015-01-03 00:00:00;3.4
103;2015-01-04 00:00:00;4.7
104;2015-01-05 00:00:00;0.4
Si je lance pgloader avec le fichier de commandes pgloader_commandes.txt en simple argument, il charge bien la table t :
pgloader ./pgloader_commandes.txt
2015-09-15T13:19:55.129000Z LOG Main logs in '/tmp/pgloader/pgloader.log'
2015-09-15T13:19:55.140000Z LOG Data errors in '/tmp/pgloader/'
2015-09-15T13:19:55.140000Z LOG Parsing commands from file #P"/home/climsol/chaine/pgloader_commandes.txt"
table name read imported errors time
------------------------------ --------- --------- --------- --------------
fetch 0 0 0 0.016s
------------------------------ --------- --------- --------- --------------
u 5 5 0 0.048s
------------------------------ --------- --------- --------- --------------
Total import time 5 5 0 0.064s
Par contre, si je lance pgloader avec les options en paramètres, j'ai une erreur a priori sur l'argument --with :
pgloader --type csv --field "a,b,c" --with "fields terminated by ';'" ./donnees_a_charger.csv postgresql://anonymous:anonymous@mamachine:5432/mabase?t
=> MESSAGE D'ERREUR :
An unhandled error condition has been signalled: At
fields terminated by ';'
^ (Line 1, Column 0, Position 0)
Could not parse KW-NO. Expected:
the character Space
or the character Tab
or the character Newline
or the string "--"
or the string "/*"
or the string "no", disregarding case
Reached via
KW-NO
-> (AND IGNORE-WHITESPACE (~ "no") IGNORE-WHITESPACE)
-> "no"
Could not parse WHITESPACE. Expected:
the character Space
or the character Tab
or the character Newline
or the string "--"
or the string "/*"
Reached via
2015-09-15T13:32:00.063000Z LOG Main logs in '/tmp/pgloader/pgloader.log'
2015-09-15T13:32:00.069000Z LOG Data errors in '/tmp/pgloader/'
2015-09-15T13:32:00.070000Z FATAL We have a situation here.
WHITESPACE
-> (+ (OR Space Tab Newline Newline COMMENTS))
-> (OR Space Tab Newline Newline COMMENTS)
-> COMMENTS
-> (OR SINGLE-LINE-COMMENT MULTI-LINE-COMMENT)
-> MULTI-LINE-COMMENT
-> (AND "/*" (+ (NOT "*/")) "*/")
-> "/*"
Could not parse COPY-OPTION. Expected:
the string "truncate", disregarding case
or the string "skip", disregarding case
or the character Space
or the character Tab
or the character Newline
or the string "--"
or the string "/*"
or the string "batch", disregarding case
Reached via
COPY-OPTION
-> (OR OPTION-BATCH-ROWS OPTION-BATCH-SIZE OPTION-BATCH-CONCURRENCY
OPTION-TRUNCATE OPTION-SKIP-HEADER)
-> OPTION-SKIP-HEADER
-> (AND KW-SKIP KW-HEADER EQUAL-SIGN (+ (DIGIT-CHAR-P CHARACTER)))
-> KW-SKIP
-> (AND IGNORE-WHITESPACE (~ "skip") IGNORE-WHITESPACE)
-> "skip"
Avez-vous une explication sur l'erreur ?
Bonjour,
Je cherche comment afficher (déterminer) le type d'une expression.
Par exemple, le type de l'expression " 123/2 " ou bien l'expression " 123/2.0 "
Le mieux serait une fonction SQL, à défaut une fonction psql ou encore en PlPgSql
Je n'ai pas trouver d'autres moyens que de faire, sous psql :
CREATE table maTable as SELECT 123/2 as c1;
SELECT 1
\d maTable
Table « public.matable »
Colonne | Type | Modificateurs
---------+---------+---------------
c1 | integer |
DROP table maTable;
DROP TABLE
CREATE table maTable as SELECT 123/2.0 as c1;
SELECT 1
\d maTable
Table « public.matable »
Colonne | Type | Modificateurs
---------+---------+---------------
c1 | numeric |
Connaissez un moyen plus simple ?
Merci par avance.
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 ?
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.
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.
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.
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.
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.
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.
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.
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) ?
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
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
bdcp=> \d toto_v1
Table "toto_v1"
Column | Type | Modifiers
---------------+-----------------------------+-----------
dat | timestamp without time zone | not null
origine | smallint | not null
num_maille | smallint | not null
prenei | real |
preliq | real |
t | real |
ff | real |
q | smallint |
dli | real |
ssi | real |
etr | real |
pe | real |
swi | real |
drainc | real |
runc | real |
resr_neige | double precision |
hteurneige | real |
snow_frac | smallint |
tsurf | real |
swi_unif | real |
origine_swi_u | smallint |
Indexes:
"toto_v1_pkey" PRIMARY KEY, btree (dat, num_maille)
"i_toto_v1_num_maille" btree (num_maille)
bdcp=> \d toto_v2
Table "toto_v2"
Column | Type | Modifiers
---------------+-----------------------------+-----------
dat | timestamp without time zone | not null
origine | smallint | not null
num_maille | smallint | not null
prenei | real |
preliq | real |
t | real |
ff | real |
q | smallint |
dli | real |
ssi | real |
etr | real |
pe | real |
swi | real |
drainc | real |
runc | real |
resr_neige | real |
hteurneige | real |
snow_frac | smallint |
tsurf | real |
swi_unif | real |
origine_swi_u | smallint |
etp | real |
hteurneige6 | real |
resr_neige6 | real |
hteurneigex | real |
wg1 | real |
wg2 | real |
wg3 | real |
wgi1 | real |
wgi2 | real |
tinf_h | real |
tsup_h | real |
Indexes:
"toto_v2_pkey" PRIMARY KEY, btree (dat, num_maille)
"i_toto_v2_num_maille" btree (num_maille)
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.
Bonjour,
Contrairement aux fonctions de groupe qui par définition ne renvoient qu'une seule ligne par groupe (GROUP BY), les fonctions analytiques renvoient toutes les lignes des partitions (PARTITION BY).
Cordialement
Bonjour,
Le problème qui m'amène à ouvrir cette discussion est le suivant :
lancer, une commande système (par exemple exécuter un programme, un script Linux shell ou bien créer un fichier) lorsqu'une table est mise à jour (insertion d'une ligne ou modification de la valeur d'une colonne) ou bien lorsque la valeur d'une colonne de type date devient égale à la date système.
On peut donc penser tout naturellement à un TRIGGER.
Mais, il semble que PostgreSQL ne soit pas destiné à lancer des commandes système.
Dans au moins un autre SGBD du marché, il existe une façon de lancer une commande système à partir du client SQL (l'équivalent de psql), mais je n'ai pas trouvé l'équivalent sous PostgreSQL, y en a-t-il un ?
Par exemple, pourrait-on, à l'aide d'un déclencheur (TRIGGER) sur l'évènement INSERT ou UPDATE de la colonne de la table spécifiée, lancer l'action (lancement de la commande système) à l'aide d'une fonction en langage C (C-Language Function) qui serait utilisé en tant que langage procédural associé au déclencheur ou par un autre moyen ?
Est-ce qu'un trigger peut déclencher un job de PgAgent ?
Pourrait-on envisager qu'une tâche (Job) de PgAgent aille, en première étape à l'aide d'une requête SQL, consulter la valeur d'une ligne/colonne d'une table et en fonction du résultat, lance, en deuxième étape la commande système ?
Cordialement
Effectivement, c'était évident pour moi avant cette discussion, mais si je me réfère à votre idée sur les " algorithmes différents pour cette devinette " et dès lors je raisonne selon un algorithme, cela devient davantage une devinette qu'un algorithme :
to_timestamp('201107-2','yyyymmdd'); --> 2 jours avant le 30 juin car c'est le jour qui précède le 1er juillet, logique quoi.
Autre remarque :
bdcp=> select to_timestamp('20110700','yyyymmdd'); -- même résultat qu'avec 20110701
to_timestamp
------------------------
2011-07-01 00:00:00+00
(1 row)
bdcp=> select to_timestamp('20110701','yyyymmdd'); -- même résultat qu'avec 20110700
to_timestamp
------------------------
2011-07-01 00:00:00+00
