Início / dba / Perguntas / 118688
Accepted
St.Antario
Asked: 2015-10-21 21:35:43 +0800 CST

Consulta mais rápida para IDs distintos em um relacionamento muitos-para-muitos

  • 772

Eu tenho esta tabela no PostgreSQL 9.4:

CREATE TABLE user_operations( 
    id SERIAL PRIMARY KEY, 
    operation_id integer, 
    user_id integer )

A tabela consiste em ~1000-2000diferentes operações, cada uma correspondendo a algum subconjunto (consistindo aproximadamente de 80000-120000elementos cada) do conjunto Sde todos os usuários:

S = {1, 2, 3, ... , 122655}

Parâmetros:

work_mem = 128MB
table_size = 880MB

Eu também tenho um índice no arquivo operation_id.

PERGUNTA: Qual seria o plano ideal para consultar todos os distintosuser_idpara uma parte significativa dooperation_idconjunto (20%-60%) como:

SELECT DISTINCT user_id FROM user_operation WHERE operation_id < 500

É possível criar mais índices na tabela . Atualmente, o plano para a consulta é:

HashAggregate  (cost=196173.56..196347.14 rows=17358 width=4) (actual time=1227.408..1359.947 rows=598336 loops=1)
  ->  Bitmap Heap Scan on user_operation  (cost=46392.24..189978.17 rows=2478155 width=4) (actual time=233.163..611.182 rows=2518122 loops=1)
        Recheck Cond: (operation_id < 500)
        ->  Bitmap Index Scan on idx  (cost=0.00..45772.70 rows=2478155 width=0) (actual time=230.432..230.432 rows=2518122 loops=1)
              Index Cond: (operation_id < 500)

Esse plano de consulta é realmente ideal nessas circunstâncias? Quero dizer, não tenho certeza sobre a correção de usar Bitmap Heap Scan. Eu apreciarei todas as referências a artigos relevantes.

postgresql count

1 respostas

  1. Best Answer

    Qual seria o plano ideal para consultar todos os distintos user_id para uma parte significativa do operation_idconjunto (20%-60%).

    Use uma consulta recursiva:

    WITH RECURSIVE cte AS (
   (  -- parentheses are required
   SELECT user_id
   FROM   user_operations
   WHERE  operation_id < 500
   ORDER  BY user_id
   LIMIT  1
   )
   UNION ALL
   SELECT u.user_id
   FROM   cte, LATERAL (
      SELECT user_id
      FROM   user_operations
      WHERE  operation_id < 500
      AND    user_id > cte.user_id  -- lateral reference
      ORDER  BY user_id
      LIMIT  1
      ) u
   )
TABLE cte;

    Em combinação com um índice nas colunas nessa ordem (user_id, operation_id). Espero varreduras de índice que filtrem na segunda coluna. Estatísticas de tabela razoavelmente precisas são importantes, então o Postgres sabe que só terá que pular algumas linhas no índice para encontrar a próxima . Geralmente, pode -se querer aumentar a meta de estatísticas em particular para:user_idoperation_id

    ALTER TABLE user_operations ALTER operation_id SET STATISTICS 1000;

    Como existem apenas ~1000-2000 different operations, isso pode nem ser necessário, mas é um pequeno preço a pagar.

    Detalhes:

    Se o predicado operation_id < 500for estável (sempre o mesmo), torne-o um índice parcial apenas (user_id):

    CREATE INDEX foo ON user_operations (user_id) WHERE operation_id < 500;

    Então as estatísticas operation_idnão são mais relevantes para esta consulta.

    Mesmo que o predicado não seja estável, pode haver maneiras de otimizar - dependendo de toda a gama de condições possíveis e frequências de valor.

    O desempenho deve ser... delicioso .

    Eu otimizei a técnica nesta resposta relacionada no SO (com explicação detalhada):

    Se você tiver uma userstabela separada e uma grande parte de todos os usuários puder ser encontrada em sua amostra, estilos de consulta ainda mais rápidos serão possíveis. Detalhes na resposta vinculada.

    • 4

relate perguntas