Re: Update et effet sur les INDEX

Bonsoir à tous,

Guillaume Lelarge <guillaume(at)lelarge(dot)info> writes:
> Rodolphe Quiédeville <rodolphe(at)quiedeville(dot)org> a écrit :
>> J'ai cherché en vain ce soir des informations sur la mécanique interne à
>> PG lors d'un UPDATE et les effets sur les index, je cherche à mesurer
>> l'impact de performance entre deux requêtes, une spécifiant uniquement
>> la colonne à modifier et une autre indiquant toutes les colonnes même
>> celle qui ne sont pas modifiées.

Dans PostgreSQL, étant donnée l'implémentation de MVCC telle que décrite
au lien suivant, toute opération d'UPDATE est strictement équivalente à
une opération DELETE de la version courrante de la ligne suivie d'une
opération INSERT de la nouvelle version de la ligne :

http://www.postgresql.org/docs/9.3/static/mvcc.html

>> UPDATE foo SET active = true WHERE id = 1;
>>
>> L'index foo_name ne sera pas mis à jour car name n'est pas modifié.

Il sera mis à jour dans le cas par défaut, mais on peut facilement faire
en sorte qu'il ne soit pas mis à jour.

> Il sera mis à jour car l'adresse de la ligne changera. Sauf si la nouvelle
> adresse se trouve sur le même bloc que l'ancienne.

Guillaume est très joueur ce soir, ou peut être trop pressé pour
répondre à ton email, et du coup il répond sans répondre… voir HOT un
peu plus loin.

>> Mais si je fais (considérant toujours que active=false), en ajoutant la
>> colonne name mais avec la même valeur.
>>
>> UPDATE foo SET name='Barack', active = true WHERE id = 1;
>>
>> Est-ce que PG met à jour l'index où il détecte que la valeur n'a pas
>> changé dans la ligne ?
>
> Il met à jour l'index.
>
> Bon courage, sincèrement. Il y a tellement de cas possibles que ça rend
> l'analyse très compliquée.

C'est relativement vrai, mais très bien documenté. Il suffit en grande
partie de lire la doc. Le soucis autour de cette question très précise
sur les détails d'implémentation de la mise à jour des données et des
indexes de PostgreSQL, c'est que la documentation en question se trouve
dans les sources de l'outil plutôt que dans la partie utilisateur.

Ceci dit ça tombe assez bien, PostgreSQL est Open Source, on a donc
accès au document :

http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/access/heap/README.HOT

Il faut ensuite lire la documentation du FILLFACTOR, pour les tables
(heap) et les indexes :

http://www.postgresql.org/docs/9.3/static/sql-createtable.html
http://www.postgresql.org/docs/9.3/static/sql-createindex.html

Je recommande de jouer avec un fillfactor à 40% quand on doit le modifier.

Ceci dit HOT ne fonctionne que lorsque des valeurs non indexées sont
modifiées, donc cela couvrira très bien ton premier cas mais pas le
second.

> Mon seul conseil est de ne pas mettre à jour une ligne pour rien car ça
> sera forcément plus coûteux que de ne pas la mettre à jour. Même si vous
> faites un bête "UPDATE t1 set c1=c1;", vous dupliquez la ligne, ce qui a
> une conséquence immédiate en écriture de la table et des index, et une
> conséquence retardée au niveau de la maintenance par VACUUM de la table. Je
> pense que, sur ce bête truc, on peut gagner beaucoup. Ensuite, le coup des
> colonnes me paraît plus discutable.

Il existe de nombreux cas où un UPDATE qui semble inutile au niveau SQL
est en fait réalisé pour ses effets de bords, tels les triggers.

--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

--
Envoi via la liste pgsql-fr-generale (pgsql-fr-generale(at)postgresql(dot)org)