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