recherche sur les rpremier caractères d'une chaine sous postgreSQL
8 réponses
Etienne SOBOLE
Salut, j'ai besoin de faire un truc genre
select * from member where nom ~* 'bo.*'
afin de rechercher tous les noms commencant par bo
Comment faire ça le plus rapidement possible.
C'est à dire, y a t'il une différence entre l'utilisation d'un champ text ou
d'un champ varchar (ou autre)
Y a t-il moyen de crée un index ou quelques chose sur le champ nom pour
accélerer la recherche ?
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire
Veuillez sélectionner un problème
Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
Joe
Etienne SOBOLE a écrit :
Salut, j'ai besoin de faire un truc genre
select * from member where nom ~* 'bo.*'
bizarre cette syntaxe ! un truc du style like 'BO%'
afin de rechercher tous les noms commencant par bo Comment faire ça le plus rapidement possible.
C'est à dire, y a t'il une différence entre l'utilisation d'un champ text ou d'un champ varchar (ou autre) Y a t-il moyen de crée un index ou quelques chose sur le champ nom pour accélerer la recherche ?
oui, un index sur member
Etienne SOBOLE a écrit :
Salut, j'ai besoin de faire un truc genre
select * from member where nom ~* 'bo.*'
bizarre cette syntaxe !
un truc du style like 'BO%'
afin de rechercher tous les noms commencant par bo
Comment faire ça le plus rapidement possible.
C'est à dire, y a t'il une différence entre l'utilisation d'un champ text ou
d'un champ varchar (ou autre)
Y a t-il moyen de crée un index ou quelques chose sur le champ nom pour
accélerer la recherche ?
bizarre cette syntaxe ! un truc du style like 'BO%'
afin de rechercher tous les noms commencant par bo Comment faire ça le plus rapidement possible.
C'est à dire, y a t'il une différence entre l'utilisation d'un champ text ou d'un champ varchar (ou autre) Y a t-il moyen de crée un index ou quelques chose sur le champ nom pour accélerer la recherche ?
oui, un index sur member
Etienne SOBOLE
oui, un index sur member
hum. sur "nom" tu veux dire, mais un index va indéxé le mot en entier ou bien la recherche de début de mot va fonctionner avec l'index ?
Sinon j'avais vu un truc qui consistait a transformer une chaine en valeur 64 bit. on peut alors coder jusqu'a 13 caractères, ce qui devrait deja largement suffir a mon besoin...
Etienne
oui, un index sur member
hum. sur "nom" tu veux dire, mais un index va indéxé le mot en entier ou
bien la recherche de début de mot va fonctionner avec l'index ?
Sinon j'avais vu un truc qui consistait a transformer une chaine en valeur
64 bit.
on peut alors coder jusqu'a 13 caractères, ce qui devrait deja largement
suffir a mon besoin...
hum. sur "nom" tu veux dire, mais un index va indéxé le mot en entier ou bien la recherche de début de mot va fonctionner avec l'index ?
Sinon j'avais vu un truc qui consistait a transformer une chaine en valeur 64 bit. on peut alors coder jusqu'a 13 caractères, ce qui devrait deja largement suffir a mon besoin...
Etienne
Patrick Mevzek
Le Mon, 13 Aug 2007 11:11:02 +0200, Joe a écrit:
select * from member where nom ~* 'bo.*'
bizarre cette syntaxe !
Non, normal. PostgreSQL supporte les expressions régulières avec l'opérateur ~* ce qui est bien plus puissant que LIKE, même si ce dernier pourrait faire l'affaire sur ce cas précis.
-- Patrick Mevzek . . . . . . . . . . . . . . Dot and Co <http://www.dotandco.net/> <http://www.dotandco.com/> Dépêches sur le nommage <news://news.dotandco.net/dotandco.info.news>
Le Mon, 13 Aug 2007 11:11:02 +0200, Joe a écrit:
select * from member where nom ~* 'bo.*'
bizarre cette syntaxe !
Non, normal. PostgreSQL supporte les expressions régulières
avec l'opérateur ~* ce qui est bien plus puissant que LIKE, même si
ce dernier pourrait faire l'affaire sur ce cas précis.
--
Patrick Mevzek . . . . . . . . . . . . . . Dot and Co
<http://www.dotandco.net/> <http://www.dotandco.com/>
Dépêches sur le nommage <news://news.dotandco.net/dotandco.info.news>
Non, normal. PostgreSQL supporte les expressions régulières avec l'opérateur ~* ce qui est bien plus puissant que LIKE, même si ce dernier pourrait faire l'affaire sur ce cas précis.
-- Patrick Mevzek . . . . . . . . . . . . . . Dot and Co <http://www.dotandco.net/> <http://www.dotandco.com/> Dépêches sur le nommage <news://news.dotandco.net/dotandco.info.news>
Patrick Mevzek
Le Mon, 13 Aug 2007 10:45:05 +0200, Etienne SOBOLE a écrit:
select * from member where nom ~* 'bo.*'
afin de rechercher tous les noms commencant par bo Comment faire ça le plus rapidement possible.
C'est à dire, y a t'il une différence entre l'utilisation d'un champ text ou d'un champ varchar (ou autre)
Non, ce dernier est géré comme text par PostgreSQL.
Y a t-il moyen de crée un index ou quelques chose sur le champ nom pour accélerer la recherche ?
L'attribut nom peut être indexé, si les valeurs ne dépasse pas une certaine taille (dans les 3000 caractères de mémoire). L'index ne sera utilisé (éventuellement) que sur ce genre de recherches (le début de la valeur de l'attribut, vous feriez bien d'ancrer d'ailleurs avec ^ dans l'expression régulière)
Maintenant, il y a d'autres pistes - si vous cherchez au début toujours la même taille (exemple : les 2 premiers caractères), il pourrait être intéressant de dénormaliser un peu, et de stocker ces 2 caractères à part, de les indexer, et de faire une recherche d'égalité - on peut aussi convertir dans un autre « format » comme vous le dites après, mais attention aux problèmes d'encodage et de longueur - plus généralement, pour les recherches « en texte libre » il y a le module tsearch2 qui fournit plein d'outils optimisés pour cela.
-- Patrick Mevzek . . . . . . . . . . . . . . Dot and Co <http://www.dotandco.net/> <http://www.dotandco.com/> Dépêches sur le nommage <news://news.dotandco.net/dotandco.info.news>
Le Mon, 13 Aug 2007 10:45:05 +0200, Etienne SOBOLE a écrit:
select * from member where nom ~* 'bo.*'
afin de rechercher tous les noms commencant par bo
Comment faire ça le plus rapidement possible.
C'est à dire, y a t'il une différence entre l'utilisation d'un champ text ou
d'un champ varchar (ou autre)
Non, ce dernier est géré comme text par PostgreSQL.
Y a t-il moyen de crée un index ou quelques chose sur le champ nom pour
accélerer la recherche ?
L'attribut nom peut être indexé, si les valeurs ne dépasse pas une
certaine taille (dans les 3000 caractères de mémoire).
L'index ne sera utilisé (éventuellement) que sur ce genre de recherches
(le début de la valeur de l'attribut, vous feriez bien d'ancrer d'ailleurs
avec ^ dans l'expression régulière)
Maintenant, il y a d'autres pistes
- si vous cherchez au début toujours la même taille (exemple : les 2
premiers caractères), il pourrait être intéressant de dénormaliser un peu,
et de stocker ces 2 caractères à part, de les indexer, et de faire une
recherche d'égalité
- on peut aussi convertir dans un autre « format » comme vous le dites
après, mais attention aux problèmes d'encodage et de longueur
- plus généralement, pour les recherches « en texte libre » il y a le
module tsearch2 qui fournit plein d'outils optimisés pour cela.
--
Patrick Mevzek . . . . . . . . . . . . . . Dot and Co
<http://www.dotandco.net/> <http://www.dotandco.com/>
Dépêches sur le nommage <news://news.dotandco.net/dotandco.info.news>
Le Mon, 13 Aug 2007 10:45:05 +0200, Etienne SOBOLE a écrit:
select * from member where nom ~* 'bo.*'
afin de rechercher tous les noms commencant par bo Comment faire ça le plus rapidement possible.
C'est à dire, y a t'il une différence entre l'utilisation d'un champ text ou d'un champ varchar (ou autre)
Non, ce dernier est géré comme text par PostgreSQL.
Y a t-il moyen de crée un index ou quelques chose sur le champ nom pour accélerer la recherche ?
L'attribut nom peut être indexé, si les valeurs ne dépasse pas une certaine taille (dans les 3000 caractères de mémoire). L'index ne sera utilisé (éventuellement) que sur ce genre de recherches (le début de la valeur de l'attribut, vous feriez bien d'ancrer d'ailleurs avec ^ dans l'expression régulière)
Maintenant, il y a d'autres pistes - si vous cherchez au début toujours la même taille (exemple : les 2 premiers caractères), il pourrait être intéressant de dénormaliser un peu, et de stocker ces 2 caractères à part, de les indexer, et de faire une recherche d'égalité - on peut aussi convertir dans un autre « format » comme vous le dites après, mais attention aux problèmes d'encodage et de longueur - plus généralement, pour les recherches « en texte libre » il y a le module tsearch2 qui fournit plein d'outils optimisés pour cela.
-- Patrick Mevzek . . . . . . . . . . . . . . Dot and Co <http://www.dotandco.net/> <http://www.dotandco.com/> Dépêches sur le nommage <news://news.dotandco.net/dotandco.info.news>
nobody
Joe a écrit :
Etienne SOBOLE a écrit :
Salut, j'ai besoin de faire un truc genre
select * from member where nom ~* 'bo.*'
bizarre cette syntaxe ! un truc du style like 'BO%'
afin de rechercher tous les noms commencant par bo Comment faire ça le plus rapidement possible.
C'est à dire, y a t'il une différence entre l'utilisation d'un champ text ou d'un champ varchar (ou autre) Y a t-il moyen de crée un index ou quelques chose sur le champ nom pour accélerer la recherche ?
oui, un index sur member
et avec SEQUEL :
select member with nom = 'bo]'
ou même
select member with debutnom = 'bo'
si debutnom a été définie dans le dictionnaire du fichier member comme étant les 2 premiers caracteres de nom
Joe a écrit :
Etienne SOBOLE a écrit :
Salut, j'ai besoin de faire un truc genre
select * from member where nom ~* 'bo.*'
bizarre cette syntaxe !
un truc du style like 'BO%'
afin de rechercher tous les noms commencant par bo
Comment faire ça le plus rapidement possible.
C'est à dire, y a t'il une différence entre l'utilisation d'un champ
text ou d'un champ varchar (ou autre)
Y a t-il moyen de crée un index ou quelques chose sur le champ nom
pour accélerer la recherche ?
oui, un index sur member
et avec SEQUEL :
select member with nom = 'bo]'
ou même
select member with debutnom = 'bo'
si debutnom a été définie dans le dictionnaire du fichier member comme
étant les 2 premiers caracteres de nom
bizarre cette syntaxe ! un truc du style like 'BO%'
afin de rechercher tous les noms commencant par bo Comment faire ça le plus rapidement possible.
C'est à dire, y a t'il une différence entre l'utilisation d'un champ text ou d'un champ varchar (ou autre) Y a t-il moyen de crée un index ou quelques chose sur le champ nom pour accélerer la recherche ?
oui, un index sur member
et avec SEQUEL :
select member with nom = 'bo]'
ou même
select member with debutnom = 'bo'
si debutnom a été définie dans le dictionnaire du fichier member comme étant les 2 premiers caracteres de nom
Etienne SOBOLE
Non le besoin est assez simple. J'utilise un champs autocomplété en dhtml. En gros des que l'utilsateur tape un caractère, une requete est lancé vers le serveur pour lui demander la liste des réponses possibles. donc on n'est donc pas limité à 2 caractères.
Les champs ne sont pas nom plus tres long puisqu'il s'agit de nom de personne, et qu'on peut raisonnablement estimer qu'à partir de 10 caractère on a déjà réduit pas mal la liste.
Ce que je n'ai pas compris dans votre réponse, c'est est ce qu'un index va etre utilisé pour les requetes sur des debut de mots ! Si oui, dans ce cas pas la peine que je m'embete, l'index resoudra mon problème.
Etienne
Non le besoin est assez simple.
J'utilise un champs autocomplété en dhtml.
En gros des que l'utilsateur tape un caractère, une requete est lancé vers
le serveur pour lui demander la liste des réponses possibles.
donc on n'est donc pas limité à 2 caractères.
Les champs ne sont pas nom plus tres long puisqu'il s'agit de nom de
personne, et qu'on peut raisonnablement estimer qu'à partir de 10 caractère
on a déjà réduit pas mal la liste.
Ce que je n'ai pas compris dans votre réponse, c'est est ce qu'un index va
etre utilisé pour les requetes sur des debut de mots !
Si oui, dans ce cas pas la peine que je m'embete, l'index resoudra mon
problème.
Non le besoin est assez simple. J'utilise un champs autocomplété en dhtml. En gros des que l'utilsateur tape un caractère, une requete est lancé vers le serveur pour lui demander la liste des réponses possibles. donc on n'est donc pas limité à 2 caractères.
Les champs ne sont pas nom plus tres long puisqu'il s'agit de nom de personne, et qu'on peut raisonnablement estimer qu'à partir de 10 caractère on a déjà réduit pas mal la liste.
Ce que je n'ai pas compris dans votre réponse, c'est est ce qu'un index va etre utilisé pour les requetes sur des debut de mots ! Si oui, dans ce cas pas la peine que je m'embete, l'index resoudra mon problème.
Etienne
Patrick Mevzek
Le Tue, 14 Aug 2007 01:07:04 +0200, Etienne SOBOLE a écrit:
Ce que je n'ai pas compris dans votre réponse, c'est est ce qu'un index va etre utilisé pour les requetes sur des debut de mots ! Si oui, dans ce cas pas la peine que je m'embete, l'index resoudra mon problème.
Peut-être, peut-être pas.
Un accès indexé n'est pas meilleur _dans tous les cas_. Aussi PostgreSQL, en présence d'index, se base sur les statistiques pour savoir s'il vaut mieux utiliser un index ou non. Le but n'est pas d'utiliser tous les index disponibles, mais d'avoir les meilleures performances, but que l'on atteint parfois en évitant justement les index.
Cela dépend en particulier du volume (en-dessous de quelques milliers de tuples, un accès séquentiel est toujours plus rapide), de la présence de NULL ou non, de la répartition statistique des valeurs, du type de recherches effectuées, de la configuration du SGBDR (les différents coûts), etc.
Après avoir éliminé tous les cas triviaux il n'y a plus trente six possibilités : sur une base _remplie_, créer l'index, et faire des EXPLAIN pour voir le choix de PostgreSQL. S'il s'avère qu'il ne sert à rien, le supprimer, car il a nécessairement un coût négatif en performances pour toutes les mises à jour.
-- Patrick Mevzek . . . . . . . . . . . . . . Dot and Co <http://www.dotandco.net/> <http://www.dotandco.com/> Dépêches sur le nommage <news://news.dotandco.net/dotandco.info.news>
Le Tue, 14 Aug 2007 01:07:04 +0200, Etienne SOBOLE a écrit:
Ce que je n'ai pas compris dans votre réponse, c'est est ce qu'un index va
etre utilisé pour les requetes sur des debut de mots !
Si oui, dans ce cas pas la peine que je m'embete, l'index resoudra mon
problème.
Peut-être, peut-être pas.
Un accès indexé n'est pas meilleur _dans tous les cas_. Aussi PostgreSQL,
en présence d'index, se base sur les statistiques pour savoir s'il vaut
mieux utiliser un index ou non. Le but n'est pas d'utiliser tous les index
disponibles, mais d'avoir les meilleures performances, but que l'on
atteint parfois en évitant justement les index.
Cela dépend en particulier du volume (en-dessous de quelques milliers de
tuples, un accès séquentiel est toujours plus rapide), de la présence de
NULL ou non, de la répartition statistique des valeurs, du type de
recherches effectuées, de la configuration du SGBDR (les différents coûts),
etc.
Après avoir éliminé tous les cas triviaux il n'y a plus trente six
possibilités : sur une base _remplie_, créer l'index, et faire des EXPLAIN
pour voir le choix de PostgreSQL. S'il s'avère qu'il ne sert à rien, le
supprimer, car il a nécessairement un coût négatif en performances pour
toutes les mises à jour.
--
Patrick Mevzek . . . . . . . . . . . . . . Dot and Co
<http://www.dotandco.net/> <http://www.dotandco.com/>
Dépêches sur le nommage <news://news.dotandco.net/dotandco.info.news>
Le Tue, 14 Aug 2007 01:07:04 +0200, Etienne SOBOLE a écrit:
Ce que je n'ai pas compris dans votre réponse, c'est est ce qu'un index va etre utilisé pour les requetes sur des debut de mots ! Si oui, dans ce cas pas la peine que je m'embete, l'index resoudra mon problème.
Peut-être, peut-être pas.
Un accès indexé n'est pas meilleur _dans tous les cas_. Aussi PostgreSQL, en présence d'index, se base sur les statistiques pour savoir s'il vaut mieux utiliser un index ou non. Le but n'est pas d'utiliser tous les index disponibles, mais d'avoir les meilleures performances, but que l'on atteint parfois en évitant justement les index.
Cela dépend en particulier du volume (en-dessous de quelques milliers de tuples, un accès séquentiel est toujours plus rapide), de la présence de NULL ou non, de la répartition statistique des valeurs, du type de recherches effectuées, de la configuration du SGBDR (les différents coûts), etc.
Après avoir éliminé tous les cas triviaux il n'y a plus trente six possibilités : sur une base _remplie_, créer l'index, et faire des EXPLAIN pour voir le choix de PostgreSQL. S'il s'avère qu'il ne sert à rien, le supprimer, car il a nécessairement un coût négatif en performances pour toutes les mises à jour.
-- Patrick Mevzek . . . . . . . . . . . . . . Dot and Co <http://www.dotandco.net/> <http://www.dotandco.com/> Dépêches sur le nommage <news://news.dotandco.net/dotandco.info.news>
Patrick Mevzek
Le Wed, 15 Aug 2007 16:51:58 +0200, Fred Brouard - SQLpro a écrit:
Après avoir éliminé tous les cas triviaux il n'y a plus trente six possibilités : sur une base _remplie_, créer l'index, et faire des EXPLAIN pour voir le choix de PostgreSQL. S'il s'avère qu'il ne sert à rien, le supprimer, car il a nécessairement un coût négatif en performances pour toutes les mises à jour.
non, pas toujours. En effet un index est primodial pour savoir ou placer la ligne dans la table : 1) pour les index cluster 2) pour les contraintes de PK ou d'UK....
Danc ces cas, cela accélère le traitement des insert...
Merci pour la paraphrase c'est bien ce que je disais : faire des tests sur une base remplie, et si aucune requête SQL (qui sera utilisée après en production) ne montre un usage de l'index, alors il est inutile.
Stricto sensu un index n'est pas nécessaire pour une contrainte d'unicité, il peut permettre de la vérifier plus rapidement. Mais pas sur une petite table par exemple. PostgresSQL créé automatiquement un index sur une clef primaire (et plus généralement sur tout attribut avec la propriété UNIQUE) mais il peut être supprimé, il n'est pas essentiel à la fonctionnalité d'unicité. Idem pour les clefs étrangères, cela peut accélérer les recherches, mais pas forcément toujours. D'où la nécessité de faire des EXPLAIN.
-- Patrick Mevzek . . . . . . . . . . . . . . Dot and Co <http://www.dotandco.net/> <http://www.dotandco.com/> Dépêches sur le nommage <news://news.dotandco.net/dotandco.info.news>
Le Wed, 15 Aug 2007 16:51:58 +0200, Fred Brouard - SQLpro a écrit:
Après avoir éliminé tous les cas triviaux il n'y a plus trente six
possibilités : sur une base _remplie_, créer l'index, et faire des EXPLAIN
pour voir le choix de PostgreSQL. S'il s'avère qu'il ne sert à rien, le
supprimer, car il a nécessairement un coût négatif en performances pour
toutes les mises à jour.
non, pas toujours. En effet un index est primodial pour savoir ou placer
la ligne dans la table :
1) pour les index cluster
2) pour les contraintes de PK ou d'UK....
Danc ces cas, cela accélère le traitement des insert...
Merci pour la paraphrase c'est bien ce que je disais : faire des tests sur
une base remplie, et si aucune requête SQL (qui sera utilisée après en
production) ne montre un usage de l'index, alors il est inutile.
Stricto sensu un index n'est pas nécessaire pour une contrainte d'unicité,
il peut permettre de la vérifier plus rapidement. Mais pas sur une petite
table par exemple. PostgresSQL créé automatiquement un index sur une clef
primaire (et plus généralement sur tout attribut avec la propriété UNIQUE)
mais il peut être supprimé, il n'est pas essentiel à la fonctionnalité
d'unicité.
Idem pour les clefs étrangères, cela peut accélérer les recherches, mais
pas forcément toujours. D'où la nécessité de faire des EXPLAIN.
--
Patrick Mevzek . . . . . . . . . . . . . . Dot and Co
<http://www.dotandco.net/> <http://www.dotandco.com/>
Dépêches sur le nommage <news://news.dotandco.net/dotandco.info.news>
Le Wed, 15 Aug 2007 16:51:58 +0200, Fred Brouard - SQLpro a écrit:
Après avoir éliminé tous les cas triviaux il n'y a plus trente six possibilités : sur une base _remplie_, créer l'index, et faire des EXPLAIN pour voir le choix de PostgreSQL. S'il s'avère qu'il ne sert à rien, le supprimer, car il a nécessairement un coût négatif en performances pour toutes les mises à jour.
non, pas toujours. En effet un index est primodial pour savoir ou placer la ligne dans la table : 1) pour les index cluster 2) pour les contraintes de PK ou d'UK....
Danc ces cas, cela accélère le traitement des insert...
Merci pour la paraphrase c'est bien ce que je disais : faire des tests sur une base remplie, et si aucune requête SQL (qui sera utilisée après en production) ne montre un usage de l'index, alors il est inutile.
Stricto sensu un index n'est pas nécessaire pour une contrainte d'unicité, il peut permettre de la vérifier plus rapidement. Mais pas sur une petite table par exemple. PostgresSQL créé automatiquement un index sur une clef primaire (et plus généralement sur tout attribut avec la propriété UNIQUE) mais il peut être supprimé, il n'est pas essentiel à la fonctionnalité d'unicité. Idem pour les clefs étrangères, cela peut accélérer les recherches, mais pas forcément toujours. D'où la nécessité de faire des EXPLAIN.
-- Patrick Mevzek . . . . . . . . . . . . . . Dot and Co <http://www.dotandco.net/> <http://www.dotandco.com/> Dépêches sur le nommage <news://news.dotandco.net/dotandco.info.news>
