Hola de nuevo. Hoy queríamos contaros cómo revisar las estadísticas de una tabla en PostgreSQL.
Para generar un buen plan para las consultas, el planificador de consultas se basa en la información estadística disponible sobre el contenido de las tablas. Por tanto, es fundamental asegurarse de que las estadísticas sean precisas y estén actualizadas. Si las estadísticas son obsoletas, se generará un plan deficiente para las consultas, lo que acabará degradando aún más el rendimiento de la base de datos. Para ver como se almacenan las estadísticas de cada tabla vamos a crear una tabla de ejemplo y la vamos rellenar con un millón de filas y dos columnas.
Preparación del escenario para analizar estadísticas
En la primera columna tenemos un número secuencial de 1 a 1.000.000 y en la segunda un valor fijo, ‘hola’ en este caso:
test=# CREATE TABLE t_test AS SELECT *, 'hola'::text AS name FROM generate_series(1, 1000000) AS id; SELECT 1000000
Para tener una mayor fiabilidad se puede analizar el 100% de los registros de la tabla, en este caso solo hemos puesto un 10% que debería ser suficiente:
test=# ALTER TABLE t_test ALTER COLUMN id SET STATISTICS 10; ALTER TABLE test=# ANALYZE; ANALYZE
Si mostramos el plan de ejecución para los valores que sean menores de 150000 nos mostrará lo siguiente:
test=# explain SELECT * FROM t_test WHERE id < 150000; QUERY PLAN --------------------------------------------------------------- Seq Scan on t_test (cost=0.00..17906.00 rows=145969 width=9) Filter: (id < 150000) (2 rows)
Lo que vemos aquí es que PostgreSQL espera que el escaneo secuencial devolviera 145.000 filas. Esta información es muy importante porque si el sistema sabe qué esperar, puede ajustar su estrategia en consecuencia (índice, sin índice, etc.).
Pero, ¿cómo podemos ver nosotros esta información?
Entendiendo pg_stats
La respuesta es la vista pg_stat. El contenido de la vista es:
test=# \d pg_stats View "pg_catalog.pg_stats" Column | Type | Collation | Nullable | Default ------------------------+----------+-----------+----------+--------- schemaname | name | | | tablename | name | | | attname | name | | | inherited | boolean | | | null_frac | real | | | avg_width | integer | | | n_distinct | real | | | most_common_vals | anyarray | | | most_common_freqs | real[] | | | histogram_bounds | anyarray | | | correlation | real | | | most_common_elems | anyarray | | | most_common_elem_freqs | real[] | | | elem_count_histogram | real[] | | |
Repasemos la salida y analicemos qué tipo de datos puede usar el planificador:
- schemaname + tablename + attname: Para cada columna de cada tabla en cada esquema, PostgreSQL almacenará una línea de datos.
- inherited: ¿Estamos viendo una tabla heredada / particionada o no?
- null_frac: ¿Qué porcentaje de la columna contiene valores NULL? Esto es importante si utilizamos queries como » WHERE col IS NULL» o » WHERE col IS NOT NULL»
- avg_width: ¿Cuál es el ancho promedio esperado de la columna?
- n_distinct: número esperado de entradas diferentes en la columna
- most_common_vals: Tenemos información más precisa para los valores que ocurren con más frecuencia. Esto es especialmente importante si las entradas de la tabla no están distribuidas de manera uniforme.
- most_common_freqs: ¿Cuál es la frecuencia de esos valores más comunes? PostgreSQL almacena aquí un valor porcentual. Por ejemplo: “Masculino” es una entrada frecuente y el 54,32% de las entradas son masculinos.
- histogram_bounds: PostgreSQL usa un histograma para almacenar la distribución de datos. En caso de que el objetivo de las estadísticas sea 100, la base de datos almacenará 101 entradas para indicar los límites dentro de los datos.
- correlation: el optimizador también quiere saber algo sobre el orden físico de los datos en el disco. Hace una diferencia sobre si los datos se almacenan en orden (1, 2, 3, 4, 5, 6,…) o aleatoriamente (6, 1, 2, 3, 5, 4,…). En caso de que estemos buscando rangos, se necesitan menos bloques para leer datos ordenados.
Análisis de estadísticas de nuestra tabla con pg_stats
Si buscamos nuestra tabla en la vista, lo que obtenemos es:
En este listado, se puede ver lo que PostgreSQL sabe sobre nuestra tabla.
En la columna «id», la parte del histograma es la más importante: «{47,102906,205351,301006,402747,503156,603102,700866,802387,901069,999982}«. PostgreSQL cree que el valor más pequeño es 47. El 10% es menor que 102906, se espera que el 20% sea menor que 205351, y así sucesivamente. Lo que también es interesante aquí es n_distinct: -1 básicamente significa que todos los valores son diferentes. Esto es importante si estamos utilizando GROUP BY. En el caso de GROUP BY, el optimizador quiere saber cuántos grupos esperar. n_distinct se utiliza en muchos casos para proporcionarnos esa estimación.
En el caso de la columna “nombre” podemos ver que “hola” es el valor más frecuente (100%). Por eso no obtenemos un histograma.
Por supuesto, hay mucho más que decir sobre cómo funciona el optimizador de PostgreSQL. Sin embargo, para empezar, es muy útil tener una comprensión básica de la forma en que PostgreSQL usa las estadísticas.
Esperamos que os guste esta entrada y en post posteriores iremos profundizando en la forma de funcionar de este estupendo gestor. No te pierdas la siguiente, suscribiéndote a nuestra newsletter. Con un solo email al mes estarás informado de todas nuestras entradas.
¿Aún no conoces Query Performance? Descubre cómo puede ayudarte en tu entorno Oracle. Más información en su página de LinkedIn.
Sígue a GPS en LinkedIn
Un saludo y hasta la próxima.