Solución:
P2: way to measure page size
PostgreSQL proporciona una serie de Funciones de tamaño de objeto de base de datos. Empaqueté los más interesantes en esta consulta y agregué algunas funciones de acceso a estadísticas en la parte inferior. (El módulo adicional pgstattuple proporciona funciones más útiles, todavía.)
Esto mostrará que diferentes métodos para medir el “tamaño de una fila” conducen a resultados muy diferentes. Todo depende de lo que quieras medir, exactamente.
Esta consulta requiere Postgres 9.3 o posterior. Para versiones anteriores, consulte a continuación.
Usando un VALUES expresión en una LATERAL subconsulta, para evitar deletrear cálculos para cada fila.
Reemplazar public.tbl con su nombre de tabla opcionalmente calificado por esquema para obtener una vista compacta de las estadísticas de tamaño de fila recopiladas. Puede envolver esto en una función plpgsql para uso repetido, entregar el nombre de la tabla como parámetro y usar EXECUTE …
SELECT l.metric, l.nr AS "bytes/ct"
, CASE WHEN is_size THEN pg_size_pretty(nr) END AS bytes_pretty
, CASE WHEN is_size THEN nr / NULLIF(x.ct, 0) END AS bytes_per_row
FROM (
SELECT min(tableoid) AS tbl -- = 'public.tbl'::regclass::oid
, count(*) AS ct
, sum(length(t::text)) AS txt_len -- length in characters
FROM public.tbl t -- provide table name *once*
) x
, LATERAL (
VALUES
(true , 'core_relation_size' , pg_relation_size(tbl))
, (true , 'visibility_map' , pg_relation_size(tbl, 'vm'))
, (true , 'free_space_map' , pg_relation_size(tbl, 'fsm'))
, (true , 'table_size_incl_toast' , pg_table_size(tbl))
, (true , 'indexes_size' , pg_indexes_size(tbl))
, (true , 'total_size_incl_toast_and_indexes', pg_total_relation_size(tbl))
, (true , 'live_rows_in_text_representation' , txt_len)
, (false, '------------------------------' , NULL)
, (false, 'row_count' , ct)
, (false, 'live_tuples' , pg_stat_get_live_tuples(tbl))
, (false, 'dead_tuples' , pg_stat_get_dead_tuples(tbl))
) l(is_size, metric, nr);
Resultado:
metric | bytes/ct | bytes_pretty | bytes_per_row -----------------------------------+----------+--------------+--------------- core_relation_size | 44138496 | 42 MB | 91 visibility_map | 0 | 0 bytes | 0 free_space_map | 32768 | 32 kB | 0 table_size_incl_toast | 44179456 | 42 MB | 91 indexes_size | 33128448 | 32 MB | 68 total_size_incl_toast_and_indexes | 77307904 | 74 MB | 159 live_rows_in_text_representation | 29987360 | 29 MB | 62 ------------------------------ | | | row_count | 483424 | | live_tuples | 483424 | | dead_tuples | 2677 | |
Para versiones anteriores (Postgres 9.2 o anterior):
WITH x AS (
SELECT count(*) AS ct
, sum(length(t::text)) AS txt_len -- length in characters
, 'public.tbl'::regclass AS tbl -- provide table name as string
FROM public.tbl t -- provide table name as name
), y AS (
SELECT ARRAY [pg_relation_size(tbl)
, pg_relation_size(tbl, 'vm')
, pg_relation_size(tbl, 'fsm')
, pg_table_size(tbl)
, pg_indexes_size(tbl)
, pg_total_relation_size(tbl)
, txt_len
] AS val
, ARRAY ['core_relation_size'
, 'visibility_map'
, 'free_space_map'
, 'table_size_incl_toast'
, 'indexes_size'
, 'total_size_incl_toast_and_indexes'
, 'live_rows_in_text_representation'
] AS name
FROM x
)
SELECT unnest(name) AS metric
, unnest(val) AS "bytes/ct"
, pg_size_pretty(unnest(val)) AS bytes_pretty
, unnest(val) / NULLIF(ct, 0) AS bytes_per_row
FROM x, y
UNION ALL SELECT '------------------------------', NULL, NULL, NULL
UNION ALL SELECT 'row_count', ct, NULL, NULL FROM x
UNION ALL SELECT 'live_tuples', pg_stat_get_live_tuples(tbl), NULL, NULL FROM x
UNION ALL SELECT 'dead_tuples', pg_stat_get_dead_tuples(tbl), NULL, NULL FROM x;Mismo resultado.
Q1: anything inefficient?
Podrías optimizar orden de columna para ahorrar algunos bytes por fila, actualmente desperdiciados en el relleno de alineación:
integer | not null default nextval('core_page_id_seq'::regclass)
integer | not null default 0
character varying(255) | not null
character varying(64) | not null
text | default '{}'::text
character varying(255) |
text | default '{}'::text
text |
timestamp with time zone |
timestamp with time zone |
integer |
integer |
Esto ahorra entre 8 y 18 bytes por fila. Yo lo llamo “columna tetris”. Detalles:
- Configuración de PostgreSQL para rendimiento de lectura
- Calculando y ahorrando espacio en PostgreSQL
Considere también:
- ¿La búsqueda de índices sería notablemente más rápida con char vs varchar cuando todos los valores son 36 caracteres?
Es fácil obtener una aproximación del tamaño de una fila, incluido el contenido tostado, consultando la longitud de la representación de TEXTO de toda la fila:
SELECT octet_length(t.*::text) FROM tablename AS t WHERE primary_key=:value;
Esta es una aproximación cercana a la cantidad de bytes que se recuperarán del lado del cliente al ejecutar:
SELECT * FROM tablename WHERE primary_key=:value;
… asumiendo que la persona que llama a la consulta solicita resultados en formato de texto, que es lo que hacen la mayoría de los programas (el formato binario es posible, pero no vale la pena en la mayoría de los casos).
Se podría aplicar la misma técnica para localizar el N filas “más grandes en el texto” de tablename:
SELECT primary_key, octet_length(t.*::text) FROM tablename AS t
ORDER BY 2 DESC LIMIT :N;
Hay algunas cosas que podrían estar sucediendo. En general, dudo que la longitud sea el problema próximo. Sospecho que, en cambio, tienes un problema relacionado con la longitud.
Dice que los campos de texto pueden llegar a unos pocos k. Una fila no puede superar los 8k en el almacenamiento principal y es probable que los campos de texto más grandes se hayan tostado o se hayan movido del almacenamiento principal a un almacenamiento extendido en archivos separados. Esto hace que su almacenamiento principal sea más rápido (por lo que la identificación de selección en realidad es más rápida porque hay menos páginas de disco a las que acceder), pero la selección * se vuelve más lenta porque hay más E / S aleatorias.
Si el tamaño total de las filas aún está por debajo de 8k, puede intentar alterar la configuración de almacenamiento. Sin embargo, advertiría que pueden ocurrir cosas malas al insertar un atributo de gran tamaño en el almacenamiento principal, así que es mejor no tocar esto si no es necesario y, si lo hace, establezca los límites apropiados a través de restricciones de verificación. Por lo tanto, el transporte probablemente no sea lo único. Puede estar comparando muchos, muchos campos que requieren lecturas aleatorias. Una gran cantidad de lecturas aleatorias también pueden causar fallas de caché, y una gran cantidad de memoria requerida puede requerir que las cosas se materialicen en el disco y una gran cantidad de filas anchas, si hay una unión presente (y hay una si TOAST está involucrado) puede requerir más costos unir patrones, etc.
Lo primero que miraría hacer es seleccionar menos filas y ver si eso ayuda. Si eso funciona, también podría intentar agregar más RAM al servidor, pero comenzaría y vería dónde comienza a caer el rendimiento debido a cambios de plan y fallas de caché primero.