Início / dba / Perguntas / 29489
Accepted
reox
Asked: 2012-11-30 01:23:56 +0800 CST

Consulta lenta em tabela grande com GROUP BY e ORDER BY

  • 772

Eu tenho uma tabela com 7,2 milhões de tuplas que se parece com isso:

                               table public.methods
 column |          type         |                      attributes
--------+-----------------------+----------------------------------------------------
 id     | integer               | not null DEFAULT nextval('methodkey'::regclass)
 hash   | character varying(32) | not null
 string | character varying     | not null
 method | character varying     | not null
 file   | character varying     | not null
 type   | character varying     | not null
Indexes:
    "methods_pkey" PRIMARY KEY, btree (id)
    "methodhash" btree (hash)

Agora quero selecionar alguns valores, mas a consulta é incrivelmente lenta:

db=# explain 
    select hash, string, count(method) 
    from methods 
    where hash not in 
          (select hash from nostring) 
    group by hash, string 
    order by count(method) desc;
                                            QUERY PLAN
----------------------------------------------------------------------------------------
 Sort  (cost=160245190041.10..160245190962.07 rows=368391 width=182)
   Sort Key: (count(methods.method))
   ->  GroupAggregate  (cost=160245017241.77..160245057764.73 rows=368391 width=182)
       ->  Sort  (cost=160245017241.77..160245026451.53 rows=3683905 width=182)
             Sort Key: methods.hash, methods.string
             ->  Seq Scan on methods  (cost=0.00..160243305942.27 rows=3683905 width=182)
                   Filter: (NOT (SubPlan 1))
                   SubPlan 1
                   ->  Materialize  (cost=0.00..41071.54 rows=970636 width=33)
                     ->  Seq Scan on nostring  (cost=0.00..28634.36 rows=970636 width=33)

A hashcoluna é o hash md5 stringe tem um índice. Então, acho que meu problema é que a tabela inteira é classificada por id e não por hash, então demora um pouco para classificá-la primeiro e depois agrupá-la?

A tabela nostringcontém apenas uma lista de hashes que não quero ter. Mas eu preciso que ambas as tabelas tenham todos os valores. Portanto, não é uma opção para excluí-los.

informações adicionais: nenhuma das colunas pode ser nula (corrigido isso na definição da tabela) e estou usando o postgresql 9.2.

postgresql performance

4 respostas

  1. Best Answer

    A resposta LEFT JOINdo @dezso deve ser boa. Um índice, no entanto, dificilmente será útil (por si só), porque a consulta precisa ler toda a tabela de qualquer maneira - a exceção são varreduras somente de índice no Postgres 9.2+ e condições favoráveis, veja abaixo.

    SELECT m.hash, m.string, count(m.method) AS method_ct
FROM   methods m
LEFT   JOIN nostring n USING (hash)
WHERE  n.hash IS NULL
GROUP  BY m.hash, m.string 
ORDER  BY count(m.method) DESC;

    Execute EXPLAIN ANALYZEna consulta. Várias vezes para excluir efeitos de saque e ruído. Compare os melhores resultados.

    Crie um índice de várias colunas que corresponda à sua consulta:

    CREATE INDEX methods_cluster_idx ON methods (hash, string, method);

    Espere? Depois que eu disse que um índice não ajudaria? Bem, precisamos disso para CLUSTERa mesa:

    CLUSTER methods USING methods_cluster_idx;
ANALYZE methods;

    Execute novamente EXPLAIN ANALYZE. Algum mais rápido? Deveria ser.

    CLUSTERé uma operação única para reescrever a tabela inteira na ordem do índice usado. Também é efetivamente um VACUUM FULL. Se você quiser ter certeza, faça um pré-teste VACUUM FULLsozinho para ver o que pode ser atribuído a isso.

    Se sua tabela vir muitas operações de gravação, o efeito será degradado com o tempo. Agende CLUSTERfora do horário para restaurar o efeito. O ajuste fino depende do seu caso de uso exato. O manual sobre CLUSTER.

    CLUSTERé uma ferramenta bastante grosseira, precisa de um bloqueio exclusivo na mesa. Se você não pode pagar por isso, considere pg_repackque pode fazer o mesmo sem bloqueio exclusivo. Mais nesta resposta posterior:

    Se a porcentagem de NULLvalores na coluna methodfor alta (mais de ~ 20%, dependendo dos tamanhos reais das linhas), um índice parcial deve ajudar:

    CREATE INDEX methods_foo_idx ON methods (hash, string)
WHERE method IS NOT NULL;

    (Sua atualização posterior mostra que suas colunas são NOT NULL, portanto, não aplicável.)

    Se você estiver executando o PostgreSQL 9.2 ou posterior (como @deszo comentou ) os índices apresentados podem ser úteis CLUSTERse o planejador puder utilizar varreduras somente de índice . Aplicável apenas em condições favoráveis: Nenhuma operação de gravação que afete o mapa de visibilidade, pois a última VACUUMe todas as colunas da consulta precisam ser cobertas pelo índice. Basicamente, as tabelas somente leitura podem usar isso a qualquer momento, enquanto as tabelas muito escritas são limitadas. Mais detalhes no Wiki do Postgres.

    O índice parcial mencionado acima poderia ser ainda mais útil nesse caso.

    Se , por outro lado, não houver NULL valores na coluna method, você deve
    1.) defini-la NOT NULLe
    2.) usar count(*)em vez de count(method), isso é um pouco mais rápido e faz o mesmo na ausência de NULLvalores.

    Se você precisar chamar essa consulta com frequência e a tabela for somente leitura, crie um arquivo MATERIALIZED VIEW.

    Ponto fino exótico: Sua tabela é nomeada nostring, mas parece conter hashes. Ao excluir hashes em vez de strings, há uma chance de você excluir mais strings do que o pretendido. Extremamente improvável, mas possível.

    • 19

  2. Bem-vindo ao DBA.SE!

    Você pode tentar reformular sua consulta assim:

    SELECT m.hash, string, count(method) 
FROM 
    methods m
    LEFT JOIN nostring n ON m.hash = n.hash
WHERE n.hash IS NULL
GROUP BY hash, string 
ORDER BY count(method) DESC;

    ou outra possibilidade:

    SELECT m.hash, string, count(method) 
FROM 
    methods m
WHERE NOT EXISTS (SELECT hash FROM nostring WHERE hash = m.hash)
GROUP BY hash, string 
ORDER BY count(method) DESC;

    NOT INé um coletor típico para desempenho, pois é difícil usar um índice com ele.

    Isso pode ser aprimorado ainda mais com índices. Um índice em nostring.hashparece útil. Mas primeiro: o que você ganha agora? (Seria melhor ver o resultado, EXPLAIN ANALYZEjá que os custos em si não informam o tempo que as operações levaram.)

    • 5

  3. Como o hash é um md5, você provavelmente pode tentar convertê-lo em um número: você pode armazená-lo como um número ou apenas criar um índice funcional que calcule esse número em uma função imutável.

    Outras pessoas já criaram uma função pl/pgsql que converte (parte de) um valor md5 de texto para string. Consulte https://stackoverflow.com/questions/9809381/hashing-a-string-to-a-numeric-value-in-postgressql para obter um exemplo

    Eu acredito que você está realmente gastando muito tempo na comparação de strings enquanto verifica o índice. Se você conseguir armazenar esse valor como um número, deve ser realmente muito mais rápido.

    • 1

  4. Eu me deparo muito com esse problema e descobri um truque simples de 2 partes.

    1. Crie um índice de substring no valor de hash: (7 geralmente é um bom comprimento)

      create index methods_idx_hash_substring ON methods(substring(hash,1,7))

    2. Faça com que suas pesquisas/junções incluam uma correspondência de substring, para que o planejador de consulta seja sugerido para usar o índice:

      velho: WHERE hash = :kwarg

      novo: WHERE (hash = :kwarg) AND (substring(hash,1,7) = substring(:kwarg,1,7))

    Você também deve ter um índice no raw hashtambém.

    o resultado (geralmente) é que o planejador consultará primeiro o índice de substring e eliminará a maioria das linhas. em seguida, ele corresponde ao hash completo de 32 caracteres ao índice (ou tabela) correspondente. essa abordagem reduziu as consultas de 800ms para 4 para mim.

    • 0

relate perguntas