Bonsoir,
Je pense que tout est dans le sous-titre: j'ai un exercice où il faut expliquer le choix du SGBD d'utiliser tel ou tel plan de requête et l'une des requêtes s'écrit:

Select * from table where cle_de_la_table*10=900;

mais déjà à la base je ne vois pas trop pourquoi ne pas mettre directement cle_de_la_table=90... ni comment le rechercher (ce n'est pas indiqué dans mes cours) et je pense que si je tente une recherche sur le net, bon ben vu la spécifité des mots ça va me ressortir n'importe quoi...

Une bonne âme pour éclairer ma lanterne ?

Bonsoir,
Je dis peut-être une bêtise, mais je ne vois pas où est le problème.
Puisque tu n'es pas dans le cas où tu aurais plusieurs résultats (comme avec les inoubliables f(x) ) : 900 divisé par 10, ça fait 90 et seulement 90.

Donc on peut en déduire que SELECT * FROM table WHERE cle_de_la_table*10=900 , c'est exactement la même chose que SELECT * FROM table WHERE cle_de_la_table=90 , à la différence près que je pencherais plutôt pour la deuxième solution, puisque elle évite à ton SGBD de faire des calculs supplémentaires (et surtout inutiles !).
En plus, c'est plus clair pour le développeur.

Bon, dis-moi si je suis complètement à côté de la plaque...

x*10=900 équivaut à x=90 en théorie, et en pratique si x est un entier et que tu n'es pas sous MySQL (sous MySQL les autres valeurs qui remplissent la condition dépendent de si t'es en 32 ou 64 bits, des flags, etc) ; ça pourrait marcher pour le type NUMERIC aussi, pour le float certaines surprises sont toujours à prévoir...

Citation : Lachésis

Select * from table where x*10=900;

Déjà il faudrait connaître le type de x.

En supposant que x est entier, la BDD pourrait résoudre l'équation et en déduire que x=90, mais je ne connais aucune BDD qui le fera, puisque cela ne sert à rien. Disons que ce n'est pas son boulot (c'est le tien).

Donc cela équivaut à WHERE f(x) = constante, donc scan complet et évaluation de l'expression à chaque ligne.

Sauf si tu as un index sur f(x), ce qui se fait pour des fonctions un peu plus complexes, disons.

En plus du type de x, on pourrait parler aussi des bugs décisions ridicules choix techniques parfaitement rationnels effectués par certains développeurs de BDD opensource :

mysql> CREATE TABLE beh ( x BIGINT UNSIGNED );
mysql> INSERT INTO beh VALUES (18446744073709550716);
mysql> SELECT x+900 FROM beh;
+-------+
| x+900 |
+-------+
|     0 |
+-------+

mysql> SELECT * FROM beh WHERE x = -900;
(rien)

mysql> SELECT * FROM beh WHERE x + 900 = 0;
+----------------------+
| x                    |
+----------------------+
| 18446744073709550716 |
+----------------------+

D'ailleurs les tâcherons développeurs ne manquent pas d'humour :

mysql> CREATE TABLE bork (x TEXT);
mysql> INSERT INTO bork VALUES ('123'),('abc');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM bork WHERE x=0;
+------+
| x    |
+------+
| abc  |
+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM bork WHERE x*10=0;
+------+
| x    |
+------+
| abc  |
+------+
1 row in set (0.02 sec)

mysql> SELECT * FROM bork WHERE x>=123;
+------+
| x    |
+------+
| 123  |
+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM bork WHERE x>='123';
+------+
| x    |
+------+
| 123  |
| abc  |
+------+
2 rows in set (0.00 sec)

x est un entier. On utilise le système postgres

Donc pour vous cette requête est strictement égale à x=90 ?


Possible, le souci c'est que la question suivante c'est la même chose mais where x=900/10 et là le temps est légèrement inférieur (je parle des infos données par la commande Explain Analyse) en unité arbitraire s'entends.
(je parle bien de la ligne cost=x.xx...y.yy, blabla)

J'adore Lord Casque Noir !!!!!!
Ralalalaa, il faut créer un bêtisier !

Citation : Lachésis

x est un entier. On utilise le système postgres
Donc pour vous cette requête est strictement égale à x=90 ?

Ah bah sous postgres les développeurs prennent beaucoup moins de drogues et les dépassements d'entiers sont contrôlés, donc oui, si x est un entier c'est équivalent à x=90.

Citation : Lachésis

Possible, le souci c'est que la question suivante c'est la même chose mais where x=900/10 et là le temps est légèrement inférieur (je parle des infos données par la commande Explain Analyse) en unité arbitraire s'entends.
(je parle bien de la ligne cost=x.xx...y.yy, blabla)

L'optimiseur se rend compte que 900 et 10 sont des constantes et que la division de 2 constantes donne toujours la même valeur, donc il remplace 900/10 par le résultat au lieu de le recalculer pour chaque ligne de ta table.

En gros toute fonction peut être marquée comme IMMUTABLE (résultat toujours constant en fonction des paramètres) ou STABLE (idem mais uniquement lors de la requête courante) ou VOLATILE (le résultat peut changer même si les paramètres ne changent pas). Les opérateurs sont des fonctions aussi donc idem. L'optimiseur s'en sert pour virer tout ce qu'il peut du plan d'exécution de la requête et remplacer par des constantes.

Même chose pour WHERE blabla = (SELECT bla) si le contenu des parenthèses ne change pas, tu auras un noeud InitPlan exécuté une seule fois.

La ligne "cost=..." reflète cette différence, dans un cas il faut calculer une expression pour chaque ligne (WHERE f(x)=constante) dans l'autre cas, c'est juste WHERE x=constante.

Citation : Cintre Sournois

J'adore Lord Casque Noir !!!!!!
Ralalalaa, il faut créer un bêtisier !

Mes préférés :

mysql> SELECT x, x+1, x-1, x=1, x='3' FROM meeeeeh;
+------+------+------+------+-------+
| x    | x+1  | x-1  | x=1  | x='3' |
+------+------+------+------+-------+
| 3    |    2 |    0 |    1 |     1 |
+------+------+------+------+-------+

mysql> SELECT 0 IN ('a','b');
+----------------+
| 0 IN ('a','b') |
+----------------+
|              1 |
+----------------+

donc la différence de temps est juste dûe au calcul à chaque ligne ? Bon eh ben on va dire que c'était ce sur quoi l'enseignant voulait nous faire mettre le doigt. Je pensais à une entourloupe plus compliquée que ça mais pourquoi pas

merci beaucoup (j'attends encore une heure ou deux et s'il n'y a personne bondissant pour hurler objection je marque le sujet résolu )

Citation : Lachésis

donc la différence de temps est juste dûe au calcul à chaque ligne ? Bon eh ben on va dire que c'était ce sur quoi l'enseignant voulait nous faire mettre le doigt. Je pensais à une entourloupe plus compliquée que ça mais pourquoi pas

merci beaucoup (j'attends encore une heure ou deux et s'il n'y a personne bondissant pour hurler objection je marque le sujet résolu )

Le temps passé à calculer l'expression n'est pas très important.

Ce qu'il faut voir c'est que WHERE f(x)=... t'interdit l'utilisation d'un index, sauf si tu as un index d'expression (rarement utilisé mais très pratique si l'expression est compliquée et lente).

test=> CREATE TABLE tst AS SELECT generate_series( 1,1000000 ) x ;
test=> ALTER TABLE tst ADD PRIMARY KEY (x);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY créera un index implicite « tst_pkey » pour la table « tst »
test=> VACUUM ANALYZE tst;
test=> EXPLAIN ANALYZE SELECT * FROM tst WHERE x=900;
                                                  QUERY PLAN                                                  
--------------------------------------------------------------------------------------------------------------
 Index Scan using tst_pkey on tst  (cost=0.00..8.38 rows=1 width=4) (actual time=0.042..0.043 rows=1 loops=1)
   Index Cond: (x = 900)
 Total runtime: 0.065 ms
(3 lignes)

test=> EXPLAIN ANALYZE SELECT * FROM tst WHERE x/10=90;
                                              QUERY PLAN                                               
-------------------------------------------------------------------------------------------------------
 Seq Scan on tst  (cost=0.00..19425.00 rows=5000 width=4) (actual time=0.181..187.042 rows=10 loops=1)
   Filter: ((x / 10) = 90)
 Total runtime: 187.070 ms
(3 lignes)

test=> CREATE INDEX tst_x90 ON tst( (x/10) );
test=> EXPLAIN ANALYZE SELECT * FROM tst WHERE x/10=90;
                                                     QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tst  (cost=95.11..4805.49 rows=5000 width=4) (actual time=0.067..0.072 rows=10 loops=1)
   Recheck Cond: ((x / 10) = 90)
   ->  Bitmap Index Scan on tst_x90  (cost=0.00..93.86 rows=5000 width=0) (actual time=0.060..0.060 rows=10 loops=1)
         Index Cond: ((x / 10) = 90)
 Total runtime: 0.098 ms

L'index d'expression est très utile sur "lower( texte )" pour faire des recherches LIKE non sensibles à la casse tout en utilisant l'index.

Tu féliciteras ton prof de ma part pour vous apprendre postgres à l'école.

merci, j'ai tendance à parler de temps arbitraire à la place de coût c'est vrai.

Tiens, tant que j'y suis, le fait qu'il y ait des clés ou pas ça peut changer le temps de parcours sur un seq scan ? J'ai la doc postgesql sous le nez mais c'est comment dire, vaste... Y a des sources plus simples pour les questions d'optimisation des bases de données sur le net ?

> le fait qu'il y ait des clés ou pas ça peut changer le temps de
> parcours sur un seq scan

- le seq scan lit la table et pas les index (quand tu dois examiner une grande partie des lignes, lire l'index en plus de la table ralentirait le processus) attention contrairement à mysql postgres ne gère pas le "index-only scan" genre SELECT a,b WHERE a=1 en ayant un index sur (a,b) va quand même lire dans la table

- l'index scan pioche dans l'index et pour chaque ligne qui valide les conditions, il va la chercher dans la table, si chaque accès nécessite un accès disque, c'est très lent, donc :

- le bitmap index scan se situe entre les deux, il lit d'abord l'index, puis trie les pages de la table qui match dans l'ordre, et les lit dans l'ordre (donc accès disque beaucoup plus rapide si tu as beaucoup de lignes à récupérer)

> Y a des sources plus simples pour les questions d'optimisation
> des bases de données sur le net ?

hmmm... ce serait une bonne idée de faire une liste de liens et de poster ça XD

en effet. Lorsque j'aurais la correction de mon sujet (un compte-rendu de TP portant sur le comportement du SGBD postgres en diverses situation) je verrais si le prof est d'accord si je le passe au site pour que d'autres en profitent.