Início / dba / Perguntas / 317051
Accepted
Mahmoud
Asked: 2022-09-19 03:25:12 +0800 CST

Consulta de seleção SQL simples do Postgres, às vezes, a latência salta para 900ms

  • 772

Eu tenho uma tabela "somente inserir" que armazena informações do usuário

  • id bigint-> Chave primária, não nula
  • user_id bigint-> chave estrangeira, não nula
  • first_name character varyingnull
  • nome_do_meio character varyingnulo
  • last_name character varyingnão é nulo
  • e-mail character varyingnão nulo
  • data_nascimento datenão nula
  • phone_number character varyingnão nulo
  • mobile_number character varyingnão nulo
  • street_name character varyingnull

e uma tabela de usuário (inserir + atualizar) (na maioria das vezes, não consulto esta tabela)

  • idchave primária, não nula
  • created_atdata, não nula
  • modified_atdata, nulo

Eu tenho um índice na birth_datecoluna na user_infotabela e minha consulta se parece com isso

SELECT * FROM user_info WHERE birth_date = @p1 and (first_name = @p2 or email = @p3) and (last_name = @p4 or email =@p3);

Na maioria das vezes, a latência de consulta no ambiente de produção é 7-26ms, mas às vezes, e está acontecendo com frequência, na verdade, a latência salta para, 900msentão vejo nos logs 3-4 consultas com latência 180, 200, 700, 900mse depois volta para 7-26ms.

  • A tabela user_infoé somente para inserção, portanto, não há atualizações nem exclusões
  • Estou usando o PostgreSQL 11
  • A tabela user_infocontém 5 milhões de registros, a distribuição de dados parece boa na produção, a maioria birth_date(s)está entre 200 e 1.000 registros, mas apenas uma data de nascimento específica tem 110 mil registros (seria esse o problema?)
  • as configurações do servidor são o padrão conforme especificado em (servidor único do Azure Postgres)
  • As especificações do servidor são (4 vCPU, 20 GB de memória, Gen 5)
  • A conexão simultânea máxima com o banco de dados de produção é de 20 por segundo
  • O melhor cenário é apenas uma consulta SQL executada (aquela anexada nesta pergunta)
  • O pior cenário é Primeiro: Consulta SQL anexada nesta pergunta, Segundo: Inserir consulta na usertabela, Terceiro Inserir consulta na user_infotabela
  • Outro cenário é Primeiro: Consulta SQL anexada nesta pergunta, Segundo: Inserir consulta na user_infotabela, Terceiro: atualizar o horário modificado na usertabela
  • consultando a pg_stat_user_tablestentativa de entender quantas varreduras sequenciais vs varreduras sequenciais produziram o seguinte resultado: insira a descrição da imagem aqui
  • executando duas consultas explain analyzeda minha máquina local usando pgAdmino servidor postgres do azure, uma com a data de nascimento que contém mais contagem e outra com outra data de nascimento, o restante dos valores é completamente aleatório e gerou os seguintes resultados

insira a descrição da imagem aqui

Meu problema é:

  • Eu preciso que a latência não exceda determinado limite

Minhas perguntas são:

  • Qual a razão por trás do salto de latência na user_infomesa?
  • Quebrar o relacionamento entre as duas tabelas resolveria o problema? Talvez ao fazer atualizações na usertabela da user_infotabela precise atualizar a coluna da chave estrangeira, porque a atualização é na verdade inserir e excluir o que causa deadrows na tabela?
  • É a distribuição de dados? E o que posso fazer para melhorar a latência?

ATUALIZADA:

Habilitei o auto_explain em produção, usei uma condição para logar somente consultas com latência > 40ms. E executei script automatizado que consulta os dados existentes, 4 deles com data de nascimento que possui 100K linha. E, como esperado, vi apenas logs para esse valor de data de nascimento específico:

2022-09-19 {TIME REDACTED}-LOG:  duration: 42.421 ms  plan:
    Query Text: 
                    select * from unf.user_info unf
                    where (
                     unf.birth_date = $1
                     and ( unf.first_name = $2 or unf.email = $3 )
                     and ( unf.last_name = $4 or unf.email = $3 ))
    Index Scan using idx_b_date on unf.user_info unf  (cost=0.43..76483.96 rows=1 width=112) (actual time=0.044..42.411 rows=2 loops=1)
      Output: id, user_id, birth_date, first_name, last_name, email,  phone_number, mobile_number, street_name, created_date
      Index Cond: (unf.birth_date = '{REDACTED} 00:00:00'::timestamp without time zone)
      Filter: ((((unf.last_name)::text = '{REDACTED}'::text) OR ((unf.email)::text = '{REDACTED}'::text)) AND (((unf.first_name)::text = '{REDACTED}'::text) OR ((unf.email)::text = '{REDACTED}'::text)))
      Rows Removed by Filter: 119228
      Buffers: shared hit=11025
2022-09-19 {TIME REDACTED}--LOG:  duration: 41.370 ms  plan:
    Query Text: 
                    select * from unf.user_info unf
                    where (
                     unf.birth_date = $1
                     and ( unf.first_name = $2 or unf.email = $3 )
                     and ( unf.last_name = $4 or unf.email = $3 ))
    Index Scan using idx_b_date on unf.user_info unf  (cost=0.43..76483.96 rows=1 width=112) (actual time=0.087..41.359 rows=2 loops=1)
      Output: id, user_id, birth_date, first_name, last_name, email,  phone_number, mobile_number, street_name, created_date
      Index Cond: (unf.birth_date = '{REDACTED} 00:00:00'::timestamp without time zone)
      Filter: ((((unf.last_name)::text = '{REDACTED}'::text) OR ((unf.email)::text = '{REDACTED}'::text)) AND (((unf.first_name)::text = '{REDACTED}'::text) OR ((unf.email)::text = '{REDACTED}'::text)))
      Rows Removed by Filter: 119228
      Buffers: shared hit=11025
2022-09-19 {TIME REDACTED}--LOG:  duration: 41.709 ms  plan:
    Query Text: 
                    select * from unf.user_info unf
                    where (
                     unf.birth_date = $1
                     and ( unf.first_name = $2 or unf.email = $3 )
                     and ( unf.last_name = $4 or unf.email = $3 ))
    Index Scan using idx_b_date on unf.user_info unf  (cost=0.43..76483.96 rows=1 width=112) (actual time=0.079..41.682 rows=2 loops=1)
      Output: id, user_id, birth_date, first_name, last_name, email,  phone_number, mobile_number, street_name, created_date
      Index Cond: (unf.birth_date = '{REDACTED} 00:00:00'::timestamp without time zone)
      Filter: ((((unf.last_name)::text = '{REDACTED}'::text) OR ((unf.email)::text = '{REDACTED}'::text)) AND (((unf.first_name)::text = '{REDACTED}'::text) OR ((unf.email)::text = '{REDACTED}'::text)))
      Rows Removed by Filter: 119228
      Buffers: shared hit=11025
2022-09-19 {TIME REDACTED}--LOG:  duration: 40.581 ms  plan:
    Query Text: 
                    select * from unf.user_info unf
                    where (
                     unf.birth_date = $1
                     and ( unf.first_name = $2 or unf.email = $3 )
                     and ( unf.last_name = $4 or unf.email = $3 ))
    Index Scan using idx_b_date on unf.user_info unf  (cost=0.43..76483.96 rows=1 width=112) (actual time=0.057..40.568 rows=2 loops=1)
      Output: id, user_id, birth_date, first_name, last_name, email,  phone_number, mobile_number, street_name, created_date
      Index Cond: (unf.birth_date = '{REDACTED} 00:00:00'::timestamp without time zone)
      Filter: ((((unf.last_name)::text = '{REDACTED}'::text) OR ((unf.email)::text = '{REDACTED}'::text)) AND (((unf.first_name)::text = 'n'::text) OR ((unf.email)::text = '{REDACTED}'::text)))
      Rows Removed by Filter: 119228
      Buffers: shared hit=11025
postgresql postgresql-performance

2 respostas

  1. A condição de índice da consulta é desnecessáriamente complexa:

    -- version 1, original
WHERE birth_date = @p1 
  AND ( first_name = @p2 OR email = @p3 )
  AND ( last_name = @p4 OR email = @p3 )

    É equivalente a:

    -- version 2
WHERE birth_date = @p1 
  AND (  first_name = @p2 AND last_name = @p4
      OR email = @p3
      )

    e para isso (parênteses redundantes, mostrados apenas para maior clareza):

    -- version 3
WHERE ( birth_date = @p1 AND first_name = @p2 AND last_name = @p4 )
   OR ( birth_date = @p1 AND email = @p3 )

    A 3ª variação acima facilita a identificação de melhores candidatos ao índice, tanto para humanos quanto para o planejador do Postgres. O planejador tem alguns recursos de reescrever/simplificar condições booleanas, mas não pode identificar todas as simplificações possíveis.

    Portanto, sugiro que você adicione esses dois índices:

    INDEX (birth_date, first_name, last_name)
INDEX (birth_date, email)

    e marque a 3ª opção acima e a reescrita usando UNION:

    -- version 3
SELECT * 
FROM user_info 
WHERE birth_date = @p1 AND first_name = @p2 AND last_name = @p4
   OR birth_date = @p1 AND email = @p ;

-- version 4: UNION
SELECT * 
FROM user_info 
WHERE birth_date = @p1 AND first_name = @p2 AND last_name = @p4
UNION
SELECT * 
FROM user_info 
WHERE birth_date = @p1 AND email = @p3  ;
    • 2
  2. Best Answer

    Em sua consulta lenta, há 1.970 linhas com birth_date = 'REDACTED', enquanto na consulta lenta, há 124.287. Portanto, é natural que a verificação do índice demore mais.

    Como você coloca um ORem sua WHEREcondição (o que dificulta a indexação), a única melhoria simples que consigo pensar é adicionar as colunas de filtro ao índice:

    CREATE INDEX ON user_info (birth_date, first_name, email);

    Talvez você possa reescrever a consulta para evitar o OR, então mais é possível.

    • 1

relate perguntas