Indexer une table

[localhost]

Bonjour,
Je veux indexer ma table de facon a optimiser les requetes de selection (nombreuses) et je voudrais avoir votre avis, je sais que pour optimiser une table on fait des index selon la methode dont on fait les select, pour moi le plus grand nombre des select c'est de cette forme :

WHERE (LOWER("Content") LIKE 'quelquechose%')

est ce qu'un index de ce type suffira :

Code :

CREATE INDEX name ON TABLE USING RTREE (LOWER(Content));

qu'est ce que vous en pensez ?

[kryskool]

qu'est ce que vous en pensez ?

Je dirais que tu n'a pas pris la peine de lire la doc sur comment bien créer ses index.

La réponse concernant les index se trouve au début du chapitre 11.2

Pour B-Tree

The optimizer can also use a B-tree index for queries involving the pattern matching operators LIKE, ILIKE, ~, and ~*, if the pattern is anchored to the beginning of the string, e.g., col LIKE 'foo%' or col ~ '^foo', but not col LIKE '%bar'.

Traduction
L'optimiseur peut aussi utiliser un index B-tree pour des requêtes qui utilisent les opérateurs de recherche de motif LIKE, ILIKE, ~, et ~*, si le motif est au début de la chaîne à rechercher. Par exemple: col LIKE 'foo%' ou col ~ '^foo', mais pas col LIKE '%bar'

Pour R-Tree

R-tree indexes are suited for queries on spatial data.

Traduction
Les index R-tree sont adaptés aux requêtes sur des données spatiales

Donc dans ton cas il faut que tu utilise un index de type B-Tree, et ne pas oublier de faire un explain plan pour vérifier qu'il soit utiliser correctement.
Sans oublier de lire la doc qui a été traduite en français

KrysKool

[localhost]

resalut, ok j'avoue que je me suis trompé en lisant la doc (oui je l'ai lu )

voila je vous donne des snapshot d'une ANALYSE d'un SELECT avant et aprés la création d'index et apparament, l'index a ralentit la selection :

voici mon SELECT :

Code :

SELECT "MsgId","Content" AS content,"TimeCreated" AS tc,"SOA" AS oa FROM sms_mo WHERE ((LOWER("Content") LIKE 'rm%')) AND (("DA"='87000')) ORDER BY "MsgId" ASC

Avant :

Sort (cost=10633.90..10633.92 rows=6 width=99)

Sort Key: "MsgId"

-> Seq Scan on sms_mo (cost=0.00..10633.83 rows=6 width=99)

Filter: ((lower("Content") ~~ 'rm%'::text) AND (("DA")::text = '87000'::text))

4 row(s)

Total runtime: 83.650 ms

maintenant je fais :

Code :

CREATE INDEX sms_mo_content ON sms_mo USING BTREE (LOWER("Content"));

et revoila mon select :

Sort (cost=8241.19..8241.22 rows=12 width=99)

Sort Key: "MsgId"

-> Index Scan using sms_mo_content on sms_mo (cost=0.01..8240.97 rows=12 width=99)

Index Cond: ((lower("Content") >= 'rm'::text) AND (lower("Content") < 'rn'::text))

Filter: ((lower("Content") ~~ 'rm%'::text) AND (("DA")::text = '87000'::text))

5 row(s)

Total runtime: 211.154 ms

alors je suis passé de 83.650 ms à 211.154 ms ce qui est enorme.

[kryskool]

Euh y'a quelque chose de bizzare, avant de crée ton index ton select te ramene 4 lignes, et après sa création il t'en ramene 5,

donc pour ma part tu ne peux pas comparer tes 2 select puis que tu as inseré d'autre enregistrement dans la base entre temps

ensuite pourquoi met tu le nom de tes champs entre " ", tu ne fais plus de SQL Server tu es sous PostgreSQL, donc pour simplifier la vie, tu écrit tes champ en minuscule, ainsi que le nom de tes tables
et vire les (( ... )) dans ton where, également vire le LOWER dans la création de ton index

Si tu utilise PHP, fais ton lower avec une fonction PHP, c'est beaucoup plus rapide.

Y'aurait d'autre chose a dire, commence par ces conseils.

KrysKool

[localhost]

ok alors,
pour les quotes ", mes reqqueuetes ne sont acceptés que si j'entoures les colonnes par des double quotes et les valeurs par des simple quotes, peut etre que c'est dans la config de la base mais peu importe.
je n'ai pas inséré d'autrs enregistrement, les deux select ont été fait sur la meme table.
5 row veut dire que le resultat de explain est affiché sur 5 lignes de meme pour 4 rows.
a mon avis enlever les ((..)) ne touchera pas a la performance du moment que les deux tests sont faits avec le meme select.
je ne peux pas utiliser une fonction lower dans mon code, je voudrais avoir le lower de la colonne elle meme et non pas de la valeur a comparer.

[kryskool]

oh quel belle uzine a gaz

je te conseille d'éviter d'avoir plusieur champ avec le meme nom et seulement différencié avec des majuscules minuscules. je prend toujours de s minuscules quand je crée mes tables

exact pour les rows j'était pas reveillé

vire les (( )) elle ne serve a rien

je ne peux pas utiliser une fonction lower dans mon code, je voudrais avoir le lower de la colonne elle meme et non pas de la valeur a comparer

c'est la que tu as des pertes de performance.

KrysKool

[djibril]

effectue aussi

Code :

un vacuum et analyze

de ta base de données.
ça peut etre utile.

[chanmix]

Je vais peut être dire une bétise mais le coup de faire plein de LOWER à chaque fois n'est peut être pas utile (et notamment dans l'index). Je m'explique :

N'est il pas possible de faire un LIKE qui se fout de la casse (ILIKE) et n'afficher que le résultat en minuscule ?

Code :

1
2
 
SELECT lower(MsgId,Content) AS content,TimeCreated AS tc,SOA AS oa FROM sms_mo WHERE content ILIKE 'rm%' AND DA='87000' ORDER BY MsgId ASC;

Le résultat est il meilleur ?