Traté de crear varios tipos de índices en la misma columna de mi tabla para ver cómo se comparan, todos los pude crear rápidamente pero no un índice HASH. Leí sobre ellos cómo mejoraron en las versiones recientes de Postgres, pero supongo que aún pueden tener algunas limitaciones.
Mi tabla tiene 96 477 996 filas y la columna en la que probé índices es un tipo de número entero.
CREATE INDEX gpps_brin_index ON cdc_s5_gpps_ind USING brin (id_transformace) WITH (pages_per_range='256'); --27s 879ms -- drop index gpps_brin_index; CREATE INDEX gpps_gin_index ON cdc_s5_gpps_ind USING gin (id_transformace); -- 1m 13s -- drop index gpps_gin_index; CREATE INDEX gpps_btree_index ON cdc_s5_gpps_ind (id_transformace); -- 45s 744ms -- drop index gpps_btree_index;Pero el índice hash no terminó incluso después de 38 minutos
CREATE INDEX gpps_hash_index ON cdc_s5_gpps_ind USING hash (id_transformace);Traté de configurar la memoria de trabajo en 4 GB para ver si hace alguna diferencia, pero no hubo cambios.
Entonces, si se crean otros índices en un minuto, probablemente haya algún problema con el índice hash. Traté de crearlo en una tabla pequeña y terminó rápidamente, por lo que parece que probablemente haya algunas limitaciones de tamaño cuando el índice de tamaño de cierta tabla comience a tener problemas. Alguien me puede confirmar esto o hay algo que me estoy perdiendo.
EDITAR: como lo explicó @jjanes, probé el índice hash en otra columna que solo tiene valores únicos (id de fila) y el índice HASH se creó en 2m34s.
PostgreSQL 12.3 en x86_64-pc-linux-gnu, compilado por gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64 bits
Digamos que tiene 100 valores distintos, que ocurren aproximadamente 1 millón de veces cada uno. Entonces, solo se pueden ocupar 100 cubos. Una vez que cada id_transformance tiene su propio depósito, no importa cuántas veces más divida un depósito, todas las filas siguen una ruta de la división y terminan nuevamente en el mismo depósito. Entonces, cada cubo ocupado tendrá una larga lista de páginas de desbordamiento. Y no creo que haya un camino rápido para llegar al final de dicha lista, tiene que recorrerlo cada vez que necesite agregar un registro al final.
Por lo tanto, obtiene un rendimiento de compilación degenerado cuando tiene una gran cantidad de filas, pero solo una pequeña cantidad de valores distintos. Este no es un problema general con tablas grandes, pero es específico de esta situación.
Esto posiblemente podría mejorarse para la creación de índices masivos mediante la creación de una ruta rápida al final de la lista de páginas de desbordamiento o al depósito utilizado más recientemente, pero incluso si lo fuera, no creo que este tipo de índice sea adecuado para este tipo de datos.