8bitIcon's questions

Eu tenho uma tabela PostgreSQL nomeada item_logcom uma logcoluna do tipo jsonbque contém vários atributos. Um desses atributos é delete_log. Estou consultando esta tabela com base no created_atcampo e na presença de non-nullvalores no delete_logatributo.

SELECT item_id
FROM item_log
WHERE created_at >= CURRENT_DATE - INTERVAL '15 days'
  AND (log->>'delete_log') IS NOT NULL;

Estou considerando diferentes estratégias de indexação para otimizar minhas consultas:

Aqui está o que eu tentei,

Índice criado emcreated_at : A consulta ficou mais rápida quando não coloquei (log->>'delete_log') IS NOT NULLna consulta a condição esperada. Assim que coloquei essa condição ou mesmo selecionei esse campo demorou em torno de 40 minutos.

Acho que a lentidão da consulta ocorre devido à inclusão do delete_log. Quando este campo é consultado, ele inicia a leitura da TOASTtabela e realiza deTOASToperações, o que aumenta significativamente o tempo de I/O.

Agora, para otimizar a consulta, tenho algumas opções:

• Adicionar índice composto em(created_at, log->>delete_log)
• Adicionar índice (log->>delete_log)porque já tenho índice ativado created_at?

Além disso, tenho outra confusão.

• Que tipo de índice devo escolher log->>delete_log?
• E como isso difere da indexação de todo o logcampo em termos de desempenho ou armazenamento para a consulta acima?

Preciso adicionar uma chave primária a uma tabela grande do PostgreSQL (aproximadamente 2 TB) com alto tráfego. Esta é uma operação crítica e estou procurando orientação sobre como fazê-la com eficiência.

Eu já tentei os passos abaixo:

-- Step 1: Add id identity column 
ALTER TABLE users
ADD COLUMN id BIGINT GENERATED ALWAYS as IDENTITY;

-- Step 2: Add unique index on (id, user_id) concurrently
CREATE UNIQUE INDEX CONCURRENTLY users_id_user_id_idx
   ON users (id, user_id);

-- verify that step 2 is completed
-- Step 3: Add primary key
ALTER TABLE users
   ADD CONSTRAINT users_pkey PRIMARY KEY USING INDEX users_id_user_id_idx;

Estou enfrentando dois problemas:

• A tabela está completamente bloqueada na própria "Etapa 1".

  Eu sei que isso é esperado, mas se houver alguma opção para evitar isso, sugira.

• Eu recebo o erro abaixo,

ERRO: não foi possível estender o arquivo "base/16401/90996": Não há espaço restante no dispositivo DICA: Verifique o espaço livre em disco.

Mas ainda tenho espaço 600GBde armazenamento no meu servidor.

Como a tabela ficará bloqueada na "Etapa 1", e se não houver opção para evitar isso, eu poderia aproveitar o tempo de inatividade e adicionar a idcoluna primeiro e depois executar os outros dois scripts.

Não sei se isso resolveria o erro de armazenamento.

Forneça sugestões para que eu possa adicionar o PK com o menor tempo de inatividade possível.

PostgreSQL v14.6

Eu tenho uma tabela PostgreSQL chamada orders, que particionei com base na user_idcoluna usando o particionamento LIST usando,

PARITION BY LIST lower(right(user_id, 2)) 

A tabela possui colunas customer_ide order_id, entre outros. Antes da partição, eu frequentemente consultava a tabela usando o seguinte padrão de consulta:

SELECT *
FROM orders
WHERE customer_id = 234234 AND order_id = 234234;

SELECT *
FROM orders
WHERE order_id = 234234;

Agora, devido a partição que estou usando conforme abaixo:

SELECT *
FROM orders
WHERE user_id = 234234 AND customer_id = 234234 AND order_id = 234234 AND lower(right(user_id, 2)) = '34';

Percebi que a remoção de partição não é totalmente utilizada porque devo incluir lower(right(user_id, 2)) = '34'na cláusula WHERE para acionar a remoção.

No entanto, não tenho uma necessidade específica de filtrar o user_id diretamente nesta consulta.

Minhas perguntas são:

1. Devo parar de passar user_ida cláusula WHERE, pois já particionei a tabela com base nela?

2. Seria benéfico criar um índice (user_id, customer_id, order_id)para otimizar o desempenho da consulta?

3. Como alternativa, devo criar um índice (lower(right(user_id, 2)), customer_id, order_id)e omitir a passagem de user_id na cláusula WHERE para melhor remoção?

4. Devo também ter um índice (order_id, lower(right(user_id,2)))para a segunda consulta? A seletividade de order_idserá maior que user_id.

Desejo garantir que a remoção de partição seja utilizada de maneira ideal, mantendo um bom desempenho de consulta. Qualquer conselho ou práticas recomendadas sobre indexação e particionamento nesse cenário serão muito apreciados.

Tenho uma tabela com três colunas: user_id, customer_id, and order_id.

Em minhas consultas, frequentemente filtro os dados usando condições como,

  ... WHERE user_id = 23434 AND customer_id = 234234 AND order_id IN [23334, 23423, 23452];

Desejo otimizar o desempenho da consulta criando um índice composto nessas colunas, mas não tenho certeza em que ordem as colunas devem ser incluídas no índice.

Considerando que a seletividade das colunas estaria na ordem customer_id, ordre_ide user_id, qual seria a ordem ótima para criar o índice composto?

Eu poderia fazer (customer_id, order_id, user_id), mas aqui para filtrar registros com base em user_id, banco de dados tem que visitar cada nó folha no intervalo de passado order_id.

Mas, se eu fizer isso (customer_id, user_id, order_id), aqui ele irá verificar muitos índices correspondentes a user_ide fazer a filtragem e, em seguida, buscar as linhas de diskou shared_buffers. Eu não tenho certeza aqui. Se alguém puder fornecer mais informações sobre isso.

Devo priorizar as colunas com base em sua seletividade ou seguir uma abordagem diferente?

Estou mais inclinado a isso (customer_id, order_id, user_id), mas preciso de alguns esclarecimentos.

Encontrei um cenário em que a mesma consulta em um banco de dados PostgreSQL exibe diferentes seleções de índice e estratégias de junção entre os ambientes QA e Prod. Estou tentando entender as possíveis razões por trás desse comportamento.

Aqui estão os detalhes:

Ambiente de controle de qualidade:

• Conjunto de dados menor em comparação com o Prod
• A consulta usa junção de loop aninhada
• Consulta usa idx_user_id_id_customer_idíndice
• Item de lista

Ambiente de produção:

• Conjunto de dados maior em comparação com o controle de qualidade
• A consulta usa junção de mesclagem
• Consulta usa idx_customer_idíndice
• O tamanho do idx_user_id_customer_idíndice é de 118 GB, enquanto idx_customer_ido índice é de 85 GB

Ambos os ambientes têm o mesmo conjunto de índices. As principais diferenças estão no tamanho dos dados e nos planos de execução escolhidos pelo otimizador de consultas.

Registro de explicação do produto: https://explain.depesz.com/s/28la

Registro de explicação do controle de qualidade: https://explain.depesz.com/s/zM6e

1. Quais seriam as possíveis razões para a disparidade na seleção de índices e na estratégia de junção entre os dois ambientes?
2. Existem fatores específicos que influenciam o processo de tomada de decisão do otimizador?

Aqui está o que eu penso, por favor me corrija se eu estiver errado e adicione mais informações:

• Ele está usando junção de loop aninhado em vez de junção de mesclagem porque no controle de qualidade pode haver menos linhas para o mesmo registro em um lado.
• O tamanho do índice idx_user_id_customer_idé grande, por isso está sendo ignorado. Ou pode ser que a seletividade user_idseja baixa, por customer_idisso está escolhendocustomer_id

Entendo o armazenamento básico de tabela de chave primária no PostgreSQL, onde os dados são armazenados em páginas e uma estrutura de índice de árvore B é usada para recuperação eficiente.

No entanto, gostaria de entender o comportamento quando uma tabela não possui uma chave primária, mas possui um índice em uma coluna de chave não primária, como a namecoluna.

CREATE TABLE products (
  id SERIAL,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10, 2) NOT NULL
);

Supondo que a tabela "products" tenha 100 linhas, meu entendimento é que os dados serão armazenados em páginas e o índice da árvore B terá um nó raiz [1, 100].

Além disso, haverá nós intermediários com intervalos como [1, 49]e [50, 100], que terão nós filhos e assim por diante. No entanto, não estou certo sobre o que acontecerá com os nós folha do índice. Eles conterão dados de linha reais?

1. No caso de um índice na namecoluna sem uma chave primária, os nós folha do índice conterão os dados reais da linha ou ponteiros para as linhas?

2. Como o processo de recuperação funcionará se os nós folha contiverem os dados reais da linha? Ele executará uma varredura linear dos nós de folha para encontrar o valor de "nome" desejado?

3. Além disso, ter uma chave primária, se a tabela já estiver indexada em name, pode otimizar a consulta?

Eu apreciaria uma explicação detalhada do mecanismo de armazenamento e recuperação deste cenário, juntamente com quaisquer exemplos de código relevantes ou considerações adicionais.

Criei minha tabela conforme abaixo:

CREATE TABLE IF NOT EXISTS public.customers (
    user_id varchar(255) NOT NULL,
    item_name varchar(255) NOT NULL,
    score int4 NULL,
    is_marked bool NULL
) PARTITION BY list(lower(right(user_id, 2)));

Em seguida, criei partições user_idcomo combinações de arquivos 00, 01,....a0....zz.

Eu também tenho índice na user_idcoluna no pai, bem como nas partições filhas.

Quando eu uso uma consulta simples como abaixo, ele está verificando todas as partições.

EXPLAIN ANALYZE SELECT * FROM customers where user_id = '1304257761';

Saída:

"QUERY PLAN"
"Append  (cost=0.42..11205.43 rows=11539 width=876) (actual time=268.869..1013.606 rows=9365 loops=1)"
"  ->  Index Scan using customers_00_user_id_idx on customers_00  (cost=0.42..8.22 rows=6 width=701) (actual time=4.676..4.677 rows=0 loops=1)"
"        Index Cond: ((user_id)::text = '1304257761'::text)"
"  ->  Index Scan using customers_01_user_id_idx on customers_01  (cost=0.42..21.11 rows=18 width=684) (actual time=6.882..6.882 rows=0 loops=1)"
"        Index Cond: ((user_id)::text = '1304257761'::text)"
"  ->  Index Scan using customers_02_user_id_idx on customers_02  (cost=0.42..66.46 rows=64 width=513) (actual time=4.213..4.213 rows=0 loops=1)"
"        Index Cond: ((user_id)::text = '1304257761'::text)"
"  ->  Index Scan using customers_03_user_id_idx on customers_03  (cost=0.42..17.24 rows=18 width=631) (actual time=3.226..3.226 rows=0 loops=1)"
"        Index Cond: ((user_id)::text = '1304257761'::text)"
"  ->  Index Scan using customers_04_user_id_idx on customers_04  (cost=0.42..10.25 rows=8 width=697) (actual time=3.237..3.237 rows=0 loops=1)"
"        Index Cond: ((user_id)::text = '1304257761'::text)"
"  ->  Index Scan using customers_05_user_id_idx on customers_05  (cost=0.42..15.79 rows=13 width=696) (actual time=5.942..5.942 rows=0 loops=1)"
"        Index Cond: ((user_id)::text = '1304257761'::text)"
"  ->  Index Scan using customers_06_user_id_idx on customers_06  (cost=0.42..11.42 rows=9 width=700) (actual time=2.355..2.356 rows=0 loops=1)"
"        Index Cond: ((user_id)::text = '1304257761'::text)"
"  ->  Index Scan using customers_07_user_id_idx on customers_07  (cost=0.42..16.04 rows=13 width=708) (actual time=2.542..2.542 rows=0 loops=1)"
"        Index Cond: ((user_id)::text = '1304257761'::text)"
"  ->  Index Scan using customers_08_user_id_idx on customers_08  (cost=0.42..12.25 rows=10 width=698) (actual time=2.684..2.684 rows=0 loops=1)"
"        Index Cond: ((user_id)::text = '1304257761'::text)"
"  ->  Index Scan using customers_09_user_id_idx on customers_09  (cost=0.42..14.90 rows=12 width=705) (actual time=2.755..2.755 rows=0 loops=1)"
"        Index Cond: ((user_id)::text = '1304257761'::text)"
"  ->  Index Scan using customers_0a_user_id_idx on customers_0a  (cost=0.41..1.72 rows=1 width=505) (actual time=1.778..1.778 rows=0 loops=1)"
"        Index Cond: ((user_id)::text = '1304257761'::text)"
"  ->  Index Scan using customers_0b_user_id_idx on customers_0b  (cost=0.28..2.29 rows=1 width=486) (actual time=0.886..0.886 rows=0 loops=1)"
"        Index Cond: ((user_id)::text = '1304257761'::text)"
"  ->  Index Scan using customers_0c_user_id_idx on customers_0c  (cost=0.28..2.34 rows=1 width=631) (actual time=0.805..0.806 rows=0 loops=1)"
"        Index Cond: ((user_id)::text = '1304257761'::text)"
.....

Meu entendimento é que ele só deve digitalizar o customers_61conforme passadouser_id = '1304257761'

Tenho uma tabela que atualmente possui menos de 2 bilhões de registros, e sua chave primária é definida como tipo de dados "Serial", que pode lidar com até 2 bilhões de registros. No entanto, prevejo que o limite será atingido em breve.

Como não uso a chave primária em nenhum lugar da consulta e ela não tem nenhuma relação com nenhuma outra tabela, estou pensando em descartar a própria coluna de chave primária.

Minha preocupação é quanto tempo levará para remover a coluna da tabela e se isso causará algum problema de bloqueio ou inatividade para as outras consultas que acessam a tabela.

Você poderia explicar o processo de descartar uma coluna no PostgreSQL?