Início / dba / Perguntas / 213327
Accepted
AlexGordon
Asked: 2018-07-27 08:19:35 +0800 CST

Como indexar duas tabelas para otimização de consulta JOINed

  • 772

Estou usando o PostgreSQL 9.5 no High Sierra.

Sobre as duas tabelas:

request_logs - ~ 26 mil linhas

response_logs - ~ 9 mil linhas

Eu tenho a seguinte consulta (com JOIN):

SELECIONAR
  req.uuid,
  res.status,
  req.método,
  req.requesturi,
  req.acessado,
  req.payload reqpayload,

  res.payload repayload,
  COUNT(*) OVER() AS total_rows
A PARTIR DE
  request_logs req
  LEFT OUTER JOIN response_logs res ON req.uuid = res.uuid
ONDE
   req.conta = 2 AND
   req.requesturi não é semelhante a '/v1/sessions%' AND
   req. acessado ENTRE “2018-01-01 15:04:05 +0000” e “2019-01-02 15:04:05+0000” E
   res.status NÃO É NULO E
   req.id do aplicativo = 1
ORDENAR POR
   acessou DESC LIMIT 1000

Como estou tentando otimizar a consulta, experimentei diferentes índices: Aqui está uma lista do que tentei:

Configuração 1:
 1. request_log.uuid (pkey, exclusivo)
 2. response_log.uuid (pkey, exclusivo, chave estrangeira)

Tempo de resposta méd. : 260ms

Configuração 2:
 1. request_log.uuid (pkey, exclusivo)
 2. request_log.applicationid
 3. response_log.uuid (pkey, exclusivo, chave estrangeira)

Tempo de resposta méd. : 230ms

Configuração 3:
 1. request_log.uuid (pkey, exclusivo)
 2. request_log.applicationid
 3. request_log.accessed (timestampz)
 4. response_log.uuid (pkey, exclusivo, chave estrangeira)

Tempo de resposta méd. : 230ms

Configuração 4:
 1. request_log.uuid (pkey, exclusivo)
 2. request_log.applicationid
 3. request_log.accessed (timestampz)
 4. request_log.accountid
 5. response_log.uuid (pkey, exclusivo, chave estrangeira)

Tempo de resposta méd. : 230ms

Configuração 5:
 1. request_log.uuid (pkey, exclusivo)
 2. request_log.applicationid, request_log.accessed (combinado)
 3. response_log.uuid (pkey, exclusivo, chave estrangeira)

Tempo de resposta méd. : 240ms

Como é visível no resultado, a indexação por applicationid(an int8) ajudou um pouco, enquanto a indexação por timestampz accessednão ajudou em nada. Talvez o mau desempenho seja devido ao JOIN? No geral, parece bastante lento e tento não pensar no que acontecerá quando essas tabelas contiverem milhões de registros (10M+).

Qual seria a melhor maneira de indexar essas tabelas para tornar essa consulta mais rápida?

EDITAR:

Aqui está EXPLAIN ANALYZEa última configuração:

Limite (custo=3489,80..3490,69 linhas=356 largura=823) (tempo real=241,152..241,345 linhas=1000 loops=1)
  -> Ordenar (custo=3489.80..3490.69 linhas=356 largura=823) (tempo real=241.150..241.288 linhas=1000 loops=1)
        Chave de classificação: DESC acessado por req.
        Método de classificação: top-N heapsort Memória: 2064kB
        -> WindowAgg (custo=1829.41..3474.71 linhas=356 largura=823) (tempo real=230.040..237.993 linhas=3951 loops=1)
              -> Hash Join (custo=1829.41..3470.26 linhas=356 largura=823) (tempo real=8.622..17.974 linhas=3951 loops=1)
                    Hash Cond: (res.uuid = req.uuid)
                    -> Seq Scan on response_logs res (custo=0,00..1604.21 linhas=8821 largura=758) (tempo real=0,006..4.527 linhas=9124 loops=1)
                          Filtro: (status NÃO É NULO)
                    -> Hash (custo=1816.39..1816.39 linhas=1042 largura=102) (tempo real=8.243..8.243 linhas=4046 loops=1)
                          Baldes: 4096 (originalmente 2048) Lotes: 1 (originalmente 1) Uso de memória: 1122kB
                          -> Bitmap Heap Scan on request_logs req (cost=105.85..1816.39 rows=1042 width=102) (tempo real=0.581..6.449 rows=4046 loops=1)
Tempo de execução: 242,154 ms

postgresql join

1 respostas

  1. Best Answer

    Para começar, acerte-se LEFT JOINe tente se livrar do total de linhas (conforme discutido nos comentários):

    Consulta

    Atualizado no seu comentário:

    Eu só quero retornar linhas request_logsque tenham uma entrada paralela response_logscom status não nulo:

    SELECT q.uuid
     , s.status
     , q.method
     , q.requesturi
     , q.accessed
     , q.payload reqpayload
     , s.payload respayload
--   , COUNT(*) OVER() AS total_rows  -- see below
FROM   request_logs q
JOIN   response_logs s USING (uuid)   -- not LEFT JOIN
WHERE  q.accountid = 2
AND    q.requesturi NOT ILIKE '/v1/sessions%'
AND    q.accessed BETWEEN '2018-01-01 15:04:05 +0' AND '2019-01-02 15:04:05+0'
AND    q.applicationid = 1
AND    s.status IS NOT NULL           -- see below
ORDER  BY q.accessed DESC
LIMIT  1001;                          -- see below

    Sua LEFT [OUTER] JOINqueima para uma planície [INNER] JOIN. Relacionado:

    COUNT(*) OVER() AS total_rowsé caro para contagens substancialmente maiores que LIMIT(e você espera "10m+ linhas"). Talvez seja bom o suficiente para usar LIMIT 1001, use apenas as primeiras 1000 linhas, verifique a contagem de linhas e, se for 1001, você saberá que há "mais de 1000 linhas correspondentes". Relacionado:

    Índices

    Se (como você comentou)

    as únicas 'partes móveis' nesta consulta são as datas (de e até) que são comparadas com as

    ... e mais do que algumas linhas são excluídas com os outros predicados em applicationid, requesturie accountid, então um índice parcial deve ajudar a ler o desempenho (muito):

    CREATE INDEX ON request_logs (uuid, accessed DESC)
WHERE  accountid = 2
AND    requesturi NOT ILIKE '/v1/sessions%'
AND    applicationid = 1

    E se uma porcentagem substancial de linhas for eliminada por status IS NOT NULL, também:

    CREATE INDEX ON response_logs (uuid)
WHERE  status IS NOT NULL;

    Pode valer a pena anexar as colunas statuse payloadcomo colunas de índice se você puder obter varreduras somente de índice . Algumas pré-condições se aplicam.

    • 3

relate perguntas