Início / dba / Perguntas / 62500
Accepted
tomka
Asked: 2014-04-04 12:56:16 +0800 CST

Melhorar o desempenho de COUNT/GROUP-BY em uma grande tabela PostgresSQL?

  • 772

Estou executando o PostgresSQL 9.2 e tenho uma relação de 12 colunas com cerca de 6.700.000 linhas. Ele contém nós em um espaço 3D, cada um referenciando um usuário (que o criou). Para consultar qual usuário criou quantos nós, faço o seguinte (adicionado explain analyzepara obter mais informações):

EXPLAIN ANALYZE SELECT user_id, count(user_id) FROM treenode WHERE project_id=1 GROUP BY user_id;
                                                    QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=253668.70..253669.07 rows=37 width=8) (actual time=1747.620..1747.623 rows=38 loops=1)
   ->  Seq Scan on treenode  (cost=0.00..220278.79 rows=6677983 width=8) (actual time=0.019..886.803 rows=6677983 loops=1)
         Filter: (project_id = 1)
 Total runtime: 1747.653 ms

Como você pode ver, isso leva cerca de 1,7 segundos. Isso não é tão ruim considerando a quantidade de dados, mas eu me pergunto se isso pode ser melhorado. Tentei adicionar um índice BTree na coluna do usuário, mas isso não ajudou em nada.

Você tem sugestões alternativas?

Por uma questão de completude, esta é a definição completa da tabela com todos os seus índices (sem restrições de chave estrangeira, referências e gatilhos):

    Column     |           Type           |                      Modifiers                    
---------------+--------------------------+------------------------------------------------------
 id            | bigint                   | not null default nextval('concept_id_seq'::regclass)
 user_id       | bigint                   | not null
 creation_time | timestamp with time zone | not null default now()
 edition_time  | timestamp with time zone | not null default now()
 project_id    | bigint                   | not null
 location      | double3d                 | not null
 reviewer_id   | integer                  | not null default (-1)
 review_time   | timestamp with time zone |
 editor_id     | integer                  |
 parent_id     | bigint                   |
 radius        | double precision         | not null default 0
 confidence    | integer                  | not null default 5
 skeleton_id   | bigint                   |
Indexes:
    "treenode_pkey" PRIMARY KEY, btree (id)
    "treenode_id_key" UNIQUE CONSTRAINT, btree (id)
    "skeleton_id_treenode_index" btree (skeleton_id)
    "treenode_editor_index" btree (editor_id)
    "treenode_location_x_index" btree (((location).x))
    "treenode_location_y_index" btree (((location).y))
    "treenode_location_z_index" btree (((location).z))
    "treenode_parent_id" btree (parent_id)
    "treenode_user_index" btree (user_id)

Edit: Este é o resultado, quando uso a consulta (e índice) proposta por @ypercube (a consulta leva cerca de 5,3 segundos sem EXPLAIN ANALYZE):

EXPLAIN ANALYZE SELECT u.id, ( SELECT COUNT(*) FROM treenode AS t WHERE t.project_id=1 AND t.user_id = u.id ) AS number_of_nodes FROM auth_user As u;
                                                                        QUERY PLAN                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on auth_user u  (cost=0.00..6987937.85 rows=46 width=4) (actual time=29.934..5556.147 rows=46 loops=1)
   SubPlan 1
     ->  Aggregate  (cost=151911.65..151911.66 rows=1 width=0) (actual time=120.780..120.780 rows=1 loops=46)
           ->  Bitmap Heap Scan on treenode t  (cost=4634.41..151460.44 rows=180486 width=0) (actual time=13.785..114.021 rows=145174 loops=46)
                 Recheck Cond: ((project_id = 1) AND (user_id = u.id))
                 Rows Removed by Index Recheck: 461076
                 ->  Bitmap Index Scan on treenode_user_index  (cost=0.00..4589.29 rows=180486 width=0) (actual time=13.082..13.082 rows=145174 loops=46)
                       Index Cond: ((project_id = 1) AND (user_id = u.id))
 Total runtime: 5556.190 ms
(9 rows)

Time: 5556.804 ms

Edit 2: Este é o resultado, quando eu uso um indexon project_id, user_id(mas sem otimização de esquema, ainda) como sugerido por @erwin-brandstetter (a consulta é executada com 1,5 segundos na mesma velocidade que minha consulta original):

EXPLAIN ANALYZE SELECT user_id, count(user_id) as ct FROM treenode WHERE project_id=1 GROUP BY user_id;
                                                        QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=253670.88..253671.24 rows=37 width=8) (actual time=1807.334..1807.339 rows=38 loops=1)
   ->  Seq Scan on treenode  (cost=0.00..220280.62 rows=6678050 width=8) (actual time=0.183..893.491 rows=6678050 loops=1)
         Filter: (project_id = 1)
 Total runtime: 1807.368 ms
(4 rows)
postgresql performance

2 respostas

  1. Best Answer

    O principal problema é o índice ausente. Mas há mais.

    SELECT user_id, count(*) AS ct
FROM   treenode
WHERE  project_id = 1
GROUP  BY user_id;

    • Você tem muitas bigintcolunas. Provavelmente exagero. Normalmente, integeré mais do que suficiente para colunas como project_ide user_id. Isso também ajudaria o próximo item.
      Ao otimizar a definição da tabela, considere esta resposta relacionada, com ênfase no alinhamento e preenchimento de dados . Mas a maior parte do resto também se aplica:

    • O elefante na sala : não há índiceproject_id . Crie um. Isso é mais importante do que o resto desta resposta.
      Enquanto estiver nisso, faça disso um índice de várias colunas:

      CREATE INDEX treenode_project_id_user_id_index ON treenode (project_id, user_id);

      Se você seguiu meu conselho, integerseria perfeito aqui:

    • user_idé definido NOT NULL, então count(user_id)é equivalente a count(*), mas o último é um pouco mais curto e mais rápido. (Nesta consulta específica, isso se aplicaria até mesmo sem user_idser definido NOT NULL.)

    • idjá é a chave primária, a UNIQUErestrição adicional é lastro inútil . Largue:

      "treenode_pkey" PRIMARY KEY, btree (id)
"treenode_id_key" UNIQUE CONSTRAINT, btree (id)

      Aparte: eu não usaria idcomo nome da coluna. Use algo descritivo como treenode_id.

    Informações adicionadas

    P: How many different project_id and user_id?
    R: not more than five different project_id.

    Isso significa que o Postgres precisa ler cerca de 20% de toda a tabela para satisfazer sua consulta. A menos que possa usar uma varredura somente de índice , uma varredura sequencial na tabela será mais rápida do que envolver quaisquer índices. Não há mais desempenho para ganhar aqui - exceto otimizando as configurações da mesa e do servidor.

    Quanto à verificação somente de índice : Para ver o quão eficaz isso pode ser, execute VACUUM ANALYZEse você puder pagar (bloqueia a tabela exclusivamente). Em seguida, tente sua consulta novamente. Agora deve ser moderadamente mais rápido usando apenas o índice. Leia esta resposta relacionada primeiro:

    Assim como a página de manual adicionada com o Postgres 9.6 e o ​​Postgres Wiki em varreduras somente de índice .

    • 33

  2. Eu adicionaria primeiro um índice (project_id, user_id)e, em seguida, na versão 9.3, tente esta consulta:

    SELECT u.user_id, c.number_of_nodes 
FROM users AS u
   , LATERAL
     ( SELECT COUNT(*) AS number_of_nodes 
       FROM treenode AS t
       WHERE t.project_id = 1 
         AND t.user_id = u.user_id
     ) c 
-- WHERE c.number_of_nodes > 0 ;   -- you probably want this as well
                                   -- to show only relevant users

    Em 9.2, tente este:

    SELECT u.user_id, 
       ( SELECT COUNT(*) 
         FROM treenode AS t
         WHERE t.project_id = 1 
           AND t.user_id = u.user_id
       ) AS number_of_nodes  
FROM users AS u ;

    Imagino que você tenha uma usersmesa. Se não, substitua userspor:
    (SELECT DISTINCT user_id FROM treenode)

    • 7

relate perguntas