Perguntas[postgresql-performance](dba)

Eu tenho uma junção de tabela grande nas chaves que não usa o índice.

Consulte e planeje aqui : https://explain.dalibo.com/plan/15a6a91492g8g935#plan/node/5

Ambas as tabelas possuem um índice na chave, assim:

CREATE UNIQUE INDEX "stage_fact_revenue_900_idx_a" ON "stage"."stage_fact_revenue_900_add_dims" USING BTREE ("date","billed_relation_id","subject_client_id","identifier","revenue_type");
CREATE UNIQUE INDEX "fact_revenue_idx_a" ON "star"."fact_revenue" USING BTREE ("date","identifier","billed_relation_id","subject_client_id","revenue_type");

mas o índice não é usado (consulte o plano de consulta).

Alguma pista de por que isso não está acontecendo? As maiores etapas são classificadas, posso pré-classificar as tabelas para evitar isso?

CREATE OR REPLACE FUNCTION public.fn_question_3_3()
RETURNS TABLE(name character varying, surname character varying, person_t person_type)
AS
$$
DECLARE
       pt person_type;
BEGIN
     RETURN QUERY
     SELECT DISTINCT pr.name, pr.surname, pr.person_t
     FROM "Person" pr
     CROSS JOIN "Professor" prof 
     CROSS JOIN "LabTeacher" lt 
     CROSS JOIN "Student" st;
           IF pr.amka = prof.amka THEN
                pt = 'Professor';
           ELSIF pr.amka = lt.amka THEN
                pt = 'LabTeacher';
           ELSE
                pt = 'Student';
           END IF;
           pt = pr.person_t;
END;
$$

LANGUAGE 'plpgsql';

SELECT*FROM fn_question_3_3()

Então, estou tentando fazer uma consulta simples aqui, onde verifico o amka da pessoa e, se for igual a uma das outras tabelas, atualiza a coluna person_t e retorna nome, sobrenome e person_t.

O problema é que a consulta SELECT * FROM fn_wuestion_3_3()leva mais de 10 minutos para ser concluída e esse não deveria ser o caso de uma consulta como essa. Qualquer ajuda seria apreciada, pois sou muito novo em bancos de dados e postgresql/pgadmin4.

tabela: "Pessoa" tem 4820 linhas
tabela: "Professor" tem 123 linhas
tabela: "LabProfessor" tem 77 linhas
tabela: "Estudante" tem 3194 linhas

As definições de índice são pr para "Pessoa", prof para "Professor", st para tabelas "Estudante" e lt para "Professor de Laboratório", respectivamente.

Estou lutando para otimizar a criação correta do índice para minha estrutura de dados. Estou tentando indexar uma grande quantidade de dados (~ 100B registros) do tipo de dados json:

{
  "email": "[email protected]",
  "first_domain": "q.w.e.r.t.y.com"
  "second_domain": "a.s.d.f.g.h.j.com"
}

Uma consulta simples pode parecer

SELECT * 
from table 
WHERE email = '[email protected]'

OU

SELECT * 
from table 
WHERE first_domain LIKE '%.r.t.y.com'
   OR second_domain LIKE '%.r.t.y.com'

Anteriormente, eu tokenizaria todos os domínios e os colocaria juntos em uma matriz no mongo

{
  "values": ["[email protected]", "moc.y.t.r.e.w.q", moc.j.h.g.f.d.s.a]
}

Mas, tendo uma quantidade de dados, o Mongo não pode armazenar todos os valores na memória e o desempenho de gravação diminui.

Então, estou planejando tentar o índice Postgres e GIN. Por favor, sugira-me uma criação de índice adequada para tal situação.

Eu tenho um script que executa aproximadamente 60 mil consultas de atualização com cláusulas where em uma tabela com cerca de 5 milhões de registros. As consultas de atualização provavelmente atualizarão todas as linhas para cada caso. Existe uma maneira melhor de otimizar isso, pois está em execução há horas? (Não há índice na coluna da cláusula where em myTable, no entanto, ela possui chave primária)

update myTable set Col1 = Val1 where Col1 = Unq1
update myTable set Col2 = Val4 where Col2 = Unq23
update myTable set Col3 = Val8 where Col3 = Unq45
.......

Eu tenho 10-15 mesa grande, 5GB a 30GB. E o resto deles menos de 1GB. Alguns dados de tabelas são enormes e onde diariamente são geradas 500.000 tuplas mortas. E o autovacuum começa quase 7 dias depois, quando as tuplas mortas são mais de 3,5 milhões. Este banco de dados possui cerca de 60 tabelas. Aqui o limite para o banco de dados é: 20%.

Quero alterar o limite de autovacuum de 20% para 1%. Existe algum impacto no desempenho do sistema (já que para tabelas menores ele será executado com frequência). Qual deve ser o valor do parâmetro maintanance_work_mem & work_mem para isso?

Eu tenho uma ticketing_systemtabela e tento inserir mais de 10 milhões de linhas. Então isso custou 50 segundos. Mas eu preciso de quase um máximo de 10-15 segundos.

CREATE TABLE ticketing_system (
  ticket_id UUID not null default uuid_generate_v4(),
  count int,
);

Esta é uma estrutura de tabela. Esta é uma estrutura de tabela. Eu apenas uso esta consulta.

INSERT INTO ticketing_system(count) 
select * from generate_series(1,10000000);

Esta consulta insere dados em quase 50 segundos.

explain analyze INSERT INTO ticketing_system(count) select * from generate_series(1,10000000);
                                                                  QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------
 Insert on ticketing_system  (cost=0.00..125000.00 rows=0 width=0) (actual time=52620.980..52620.981 rows=0 loops=1)
   ->  Function Scan on generate_series  (cost=0.00..125000.00 rows=10000000 width=20) (actual time=765.900..44678.638 rows=10000000 loops=1)
 Planning Time: 0.056 ms
 JIT:
   Functions: 4
   Options: Inlining false, Optimization false, Expressions authentic, Deforming true
   Timing: Generation 0.578 ms, Inlining 0.000 ms, Optimization 0.174 ms, Emission 1.199 ms, Total 1.952 ms
 Execution Time: 52631.718 ms
(8 rows)

Como otimizar esta consulta? Eu larguei índices.

Estou com um pequeno problema no trabalho, o ORM está gerando uma consulta muito estranha e preciso otimizar um pouco o resultado dele. Eu escrevi uma versão mais legível da consulta e o resultado é semelhante, mas o tempo de execução ainda é o mesmo.

Consulta Original
Explicar Detalhado

Esta é a consulta formatada atual, ela retorna o valor correto, mas leva cerca de 13 segundos para ser executada.

select count(*)
from reservation as r
         JOIN companyclient as cc ON r.companyclientid = cc.companyclientid
         JOIN reservationitem as ri ON r.reservationid = ri.reservationid
         LEFT JOIN billingaccount as bi
                   ON (r.reservationid = bi.reservationid AND cc.companyclientid = bi.companyclientid AND
                       bi.propertyid = '84'
                       AND NOT bi.isdeleted
                       AND bi.groupkey = bi.billingaccountid
                       AND bi.billingaccounttypeid = '3'
                       AND bi.reservationid IS NOT NULL
                       AND bi.statusid = '1')
WHERE r.propertyid = '84'
  AND NOT r.isdeleted
  AND r.companyclientid is not null
  AND ri.tenantid = '025aa64f-67fb-4c23-b975-2b0fc3f5d65a'
  AND NOT ri.isdeleted
  AND ri.reservationitemstatusid NOT IN (6, 3, 7, 8);

Explicação detalhada

Esta é a versão que escrevi na tentativa de otimizar (de 13 segundos para 5), ​​evitando as condições dentro da junção esquerda, mas o resultado é diferente da consulta original. A primeira consulta retorna 29490 e a segunda consulta retorna 29397.

select count(*)
from reservation as r
         JOIN companyclient as cc ON r.companyclientid = cc.companyclientid
         JOIN reservationitem as ri ON r.reservationid = ri.reservationid
         LEFT JOIN billingaccount as bi
                   ON (r.reservationid = bi.reservationid AND cc.companyclientid = bi.companyclientid)
WHERE r.propertyid = '84'
  AND NOT r.isdeleted
  AND r.companyclientid is not null
  AND ri.tenantid = '025aa64f-67fb-4c23-b975-2b0fc3f5d65a'
  AND NOT ri.isdeleted
  AND ri.reservationitemstatusid NOT IN (6, 3, 7, 8)
  AND (bi is null or bi.propertyid = '84'
  AND NOT bi.isdeleted
  AND bi.groupkey = bi.billingaccountid
  AND bi.billingaccounttypeid = '3'
  AND bi.reservationid IS NOT NULL
  AND bi.statusid = '1')

Explicação detalhada

Minha dúvida é, como posso otimizar a primeira consulta, já tentei alguns métodos mas não tive muito sucesso. Entendo que a contagem é linear e seu tempo é baseado no tamanho do retorno da consulta, mas imagino que seja muito lento para uma consulta de 30 mil linhas.

Neste caso específico, preciso do total de itens para calcular o número de páginas na paginação de deslocamento limite.

Desde já agradeço a todos que tiveram a paciência de ler, aceito qualquer ajuda.

Tamanho das tabelas usadas no exemplo:

• reserva: 288549 linhas
• empresacliente: 50614 linhas
• item de reserva: 387820 linhas
• conta de cobrança: 772521 linhas

Portanto, esta é a minha primeira experiência com big data. Eu tenho uma ticketing_systemtabela e inseri um bilhão (um bilhão) de dados falsos na tabela.

CREATE TABLE ticketing_system (
    id BIGSERIAL,
    ticket_id UUID not null default uuid_generate_v4(),
    count int,
    created_at timestamptz NOT NULL
);

Eu preciso de uma resposta do Postgresql de cerca de 10-30 ms ao pesquisar a ticket_idcláusula wherewith.Eu criei o índice BRIN CREATE INDEX in_ticketing_system_brin ON ticketing_system USING brin(ticket_id);. Mas isso não está me ajudando.

explain analyze select * from ticketing_system where ticket_id = '09830cb7-37f2-4951-8910-1661b1358b99';
                                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1217.39..1854198.38 rows=1 width=36) (actual time=997.056..84730.718 rows=1 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Bitmap Heap Scan on ticketing_system  (cost=217.39..1853198.28 rows=1 width=36) (actual time=56707.851..84601.029 rows=0 loops=3)
         Recheck Cond: (ticket_id = '09830cb7-37f2-4951-8910-1661b1358b99'::uuid)
         Rows Removed by Index Recheck: 46103039
         Heap Blocks: lossy=366097
         ->  Bitmap Index Scan on in_ticketing_system_brin  (cost=0.00..217.39 rows=3544816 width=0) (actual time=153.203..153.209 rows=11525760 loops=1)
               Index Cond: (ticket_id = '09830cb7-37f2-4951-8910-1661b1358b99'::uuid)
 Planning Time: 3.167 ms
 JIT:
   Functions: 6
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 12.892 ms, Inlining 491.333 ms, Optimization 92.106 ms, Emission 91.551 ms, Total 687.882 ms
 Execution Time: 84833.538 ms
(15 rows)

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

Trabalhei em um servidor de banco de dados que hospeda vários bancos de dados grandes, incluindo bancos de dados que não são mais usados. Eu queria descartar um desses bancos de dados antigos e já demorou mais do que eu esperava (mais de 40 segundos), então notei que o desempenho do servidor foi significativamente impactado e até parou de responder. Felizmente, consegui encerrar a consulta eventualmente e colocar o servidor de volta em um estado estável.

Você pode me ajudar a entender:

• Qual pode ser a razão para o impacto no desempenho? Eu teria assumido que largar um banco de dados é barato e rápido.
• Qual é a melhor abordagem para limpar um DB-Server? Seria melhor descartar cada tabela sequencialmente antes de descartar o banco de dados ou há outras coisas que eu possa fazer para descartar um banco de dados com segurança e rapidez?

Obrigado!