Mahmoud's questions

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:
• 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

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