Discussion :

[Shivan]

Bonjour,

J'ai besoin d'aide pour comprendre comment fonctionne les index.

1ere chose : j'effectue mes tests sur postgres 8.0

J'ai une table de stats qui ressemble à ça :

Code :

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

1
2
 
Table stats(connexion int4 ,client_id int4 ,period int2 ,date timestamp)

connexion = le nombre de connexion faites par le client.
client = id du client
period = 0 stat du jour / 1 stat du mois / 2 stat depuis le debut
date = date de creation de la ligne de stat

Donc a chaque fois qu'un client se connecte je met à jour la ligne du jour pour ce client, la ligne du mois sur ce client, la ligne "depuis le debut" pour ce client.

Voila ce que ça donne en requête :

Code :

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

1
2
3
4
5
6
 
Update stats set connexion= connexion+ 1 where client_id = 12345 and period = 0 and date_trunc('DAY',date) = date_trunc('DAY',now());
 
Update stats set connexion= connexion+ 1 where client_id = 12345 and period = 1 and date_trunc('MONTH',date) = date_trunc('MONTH',now());
 
Update stats set connexion= connexion+ 1 where client_id = 12345 and period = 2;

A partir de la je fais mes index. J'ai testé plusieurs types d'index :

1. Index sur client et period

Code :

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

1
2
 
CREATE INDEX idx_clientid_period  ON stats  USING btree (client_id, period);

2. Index sur client, period et date

Code :

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

1
2
 
CREATE INDEX idx_clientid_period_date  ON stats USING btree (client_id, period,date);

3. 3 index spécifiques pour chaque ligne d'update

Code :

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

1
2
3
4
5
6
 
CREATE INDEX idx_datemonth_clientid_period ON stats USING btree  (date_trunc('MONTH'::text, date), client_id, period);
 
CREATE INDEX idx_dateday_clientid_period ON stats USING btree  (date_trunc('DAY'::text, date), client_id, period);
 
CREATE INDEX idx_dateall_clientid_period ON stats USING btree  (date, client_id, period);

Je me suis dit qu'il serait choisir l'index le plus rapide. Quand je regarde le plan de requête je vois qu'il préfère utilisé l'index 1 sur client et period et ensuite il filtre par date.

donc ma question est : pourquoi il utilise de préférence cette index alors qu'il possède des index plus précis avec date_trunc (les 3 index de la partie 3) ?

Quelle est le meilleur index à mettre pour optimiser ma table ?

Merci de vos réponses

Shivan

[Sve@r]

donc ma question est : pourquoi il utilise de préférence cette index alors qu'il possède des index plus précis avec date_trunc (les 3 index de la partie 3) ?

Ben parce que tes requêtes se basent d'abord sur l'id client qui se connecte et non sur la date de connection !!!

Quelle est le meilleur index à mettre pour optimiser ma table ?

Un index qui se base sur la clef primaire => client_id (qu'on place conventionnellement en premier champ de table)

Plus généralement, un index qui se base sur le where le plus utilisé !!!