Início / dba / Perguntas / 113149
Accepted
bobocopy
Asked: 2015-09-04 12:47:02 +0800 CST

As linhas de largura fixa melhoram o desempenho de leitura do PostgreSQL?

  • 772

tenho uma tabela articles:

                                                       Table "articles"
     Column     |            Type             |                     Modifiers                      | Storage  | Stats target | Description
----------------+-----------------------------+----------------------------------------------------+----------+--------------+-------------
 id             | integer                     | not null default nextval('articles_id_seq'::regclass) | plain    |              |
 user_id        | integer                     |                                                    | plain    |              |
 title          | character varying(255)      |                                                    | extended |              |
 author         | character varying(255)      |                                                    | extended |              |
 body           | text                        | default '--- []                                   +| extended |              |
                |                             | '::text                                            |          |              |
 created_at     | timestamp without time zone |                                                    | plain    |              |
 updated_at     | timestamp without time zone |                                                    | plain    |              |
 published_date | timestamp without time zone |                                                    | plain    |              |

Indexes:
    "articles_pkey" PRIMARY KEY, btree (id)
    "index_articles_on_published_date" btree (published_date)
    "index_rents_on_user_id" btree (user_id)
    "index_articles_on_user_id_and_published_date" btree (user_id, published_date)

Estamos no Postgres 9.4.4. A máquina tem 3,5 GB de memória e 150 GB de espaço em disco em um SSD.

Nota: O 'published_date' é sempre arredondado, pela aplicação, para a data mais próxima. Todas as horas/minutos/segundos são sempre 00. Legacy. Necessidades corrigidas. etc.

Esta tabela tem centenas de milhões de artigos. A tabela recebe uma grande quantidade de consultas de leitura de (até 16) processos simultâneos executando as seguintes consultas tão rapidamente quanto nosso sistema responderá:

  • uma contagem do número total de artigos

    SELECT COUNT(*) FROM articles;

  • uma seleção de todos os artigos publicados para um determinado usuário

    SELECT * FROM articles WHERE user_id = $1;

  • uma seleção do artigo publicado mais recentemente para um determinado usuário

    SELECT * FROM articles WHERE user_id = $1 ORDER BY published_date DESC LIMIT 1;

Estou descobrindo que, com um grande número de trabalhadores, essas consultas são bastante lentas. (No pico de carga, o primeiro leva minutos para ser concluído; os outros dois são da ordem de 10 segundos.) Em particular, parece que as consultas estão sendo enfileiradas.

A questão

Em resumo, as tabelas com apenas valores de largura fixa executam consultas de leitura melhor do que aquelas com larguras variadas? (Finja que o espaço em disco não é um problema.) No meu caso, estou me perguntando se veria uma melhoria de desempenho se extraísse o campo de texto 'corpo' para uma tabela separada e transformasse os campos de variação de caracteres em largura fixa campos de caracteres.

Admito que a pergunta é um pouco culta à carga. Eu simplesmente não sei o suficiente sobre os componentes internos do mecanismo de banco de dados Postgres para construir uma hipótese informada. Pretendo realizar experimentos reais com diferentes esquemas e configurações, mas gostaria de ter um modelo mental sólido de como o Postgres realmente funciona antes de prosseguir.

Pergunta relacionada

Onde posso aprender mais sobre os componentes internos do mecanismo de banco de dados Postgres? Eu pesquisei variações da pergunta acima com pouco sucesso. Quais são os termos corretos a serem usados ​​para esta pesquisa? Esse nível de documentação existe apenas na fonte e nas mentes dos DBAs Postgres? Também convido humildemente a sugestão de bons livros sobre o tema.

postgresql performance

1 respostas

  1. Best Answer

    As tabelas com apenas valores de largura fixa executam consultas de leitura melhor do que aquelas com larguras variadas?

    Basicamente não. Existem custos muito pequenos ao acessar as colunas, mas você não poderá medir nenhuma diferença. Detalhes:

    Em particular:

    O uso de varchar(255)em uma definição de tabela geralmente indica uma falta de compreensão do sistema de tipos do Postgres. O arquiteto por trás disso provavelmente não é um falante nativo - ou o layout foi transferido de outro RDBMS como o SQL Server, onde isso costumava ser importante.

    • Sua consulta mais cara SELECT COUNT(*) FROM articlesnem sequer considera os dados da linha , apenas o tamanho total importa indiretamente. Contar todas as linhas é caro no Postgres devido ao seu modelo MVCC. Talvez uma estimativa seja boa o suficiente, que pode ser obtida muito barato ?
    • Maneira rápida de descobrir a contagem de linhas de uma tabela

    (Finja que o espaço em disco não é um problema.)

    O espaço em disco é sempre um problema, mesmo se você tiver muito. O tamanho do disco (número de páginas de dados que devem ser lidas/processadas/escritas) é um dos fatores mais importantes para o desempenho.

    Onde posso aprender mais sobre os componentes internos do mecanismo de banco de dados Postgres?

    A página de informações para a tag tem os links mais importantes para mais informações, incluindo livros, o Postgres Wiki e o excelente manual. Este último é o meu favorito.

    Sua terceira consulta tem problemas

    SELECT * FROM articles WHERE user_id = $1 ORDER BY published_date DESC LIMIT 1;

    ORDER BY published_date DESC, mas published_datepode ser NULL (sem NOT NULLrestrição). Isso é uma arma de fogo carregada se pode haver valores NULL, a menos que você prefira valores NULL sobre o último real published_date.

    Adicione uma restriçãoNOT NULL . Sempre faça isso para colunas que não podem ser NULL.
    Ou faça isso ORDER BY published_date DESCNULLS LASTe adapte o índice de acordo.

    "articles_user_id_published_date_idx" btree (user_id, published_date DESC NULLS LAST)

    Detalhes nesta resposta relacionada recente:

    Converter published_datepara um realdate

    Enquanto 'published_date' is always rounded, é efetivamente apenas um dateque ocupa 4 bytes em vez de 8 para o timestamp. É melhor mover isso na definição da tabela para vir antes das duas timestampcolunas, para não perder os 4 bytes para o preenchimento:

    ...
body           | text
published_date | date   --     <---- here
created_at     | timestamp without time zone
updated_at     | timestamp without time zone

    O armazenamento em disco menor faz diferença no desempenho.

    Mais importante, seu índice (user_id, published_date)agora ocuparia apenas 32 bytes por entrada de índice em vez de 40, porque 2x4 bytes não incorrem em preenchimento extra. E isso faria uma diferença notável para o desempenho.

    Aparte: este índice não é relevante para as consultas demonstradas. Excluir, a menos que os índices, a menos que sejam usados ​​em outro lugar:

    "index_articles_on_published_date" btree (published_date)

    • 13

relate perguntas