SQL indexation sur varchar

J'ai une table dont les colonnes sont varchar(50) et un float. J'ai besoin de (très rapidement) de chercher à obtenir le flotteur associé à une chaîne donnée. Même avec l'indexation, c'est plutôt lent.

Je sais, cependant, que chaque chaîne est associée à un nombre entier, dont je sais qu'au moment de la recherche, de sorte que chaque chaîne correspond à un entier unique, mais chaque entier ne correspond pas à une chaîne unique. On pourrait penser que c'est une structure en arbre.

Il n'y a rien à gagner par l'ajout de cet entier à la table, l'indexation sur elle, et à l'aide d'une requête comme:

SELECT floatval FROM mytable WHERE phrase=givenstring AND assoc=givenint

C'est Postgres, et si vous ne pourriez pas dire, j'ai très peu d'expérience avec les bases de données.

InformationsquelleAutor alex | 2010-04-13
  1. 18

    Touches VARCHAR les colonnes peuvent être très longues, ce qui entraîne moins d'enregistrements par page et plus de profondeur (plus de niveaux dans la B-Tree). Plus l'index également augmenter le cache miss ratio.

    Combien de chaînes au moyen de la carte pour chaque entier?

    Si il y a relativement peu, vous pouvez créer un index uniquement sur colonne de type integer et PostgreSQL va faire la fin de filtrage sur les enregistrements:

    CREATE INDEX ix_mytable_assoc ON mytable (assoc);

SELECT  floatval
FROM    mytable
WHERE   assoc = givenint
        AND phrase = givenstring

    Vous pouvez également envisager de créer un index sur la corde de hachages:

    CREATE INDEX ix_mytable_md5 ON mytable (DECODE(MD5(phrase), 'HEX'));

SELECT  floatval
FROM    mytable
WHERE   DECODE(MD5(phrase), 'HEX') = DECODE(MD5('givenstring'), 'HEX')
        AND phrase = givenstring -- who knows when do we get a collision?

    Chaque hachage n'est 16 octets de long, de sorte que l'indice clés seront beaucoup plus courts tout en préservant la sélectivité presque parfaitement.

    • La comparaison de l'indice de touches sont aussi beaucoup plus cher avec varchar, car ils sont des paramètres régionaux de conscience. L'index entier sera certainement beaucoup plus rapide que les autres options.
    • la comparaison doit être faite uniquement log(n) fois, donc je ne dirais pas ce "beaucoup" plus cher, mais vous avez raison, il ne ajouter un peu de CPU cycles.
    InformationsquelleAutor Quassnoi
  2. 4

    Je le recommande tout simplement un index de hachage:

    create index mytable_phrase_idx on mytable using hash(phrase);

    De cette façon, les requêtes comme

    select floatval from mytable where phrase='foo bar';

    sera très rapide. Test ceci:

    create temporary table test ( k varchar(50), v float);
insert into test (k, v) select 'foo bar number '||generate_series(1,1000000), 1;
create index test_k_idx on test using hash (k);
analyze test;
explain analyze select v from test where k='foo bar number 634652';
     PLAN DE REQUÊTE 
----------------------------------------------------------------------------------------------------------------- 
Analyse d'Index à l'aide de test_k_idx test (cost=0.00..8.45 rows=1 width=8) (actual time=0.201..0.206 rows=1 loops=1) 
Index Cond: ((k)::text = 'foo bar nombre 634652'::text) 
Total runtime: 0.265 ms 
(3 lignes)
    • Dans ce test de la table, je ne vois pas de différence entre l'arbre et le hachage.
    InformationsquelleAutor Tometzky
  3. 0

    Réponse courte: oui, il y aura beaucoup à gagner. Au moins aussi longtemps que vous n'avez pas beaucoup de mises à jour, mais il est très probable que la surcharge de même, il ne sera pas perceptible.

    InformationsquelleAutor Magnus Hagander
  4. -1

    En déclarant un indice sur (phrase, assoc, floatval), vous obtiendrez un "index de couverture", qui permet à la requête publié dans la question à effectuée sans même l'accès à la table. En supposant que ce soit phrase ou assoc seul est très sélective (pas de nombre de lignes qui partagent la même valeur pour le champ), la création d'un index sur ce champ devrait à lui seul des rendements presque les mêmes performances.

    Généralement, vous aurez besoin de limiter le nombre d'index pour le plus petit ensemble qui obtient vos requêtes fréquentes jusqu'à la performance souhaitée. Pour chaque indice vous ajouter à une table, vous payez une partie de l'espace disque, mais plus important encore, vous avez à payer le prix d'avoir le SGBD faire plus de travail sur chaque INSERT dans la table.

    • PostgreSQL n'a pas la couverture des index, de sorte que l'indice serait certainement une perte.
    • Donc, même si un indice couvre tous les champs nécessaires pour répondre à une requête, PostgreSQL aura pour accéder à la table pour récupérer les valeurs? Avez-vous une référence pour cela? Je suis un peu curieux de savoir pourquoi 🙂
    • Que de 9,2, PostgreSQL a maintenant indice seule scans: wiki.postgresql.org/wiki/Index-only_scans#Covering_indexes Détails dans le haut de ce post pour expliquer pourquoi il n'a pas précédemment: Avec PostgreSQL index, "il n'est pas directement possible de vérifier si tout n-uplet est visible à la transaction en cours".
    InformationsquelleAutor Jørn Schou-Rode
  5. -1

    Il ne pouvait pas de mal à essayer d'ajouter de l'int et de faire de votre index sur int, varchar et sont float - ce serait couvrant et assez efficace - ne sais pas si Postgres a des colonnes incluses - si ce n'est pas simplement l'ajouter à l'index lui-même.

    Il existe plusieurs autres techniques que vous pouvez regarder dans (je ne suis pas familier avec tous les Postgres, donc je vais leur donner par le nom du Serveur SQL):

    Vues indexées - vous peut effectivement se matérialiser un point de vue qui rejoint plusieurs tables - de sorte que vous pouvez joindre votre varchar pour votre int et votre index sur et varchar int et float

    Des colonnes incluses - vous pouvez inclure des colonnes dans un index pour s'assurer que l'index couvre - c'est à dire avoir un indice sur varchar inclure (float) - si l'index n'est pas couvrant, l'optimiseur de requête est toujours d'avoir à utiliser l'index, puis faire une recherche de signet pour obtenir les données restantes.

    • PostgreSQL ne prend pas en charge les vues indexées ou des colonnes incluses, mais il prend en charge la fonction de base des index (vous n'avez pas à se matérialiser, une expression de l'avoir indexé).
    InformationsquelleAutor Cade Roux