que - postgresql para principiantes

Sobre el índice agrupado en postgres

Question

Estoy usando psql para acceder a una base de datos postgres. Al ver los metadatos de una tabla, ¿hay alguna forma de ver si un índice de una tabla es un índice agrupado?

Escuché que la CLAVE PRIMARIA de una tabla se asocia automáticamente con un índice agrupado, ¿es cierto?

Answer 1

¿Hay alguna manera de ver si un índice de una tabla es un índice agrupado?

PostgreSQL no tiene un índice agrupado, por lo que no podrá verlos.

Escuché que la CLAVE PRIMARIA de una tabla se asocia automáticamente con un índice agrupado, ¿es cierto?

No, eso no es cierto (ver arriba)

Puede agrupar manualmente una tabla a lo largo de un índice, pero esto no es nada que se mantendrá automáticamente (como, por ejemplo, con los índices agrupados de SQL Server).

Para obtener más detalles, consulte la descripción del comando CLUSTER en el manual.

Answer 2

En PostgreSQL, el atributo agrupado se mantiene en los metadatos del índice correspondiente, en lugar de la relación en sí. Es el atributo pg_index en el catálogo pg_index . Sin embargo, tenga en cuenta que las relaciones de agrupamiento en clúster dentro de postgres es una acción que se realiza una sola vez: incluso si el atributo es verdadero, las actualizaciones de la tabla no mantienen la naturaleza ordenada de los datos. Hasta la fecha, el mantenimiento automático de la agrupación de datos sigue siendo un elemento TODO popular.

A menudo hay confusión entre los índices agrupados e integrados , particularmente porque los libros de texto populares usan nombres en conflicto, y la terminología es diferente de nuevo en los manuales de postgres y el servidor SQL (para nombrar solo dos). Cuando hablo de un índice integrado (también llamado índice principal o índice primario ) me refiero a uno en el que los datos de relación están contenidos en las hojas del índice, en oposición a un índice externo o secundario en el que las hojas contienen entradas de índice que apuntan a la tabla de registros. El primer tipo siempre está necesariamente agrupado. Desafortunadamente, postgres solo soporta este último tipo. De todos modos, el hecho de que un índice integrado (primario) siempre esté agrupado puede haber dado lugar a la creencia de que "una CLAVE PRIMARIA de una tabla se asocia automáticamente con un índice agrupado". Las dos afirmaciones suenan similares, pero son diferentes.

Answer 3

PostgreSQL no tiene implementación directa del índice CLUSTER como Microsoft SQL Server.

Referencia tomada de este blog:

En PostgreSQL, tenemos un comando CLUSTER que es similar a Cluster Index.

Una vez que haya creado su clave principal de la tabla o cualquier otro índice, puede ejecutar el comando CLUSTER especificando ese nombre del índice para lograr el orden físico de los datos de la tabla.

Cuando una tabla está agrupada, se reordena físicamente según la información del índice. La agrupación en clúster es una operación que se realiza una sola vez: cuando la tabla se actualiza posteriormente, los cambios no se agrupan. Es decir, no se intenta almacenar filas nuevas o actualizadas de acuerdo con su orden de índice.

Sintaxis de Cluster:

La primera vez que debe ejecutar CLUSTER usando el nombre del índice.

CLUSTER table_name USING index_name;

Agrupa la tabla:

Una vez que haya ejecutado CLUSTER con Índice, la próxima vez debe ejecutar solo TABLA DE CLÚSTER porque sabe qué índice ya está definido como CLÚSTER.

CLUSTER table_name;

Answer 4

Si desea saber si una tabla dada es CLUSTER ed usando SQL, puede usar la siguiente consulta para mostrar el índice que se está utilizando (probado en las versiones 9.5 y 9.6 de Postgres):

SELECT
  i.relname AS index_for_cluster
FROM
  pg_index AS idx
JOIN
  pg_class AS i
ON
  i.oid = idx.indexrelid
WHERE
  idx.indisclustered
  AND idx.indrelid::regclass = ''your_table_name''::regclass;

Answer 5

Tenga en cuenta que PostgreSQL usa el término "índice agrupado" para usar algo vagamente similar pero muy diferente a SQL Server.

Si un índice en particular ha sido designado como el índice de agrupación en clúster para una tabla, entonces el comando /d psql indicará el índice agrupado, por ejemplo,

Indexes:
    "timezone_description_pkey" PRIMARY KEY, btree (timezone) CLUSTER

PostgreSQL no nomina índices como índices agrupados por defecto. Tampoco organiza automáticamente los datos de la tabla para que se correlacionen con el índice agrupado, incluso cuando están nominados: el comando CLUSTER debe utilizarse para reorganizar los datos de la tabla.