database - pgsql - índice postgresql en la columna de cadena

postgresql jsonb example

Question

Por ejemplo, tengo una tabla ResidentInfo , y en esta tabla tengo restricciones únicas de HomeAddress , que es del tipo VARCHAR . Para futuras consultas, voy a agregar un índice en esta columna. La consulta solo tendrá la operación = , y usaré el patrón B-TREE ya que el patrón Hash no se recomienda actualmente.

Pregunta: Desde la vista de eficiencia, usando B-TREE, ¿cree que debería agregar una nueva columna con los números 1,2,3 ...., N correspondiente a diferentes direcciones de casa, y en lugar de agregar un índice en HomeAddress , debo agregar un índice? en la columna de números?

Hago esta pregunta porque no sé cómo funciona el índice.

Answer 1

En Postgres, se impone una restricción única al mantener un índice único en el campo, por lo que ya está cubierto.

En el caso de que decida que la restricción única sobre la dirección es mala (que, honestamente, es: ¿qué hace un cónyuge que crea una cuenta separada? ¿Sobre pisos compartidos? Etc.), puede crear una así:

create index on ResidentInfo (HomeAddress);

Answer 2

Para verificaciones de igualdad simples ( = ), un índice B-Tree en una columna varchar o de text es simple y la mejor opción. Sin duda, ayuda mucho el rendimiento.

Por supuesto, un índice B-Tree en un integer simple funciona mejor. Para empezar, comparar valores integer simples es un poco más rápido. Pero lo que es más importante, el rendimiento también es una función del tamaño del índice. Una columna más grande significa menos filas por página de datos, significa que se deben leer más páginas ...

Como la HomeAddress es difícilmente única, no es una buena clave primaria natural. Recomiendo encarecidamente utilizar una clave primaria sustituta en su lugar. Una columna en serial es la opción obvia para eso. Su único propósito es tener una clave principal simple y rápida para trabajar.

Si tiene otras tablas que hacen referencia a dicha tabla, esto se vuelve aún más eficiente. En lugar de duplicar una cadena larga para la columna de clave externa, solo necesita los 4 bytes para una columna entera. Y no es necesario que las actualizaciones estén en cascada, ya que una dirección está obligada a cambiar, mientras que un pk en sustitución puede permanecer igual (pero no tiene que hacerlo, por supuesto).

Tu mesa podría verse así:

CREATE TABLE resident (
   resident_id serial PRIMARY KEY
  ,address text NOT NULL
   -- more columns
);

CREATE INDEX resident_adr_idx ON resident(address);

Esto da lugar a dos índices B-Tree. Un índice único en resident_id y un índice simple en la address .

Más sobre los índices en el manual .
Postgres ofrece muchas opciones, pero no necesita más para este simple estuche.