PostgreSQL: ¡Analícelo primero!

Recientemente escribí un artículo sobre la comparación de los índices Hash y B-tree . Desafortunadamente, cometí un error y ahora es el momento de corregirlo.

En este artículo, le mostraré el script PL/pgSQL con un problema importante que es difícil de detectar. Luego, les mostraré mi historia de depuración, incluidas algunas de las partes internas de PostgreSQL. Y por último, pero no menos importante, la lección aprendida y los consejos para evitar los mismos errores.

Veamos el procedimiento incorrecto .

CREATE OR REPLACE FUNCTION test(length integer, count numeric)
RETURNS TABLE (
    sample_length integer,
    unique_ratio decimal, -- in percentage
    hash_index_size bigint, -- in kilobytes
    btree_index_size bigint, -- in kilobytes
    column_size bigint, -- in kilobytes
    hash_select_query decimal, -- in milliseconds
    btree_select_query decimal, -- in milliseconds
    hash_insert_query decimal, -- in milliseconds
    btree_insert_query decimal -- in milliseconds
) AS
$$
DECLARE
    strings varchar[];
BEGIN
    CREATE TABLE IF NOT EXISTS hash_table(example varchar);
    CREATE TABLE IF NOT EXISTS btree_table(example varchar);

    INSERT INTO hash_table (SELECT random_string(length) FROM generate_series(1, count));
    INSERT INTO btree_table (SELECT example FROM hash_table);

    CREATE INDEX IF NOT EXISTS hash_index ON hash_table USING hash(example);
    CREATE INDEX IF NOT EXISTS btree_index ON btree_table USING btree(example);

    ANALYSE hash_table;
    ANALYSE btree_table;

    strings := array_agg(random_string(length)) FROM generate_series(1, 100);

    RETURN QUERY
    SELECT (SELECT length(example) FROM hash_table LIMIT 1),
        round(count(DISTINCT example)::decimal / count(*) * 100, 2) AS unique_ratio,
       pg_relation_size('hash_index') / 1024 AS hash_index_size,
       pg_relation_size('btree_index') / 1024 AS btree_index_size,
       pg_table_size('hash_table') / 1024 AS column_size,
       benchmark('SELECT example FROM hash_table WHERE example = $1', strings) AS hash_select_query,
       benchmark('SELECT example FROM btree_table WHERE example = $1', strings) AS btree_select_query,
       benchmark('INSERT INTO hash_table VALUES($1)', strings) AS hash_insert_query,
       benchmark('INSERT INTO btree_table VALUES($1)', strings) AS btree_insert_query
    FROM hash_table;

    DROP TABLE IF EXISTS hash_table;
    DROP TABLE IF EXISTS btree_table;
END
$$ LANGUAGE plpgsql;

¿Ves algo sospechoso? yo tampoco Antes de decir lo que está mal aquí, recordemos juntos la definición del índice Hash.

Los índices hash almacenan un código hash de 32 bits derivado del valor de la columna indexada.

¿Estás siguiendo ahora a dónde voy?

Si el índice hash almacena solo índices hash de valores indexados, ¿por qué el tamaño del índice difiere para cadenas con diferentes longitudes? ¿Por qué sucede esto?

Comenzaremos nuestra investigación ejecutando solo la parte a continuación de nuestro script inicial y verificaremos algunas estadísticas usando la pageinspectextensión.

CREATE EXTENSION pageinspect;

CREATE TABLE IF NOT EXISTS hash_table(example varchar);

-- For the research, we will use 10.000 strings with a length of 1024 characters.
INSERT INTO hash_table (
  SELECT random_string(1024) FROM generate_series(1, 10000)
);

CREATE INDEX IF NOT EXISTS hash_index ON hash_table USING hash(example);

SELECT * FROM hash_metapage_info(get_raw_page('hash_index', 0));

      
                

                    
                    
                
            

• ffactordetermina cuándo un índice debe asignar más espacio para los datos guardados.
• maxbucketmuestra el número actual de cubos asignados (una lista de códigos hash ordenados con punteros a filas en la tabla).

Curiosamente, en nuestro caso, el índice creado tiene un valor predeterminado ffactorigual a 307 ( 75% del máximo 409 ) con maxbucketigual a 639 . Pero, ¿por qué tantos cubos si solo indexamos 10.000 filas?

Ahora, usando la pageinspectextensión, veamos cuántas filas (tuplas) hay en cada cubo.

SELECT (hash_page_stats(get_raw_page('hash_index', generate_series))).* 
FROM generate_series(1, 10);

      
                

                    
                    
                
            

Aquí hay una pieza interesante de código.

ffactor = HashGetTargetPageUsage(rel) / item_width;
/* keep to a sane range */
if (ffactor < 10)
  ffactor = 10;

Ejecutemos este código y veamos si detectamos alguna diferencia.

CREATE TABLE hash1_table(example varchar);
CREATE TABLE hash2_table(example varchar);

INSERT INTO hash1_table (SELECT random_string(1024) FROM generate_series(1, 10000));
INSERT INTO hash2_table (SELECT example FROM hash_table1);

SELECT relname, n_tup_ins, n_live_tup, n_ins_since_vacuum 
FROM pg_stat_user_tables WHERE relname IN ('hash1_table', 'hash2_table');

      
                

                    
                    
                
            

Desafortunadamente, ambas tablas tendrían una cantidad ineficiente de cubos en los índices hash de todos modos.

SELECT * FROM hash_metapage_info(get_raw_page('hash1_table_idx', 0))
UNION ALL
SELECT * FROM hash_metapage_info(get_raw_page('hash2_table_idx', 0));

      
                

                    
                    
                
            

CREATE TABLE hash_table(example varchar);

INSERT INTO hash_table (
    SELECT random_string(1024) FROM generate_series(1, 10000)
);

ANALYZE hash_table;

CREATE INDEX hash_table_idx ON hash_table USING hash(example);

SELECT * FROM hash_metapage_info(get_raw_page('hash_table_idx', 0));

      
                

                    
                    
                
            

Volviendo al principio, arreglemos el procedimiento para comparar los índices Hash y B-tree agregando analyzedespués de llenar las tablas. Este será el gráfico correcto que representa los resultados de 10.000 filas con diferentes longitudes dadas.

¡Los resultados ahora tienen más sentido! Y son mucho más inspiradores porque ahora el índice Hash ocupa 30 veces menos memoria que el índice B-tree ( para 10.000 cadenas con 1024 caracteres de longitud ).

analyzees un poderoso instrumento que ayuda a PostgreSQL a hacer su trabajo de manera eficiente. Por lo tanto, es mejor llamarlo con frecuencia que rara vez, especialmente cuando está haciendo pruebas comparativas . Además, lo animo a revisar sus índices y quizás reindexarlos ( después de analizarlos, por supuesto ).

Basado en la investigación anterior, creo que haré un segundo intento en el artículo "Hash vs. B-tree index".

Sígueme para ser notificado de mis próximos artículos.