O que é recuperado do disco durante uma consulta?

O armazenamento físico para linhas é descrito nos documentos em Layout de página do banco de dados . O conteúdo da coluna para a mesma linha são todos armazenados na mesma página de disco, com exceção notável do conteúdo TOAST 'ed (grande demais para caber em uma página). Os conteúdos são extraídos sequencialmente dentro de cada linha, conforme explicado:

Para ler os dados, você precisa examinar cada atributo por vez. Primeiro verifique se o campo é NULL de acordo com o bitmap nulo. Se for, vá para o próximo. Em seguida, certifique-se de ter o alinhamento correto. Se o campo for de largura fixa, todos os bytes serão simplesmente colocados.

No caso mais simples (sem colunas TOAST'ed), o postgres buscará a linha inteira, mesmo que poucas colunas sejam necessárias. Portanto, neste caso, a resposta é sim, ter mais colunas pode ter um claro impacto adverso no cache do buffer de desperdício, especialmente se o conteúdo da coluna for grande enquanto ainda estiver abaixo do limite TOAST.

Agora, o caso TOAST: quando um campo individual excede ~2kB , o mecanismo armazena o conteúdo do campo em uma tabela física separada. Ele também entra em jogo quando a linha inteira não cabe em uma página (8kB por padrão): alguns dos campos são movidos para o armazenamento TOAST. Doc diz:

Se for um campo de comprimento variável (attlen = -1), é um pouco mais complicado. Todos os tipos de dados de comprimento variável compartilham a estrutura de cabeçalho comum struct varlena, que inclui o comprimento total do valor armazenado e alguns bits de sinalizador. Dependendo dos sinalizadores, os dados podem estar embutidos ou em uma tabela TOAST; pode ser compactado também

Os conteúdos TOAST não são buscados quando não são explicitamente necessários, então seu efeito no número total de páginas a serem buscadas é pequeno (alguns bytes por coluna). Isso explica os resultados da resposta de @dezso.

Quanto às gravações, cada linha com todas as suas colunas é totalmente reescrita em cada UPDATE, independentemente das colunas alteradas. Portanto, ter mais colunas é obviamente mais caro para as gravações.

A resposta de Daniel se concentra no custo da leitura de linhas individuais. Neste contexto: Colocar NOT NULLcolunas de tamanho fixo primeiro em sua tabela ajuda um pouco. Colocar as colunas relevantes primeiro (aquelas que você consulta) ajuda um pouco. Minimizar o preenchimento (devido ao alinhamento de dados) jogando tetris de alinhamento com suas colunas pode ajudar um pouco. Mas o efeito mais importante ainda não foi mencionado, especialmente para mesas grandes.

Colunas adicionais obviamente fazem com que uma linha cubra mais espaço em disco, de modo que menos linhas caibam em uma página de dados (8 kB por padrão). Linhas individuais são espalhadas por mais páginas. O mecanismo de banco de dados geralmente precisa buscar páginas inteiras, não linhas individuais . Pouco importa se as linhas individuais são um pouco menores ou maiores - desde que o mesmo número de páginas tenha que ser lido.

Se uma consulta buscar uma porção (relativamente) pequena de uma grande tabela, onde as linhas estão espalhadas mais ou menos aleatoriamente por toda a tabela, suportadas por um índice, isso resultará aproximadamente no mesmo número de páginas lidas, com pouca consideração para o tamanho da linha. Colunas irrelevantes não vão atrasá-lo muito nesse caso (raro).

Normalmente, você buscará patches ou grupos de linhas que foram inseridas em sequência ou proximidade e compartilhará páginas de dados. Essas linhas estão espalhadas devido à desordem, mais páginas do disco precisam ser lidas para satisfazer sua consulta. Ter que ler mais páginas é normalmente o motivo mais importante para uma consulta ser mais lenta. E esse é o fator mais importante pelo qual colunas irrelevantes tornam suas consultas mais lentas.

Com bancos de dados grandes, normalmente não há RAM suficiente para manter tudo na memória cache. Linhas maiores ocupam mais cache, mais contenção, menos ocorrências de cache, mais E/S de disco. E as leituras de disco são normalmente muito mais caras. Menos com SSDs, mas uma diferença substancial permanece. Isso aumenta o ponto acima sobre leituras de página.

Pode ou não importar se as colunas irrelevantes são TOAST-ed. As colunas relevantes também podem ser TOAST-ed, trazendo de volta muito do mesmo efeito.

Um pequeno teste:

CREATE TABLE test2 (
    id serial PRIMARY KEY,
    num integer,
    short_text varchar(32),
    longer_text varchar(1000),
    long_long_text text
);

INSERT INTO test2 (num, short_text, longer_text, long_long_text)
SELECT i, lpad('', 32, 'abcdefeghji'), lpad('', 1000, 'abcdefeghji'), lpad('', (random() * 10000)::integer, 'abcdefeghji')
FROM generate_series(1, 10000) a(i);

ANALYZE test2;

SELECT * FROM test2;
[...]
Time: 1091.331 ms

SELECT num FROM test2;
[...]
Time: 21.310 ms

Limitar a consulta às primeiras 250 linhas ( WHERE num <= 250) resulta em 34,539 ms e 8,343 ms, respectivamente. A seleção de todos, exceto long_long_textdeste conjunto limitado, resulta em 18,432 ms. Isso mostra que, em seus termos, o PG é inteligente o suficiente.