All perguntas(dba)

Estou usando uma instância Aurora PostgreSQL v15 gerenciada pela AWS como catálogo para um grande número de objetos S3. A level1_datasettabela possui cerca de 2 bilhões de linhas e seu esquema possui uma metadatacoluna JSONB. Um bug de software antigo fazia com que a string nullfosse gravada na metadatacoluna (em vez de deixá-la vazia) quando nenhum metadado deveria ser gravado. Cerca de um bilhão de linhas contêm a picada nulle quero limpá-la com:

UPDATE public.level1_dataset
SET "metadata" = NULL
WHERE "metadata"::text = 'null';

O banco de dados está hospedado em um sistema db.r6g.2xlargecom 8 núcleos vCPU e 64 GB de memória. Com essa configuração, e deixando todos os ajustes nos padrões, estou obtendo cerca de 42 segundos/milhão de linhas. É possível alterar temporariamente os núcleos da CPU e a memória para esta tarefa de limpeza.

Qual é a maneira mais eficiente de proceder?

ATUALIZAÇÃO: Uma das abordagens de @laurenz-albe é fazer isso em lotes. Foi assim que fiz porque, no meu caso, “id” é UUID, não inteiro. O SELECT tem uma penalidade de 10% no meu caso de uso.

UPDATE public.level1_dataset
SET "metadata" = NULL
WHERE "id" IN (SELECT "id"
    FROM public.level1_dataset
    WHERE "metadata"::text = 'null'
    LIMIT 10000000);

É possível especificar pgbouncer reserve_pool_timeoutem milissegundos, tentei especificá-lo com '500 ms', mas gerando um erro e tentei especificar 0,5, desta vez sem erro, mas não consegui descobrir que está realmente funcionando ou apenas desativei o tempo limite, mas reserve_pool está sendo utilizado .

Por que tantas pessoas preferem bancos de dados colunares? No meu entender, as pessoas preferem bancos de dados colunares porque são mais rápidos para consultas devido ao fato de que a CPU seria capaz de armazenar em cache os próximos valores de forma previsível.

Estou analisando o Query Performance Insight para meu banco de dados de réplica de leitura. Mas as informações mostradas são estatísticas de desempenho do banco de dados primário . Como posso obter as estatísticas de desempenho da réplica de leitura?

Eu tenho uma consulta simples, como
select * from MyTable where MyTableID=@p1
onde sp_BlitzCache me avisa sobre "Long Running with Low CPU". A tabela possui um índice clusterizado em MyTableID, que é um identificador/GUID exclusivo.

Isso pode ser causado pela execução da instrução com muita frequência?
Sim, este é o Entity Framework, e estamos tentando torná-lo mais rápido, sem alterar muito o código C# (pois leva muito tempo).

Atualmente rodando em vCores = 80, e isso é "um pouco" caro. Tentei seguir https://www.brentozar.com/blitzcache/long-running-low-cpu/ , mas as diretrizes não são claras para mim.
sp_whoisactive mostra muitas instruções com Async_Network_IO
sp_BlitzFirst mostra CXSYNC_PORT como o principal tipo de espera, de longe, mas não consigo encontrar nenhuma informação sobre isso e não sei se está relacionado a esta consulta ou a uma das muitas outras consultas em execução .

Tentei encontrar um plano de execução, mas sp_BlitzIndex mostra apenas a instrução SQL. Mas, considerando isso, recebo este plano: https://www.brentozar.com/pastetheplan/?id=r11D40QQ0 Ele foi executado 13.441.177 vezes em meu servidor de teste que está ativo há 55 horas.

Por que recebo "CPU prolongada e baixa" e como faço para corrigir isso?
Também vejo INSERTs e UPDATEs simples recebendo esse rótulo.
Todos esses "tempos de baixa CPU" podem ser causados ​​por um disco lento?

Existem vários posts bons, por exemplo:

Devo usar um índice composto ou de coluna única?

mostrando que se você tiver um índice composto nas colunas (A,B) e um índice único em (A), o postgres usará o índice composto (para melhorar o cache), e o índice único é supérfluo e pode ser descartado.

Ao testar isso, percebo que o postgres às vezes usará o índice único quando o composto estiver disponível, mas não sei se isso significa que ainda precisamos (para desempenho da consulta) de ambos os índices.

Considere o seguinte:

CREATE TABLE public.events (
    id serial4 NOT NULL,
    reference int4 NOT NULL,
    "name" varchar(255) NULL,
    bet_source varchar NULL,
    CONSTRAINT events_pkey PRIMARY KEY (id),
    CONSTRAINT events_reference_betsource_key UNIQUE (reference, bet_source)
);
CREATE INDEX event_reference ON public.events USING btree (reference);

Aqui, a pequena diferença é que o índice composto é único e foi declarado como uma restrição (que presumo ser o mesmo que criar um índice único).

explain (analyze, buffers)
select * from events e where e.reference = 123;

Index Scan using event_reference on events e  (cost=0.28..8.30 rows=1 width=51) (actual time=0.006..0.007 rows=0 loops=1)
  Index Cond: (reference = 123)
  Buffers: shared hit=2
Planning:
  Buffers: shared hit=69 dirtied=1
Planning Time: 0.432 ms
Execution Time: 0.036 ms

Aqui podemos ver no plano de explicação que o postgres optou por usar o índice único. Normalmente, quando testei isso em outras tabelas, o postgres escolhe o índice composto neste caso.

A questão é,

1. estou seguro para remover event_reference sem afetar o desempenho da consulta? Como não tenho acesso aos dados de produção (ou a uma cópia deles), seria difícil testar isso com dados reais.

2. é uma restrição exclusiva igual a um índice exclusivo (para fins de consulta e otimização de inserção)

eu tenho essas tabelas

pontuações_do_filme

filmes

Aqui sabemos que user_id 1 avaliou Interestelar e Cavaleiro das Trevas com 10 e 5 apropriadamente. Sabendo disso posso recomendar a este usuário 2 filmes - O Iluminado e Forrest Gump porque eles têm uma coleção semelhante aos dois primeiros filmes. Mas como ele avaliou Interestelar acima de O Cavaleiro das Trevas, posso adivinhar que ele gosta mais da coleção 1 do que da coleção 2 .

Então a ordem deveria ser:

1. The Shining (mesma coleção de Interestelar que o usuário avaliou como 10)
2. Forrest Gump (mesma coleção do Cavaleiro das Trevas que o usuário avaliou como 5)

Fundo

Temos contratos de serviço que podem começar e terminar em qualquer data arbitrária. Normalmente, porém, os contratos terminam em “X anos menos um dia”. Por exemplo, intervalos de datas típicos:

• Data de início = 08/01/2018, Data de término = 07/01/2023
• Data de início = 01/06/2021, Data de término = 31/05/2023

No entanto, alguns contratos abrangem intervalos de datas muito arbitrários. Alguns são muito curtos, mas também podem durar alguns anos:

• Data de início = 30/04/2024, Data de término = 16/07/2024
• Data de início = 07/10/2024, Data de término = 30/10/2024
• Data de início = 15/03/2021, Data de término = 25/12/2025

Requerimento

Calcule o número de meses que o contrato abrange, para determinar quantas faturas mensais serão criadas durante a vigência do contrato. Suponha que as faturas sejam geradas no último dia do mês, portanto, um contrato com data de início em 15 de abril teria sua primeira fatura gerada em 30 de abril para a "Fatura de abril".

Problema

Usar apenas DATEDIFF(MONTH, @Date1, @Date2) produz resultados que nem sempre estão alinhados com o requisito.

Quando digo abaixo "produz um resultado incorreto", não quero dizer que a DATEDIFF()função esteja errada, estou dizendo que não é o resultado que preciso para minha exigência.

• PRINT DATEDIFF(MONTH, '2018-01-08', '2023-01-07')produz um resultado correto de 60 meses.
• PRINT DATEDIFF(MONTH, '2021-06-01', '2023-05-31')produz um resultado incorreto de 23 meses, onde preciso de um resultado de 24.
• PRINT DATEDIFF(MONTH, '2024-10-07', '2024-10-30')produz um resultado incorreto de 0, onde preciso de um resultado de 1.
• PRINT DATEDIFF(MONTH, '2024-04-15', '2024-07-16')produz um resultado incorreto de 3, onde preciso de um resultado de 4
• PRINT DATEDIFF(MONTH, '2024-10-07', '2024-10-30')produz um resultado incorreto de 0, onde preciso de um resultado de 1.
• PRINT DATEDIFF(MONTH, '2024-09-15', '2024-10-15')produz um resultado incorreto de 1, onde preciso de um resultado de 2.

Portanto, não posso apenas + 1o DATEDIFF()resultado para todos os casos. Quando um contrato começa e termina no mesmo mês do mesmo ano, DATEDIFF()produz um in

Solução atual

Esta é a minha solução atual:

PRINT CASE
    WHEN MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
        -- Special case when a contract starts and ends in the same month and year.
        THEN DATEDIFF(MONTH, @StartDate, @EndDate) + 1
    WHEN MONTH(@StartDate) = MONTH(@EndDate)
        THEN DATEDIFF(MONTH, @StartDate, @EndDate) 
    ELSE
        DATEDIFF(MONTH, @StartDate, @EndDate) + 1
    END

Esta é minha solução atual na forma de 5 testes de diferentes períodos:

DECLARE @StartDate AS DATE = '2018-01-08'
DECLARE @EndDate AS DATE = '2023-01-07'

PRINT '-------------------------------------------------------'
PRINT 'Test #1: @StartDate = 2018-01-08, @EndDate = 2023-01-07'
PRINT '-------------------------------------------------------'
PRINT ''

IF MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
    PRINT 'The dates contain the same month and year. We are adding 1 to the DATEDIFF() result.'
ELSE IF
    MONTH(@StartDate) = MONTH(@EndDate)
    PRINT 'The dates contain the same months. We are NOT adding 1 to the DATEDIFF() result.'
ELSE
    PRINT 'The dates contain the different months. We are adding 1 to the DATEDIFF() result.'

PRINT CASE
        WHEN MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
            -- Special case when a contract starts and ends in the same month and year.
            THEN DATEDIFF(MONTH, @StartDate, @EndDate) + 1
        WHEN MONTH(@StartDate) = MONTH(@EndDate)
            THEN DATEDIFF(MONTH, @StartDate, @EndDate) 
        ELSE
            DATEDIFF(MONTH, @StartDate, @EndDate) + 1
        END

PRINT ''
PRINT '-------------------------------------------------------'
PRINT 'Test #2: @StartDate = 2021-06-01, @EndDate = 2023-05-31'
PRINT '-------------------------------------------------------'
PRINT ''

SET @StartDate = '2021-06-01'
SET @EndDate = '2023-05-31'

IF MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
    PRINT 'The dates contain the same month and year. We are adding 1 to the DATEDIFF() result.'
ELSE IF
    MONTH(@StartDate) = MONTH(@EndDate)
    PRINT 'The dates contain the same months. We are NOT adding 1 to the DATEDIFF() result.'
ELSE
    PRINT 'The dates contain the different months. We are adding 1 to the DATEDIFF() result.'

PRINT CASE
        WHEN MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
            -- Special case when a contract starts and ends in the same month and year.
            THEN DATEDIFF(MONTH, @StartDate, @EndDate) + 1
        WHEN MONTH(@StartDate) = MONTH(@EndDate)
            THEN DATEDIFF(MONTH, @StartDate, @EndDate) 
        ELSE
            DATEDIFF(MONTH, @StartDate, @EndDate) + 1
        END

PRINT ''
PRINT '-------------------------------------------------------'
PRINT 'Test #3: @StartDate = 2024-04-30, @EndDate = 2024-07-16'
PRINT '-------------------------------------------------------'
PRINT ''

SET @StartDate = '2024-04-30'
SET @EndDate = '2024-07-16'

IF MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
    PRINT 'The dates contain the same month and year. We are adding 1 to the DATEDIFF() result.'
ELSE IF
    MONTH(@StartDate) = MONTH(@EndDate)
    PRINT 'The dates contain the same months. We are NOT adding 1 to the DATEDIFF() result.'
ELSE
    PRINT 'The dates contain the different months. We are adding 1 to the DATEDIFF() result.'

PRINT CASE
        WHEN MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
            -- Special case when a contract starts and ends in the same month and year.
            THEN DATEDIFF(MONTH, @StartDate, @EndDate) + 1
        WHEN MONTH(@StartDate) = MONTH(@EndDate)
            THEN DATEDIFF(MONTH, @StartDate, @EndDate) 
        ELSE
            DATEDIFF(MONTH, @StartDate, @EndDate) + 1
        END

PRINT ''
PRINT '-------------------------------------------------------'
PRINT 'Test #4: @StartDate = 2024-10-07, @EndDate = 2024-10-30'
PRINT '-------------------------------------------------------'
PRINT ''

SET @StartDate = '2024-10-07'
SET @EndDate = '2024-10-30'

IF MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
    PRINT 'The dates contain the same month and year. We are adding 1 to the DATEDIFF() result.'
ELSE IF
    MONTH(@StartDate) = MONTH(@EndDate)
    PRINT 'The dates contain the same months. We are NOT adding 1 to the DATEDIFF() result.'
ELSE
    PRINT 'The dates contain the different months. We are adding 1 to the DATEDIFF() result.'

PRINT CASE
        WHEN MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
            -- Special case when a contract starts and ends in the same month and year.
            THEN DATEDIFF(MONTH, @StartDate, @EndDate) + 1
        WHEN MONTH(@StartDate) = MONTH(@EndDate)
            THEN DATEDIFF(MONTH, @StartDate, @EndDate) 
        ELSE
            DATEDIFF(MONTH, @StartDate, @EndDate) + 1
        END

PRINT ''
PRINT '-------------------------------------------------------'
PRINT 'Test #5: @StartDate = 2024-09-15, @EndDate = 2024-10-15'
PRINT '-------------------------------------------------------'
PRINT ''

SET @StartDate = '2024-09-15'
SET @EndDate = '2024-10-15'

IF MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
    PRINT 'The dates contain the same month and year. We are adding 1 to the DATEDIFF() result.'
ELSE IF
    MONTH(@StartDate) = MONTH(@EndDate)
    PRINT 'The dates contain the same months. We are NOT adding 1 to the DATEDIFF() result.'
ELSE
    PRINT 'The dates contain the different months. We are adding 1 to the DATEDIFF() result.'

PRINT CASE
        WHEN MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
            -- Special case when a contract starts and ends in the same month and year.
            THEN DATEDIFF(MONTH, @StartDate, @EndDate) + 1
        WHEN MONTH(@StartDate) = MONTH(@EndDate)
            THEN DATEDIFF(MONTH, @StartDate, @EndDate) 
        ELSE
            DATEDIFF(MONTH, @StartDate, @EndDate) + 1
        END

Resultados:

-------------------------------------------------------
Test #1: @StartDate = 2018-01-08, @EndDate = 2023-01-07
-------------------------------------------------------
The dates contain the same months. We are NOT adding 1 to the DATEDIFF() result.
60
 
-------------------------------------------------------
Test #2: @StartDate = 2021-06-01, @EndDate = 2023-05-31
-------------------------------------------------------
The dates contain the different months. We are adding 1 to the DATEDIFF() result.
24
 
-------------------------------------------------------
Test #3: @StartDate = 2024-04-30, @EndDate = 2024-07-16
-------------------------------------------------------
The dates contain the different months. We are adding 1 to the DATEDIFF() result.
4
 
-------------------------------------------------------
Test #4: @StartDate = 2024-10-07, @EndDate = 2024-10-30
-------------------------------------------------------
The dates contain the same month and year. We are adding 1 to the DATEDIFF() result.
1
 
-------------------------------------------------------
Test #5: @StartDate = 2024-09-15, @EndDate = 2024-10-15
-------------------------------------------------------
The dates contain the different months. We are adding 1 to the DATEDIFF() result.
2

Os resultados correspondem aos meus requisitos, para os 5 conjuntos de intervalos de datas de teste.

Pergunta

Existe uma maneira mais confiável de fazer isso, além da minha CASEdeclaração desajeitada? Certamente não estou reinventando a roda aqui. Tenho certeza de que há mais casos extremos de intervalos de datas que ainda não pensei em testar. Estou fazendo isso da maneira mais difícil?

Como criar um índice composto exclusivo que não diferencia maiúsculas de minúsculas no MongoDB?

Corpo da pergunta: Estou tentando criar um índice de texto exclusivo composto no MongoDB com os seguintes campos:

tenant_id: 1 seller_id: 1 seller_sku_code: text. No entanto, estou encontrando um erro ao tentar criar o índice:

Falha na criação do índice: 4df81a22-042f-4e51-bbec-b0f8a7dafe2e:
Coleção product_db.skus ( 83cc8978-b0fe-4c4b-ae58-691e32db7f95 )
:: causado por :: E11000
coleção de erro de chave duplicada: índice product_db.skus: locatário_id_1_seller_id_1_ chave duplicada seller_sku_code_text: {locatário_id: "1", vendedor_id: "113", _fts: "acc", _ftsx: 0,75 }

Preciso garantir que o seller_sku_code seja exclusivo no banco de dados e não faça distinção entre maiúsculas e minúsculas. Por exemplo:

“ABC” e “abc” não devem ser permitidos simultaneamente. "ACC-2001" e "ACC-2000" deveriam poder coexistir.

Como posso criar esse índice exclusivo, que não diferencia maiúsculas de minúsculas, em seller_sku_code e, ao mesmo tempo, garantir a exclusividade dos campos tenant_id e seller_id?

O Oracle possui índices globais, o Postgres não. Até agora tudo bem.

A Oracle então tem índices locais prefixados e não prefixados e a Oracle faz essa diferença, mesmo na V19: Oracle Docs

Eu realmente não entendo por que a Oracle faz essa diferença e só posso explicar isso com 'programação preguiçosa' para mim mesmo, mas essa não é a questão aqui.

Minha pergunta é: é útil no postgres prefixar um índice por outros motivos além destes óbvios:

• Ter a chave de particionamento como prefixo no índice é útil para você, assim como a classificação no índice é útil para você
• Ter a chave de particionamento como prefixo no índice é útil para você, pois a chave de particionamento pode ter muitos valores diferentes