ThatDataGuy's questions

Eu tenho uma situação em que as linhas de entrada (src) precisam corresponder às linhas em uma tabela de destino (tgt). No entanto, cada linha de destino só pode ser correspondida uma vez para todas as linhas de origem de entrada. Exemplo de SQL a seguir:

drop table if exists src;
create table src(id int not null, join_key text not null);
insert into src(id, join_key) values
(1,'X'),
(2,'X'),
(3,'X'),
(4,'Y'),
(5,'Y')
;

drop table if exists tgt;
create table tgt(id int not null, join_key text not null);
insert into tgt(id, join_key) values
(10,'X'),
(20,'X'),
(40,'Y')
;

with a as (
    select s.id as src_id,
           s.join_key,
           t.id as tgt_id
    from src s
             left join tgt t on s.join_key = t.join_key
),b as (
    select *,
           row_number() over (partition by src_id order by tgt_id) as rw
    from a
)
     select src_id, tgt_id from b
 where rw = 1
;

Isto dá:

1,10
2,10
3,10
4,40
5,40

Mas os valores devem ser:

1,10
2,20
3,null
4,40
5,null

Como posso fazer isso?

Considere este trecho:

create table tbl_1(name text);
create table tbl_2(name text);

create function my_insert(_name text) returns text
    language sql
as $$
   insert into tbl_2(name)
   values (_name);
   select _name;
$$;

explain analyse
with x as (select name from tbl_1)
   ,y as (select my_insert(x.name) from x)
select 0
;

retorna

Result  (cost=12.72..12.72 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
  CTE x
    ->  Seq Scan on tbl_1  (cost=0.00..12.72 rows=1360 width=32) (never executed)
Planning Time: 0.053 ms 
Execution Time: 0.013 ms

Agora eu posso entender porque a cláusula xnão está sendo executada (e também yé claro), mas o plano não menciona nada sobre o que aconteceu com cláusula y.

Isso me leva a suspeitar que o analisador de consulta está emitindo uma consulta que não contém a cláusula y.

Como posso confirmar isso? Como posso ver o que o analisador está emitindo aqui? Como alternativa, como ver qual consulta o planejador avaliou?

Estou seguindo o exemplo de como usar 'criar estatísticas' no postgresql desta página .

CREATE TABLE tbl (
                     col1 int,
                     col2 int
);

INSERT INTO tbl SELECT i/10000, i/100000
FROM generate_series (1,10000000) s(i);

CREATE STATISTICS s1 (dependencies) on col1, col2 from tbl;
ANALYZE tbl;

SELECT stxname, stxkeys, stxdependencies
FROM pg_statistic_ext
WHERE stxname = 's1';  

retorna:

stxname,stxkeys,stxdependencies
s1,1 2,{"1 => 2": 1.000000}

No entanto, estou confuso com esta linha

Observando isso, podemos ver que o Postgres percebe que col1 determina totalmente col2 e, portanto, tem um coeficiente de 1 para capturar essa informação.

No exemplo há muitos registros em col2 que se repetem. Então, como col1 'determina totalmente' col2? O que isto significa?

Eu tenho um banco de dados com muitas tabelas de dados de referência pequenas e algumas tabelas de séries temporais grandes. As tabelas de dados de referência podem caber facilmente na memória. Eu tenho vários trabalhos de ETL que carregam dados, principalmente juntando dados de entrada à esquerda nas tabelas existentes e inserindo/atualizando/excluindo quando necessário.

Faz sentido chamar pg_prewarmtodas as tabelas de dados de referência após a conclusão de um trabalho ETL, para garantir que todos sejam carregados na memória na próxima vez que um carregamento ETL for acionado? Vou notar muita melhora?

Suponha que a memória seja suficientemente grande para não causar um problema com o despejo de buffer.

Imagine a tabela a seguir sobre donos de gatos.

drop table if exists  owners cascade;
create table owners(
    id bigint primary key generated always as identity ,
    name text not null
)
;

insert into owners(name)
select random()::text from generate_series(1,20000);
--insert 200,000 owners records

Quando excluo alguns dos registros de proprietários, é bem rápido:

delete  from owners
where id %10 = 0;

20.000 linhas afetadas em 85 ms

Agora eu adiciono uma tabela chamada 'cats' que se refere aos proprietários:

drop table if exists cats;
create table cats(
    id serial primary key ,
    name varchar(20000) not null,
    owner_id int not null references owners(id)
);

--insert 1bn cats records
insert into cats(name, owner_id)
select
       random()::text,
       owners.id
from generate_series(1,10), owners;

Vamos deletar alguns donos, mas primeiro temos que deletar os gatos que esses donos 'possuem':

--delete the records in cats so we don't get a foreign key constraint violation
delete  from cats
where owner_id %10 = 1;


---now we do the same delete on owners as we did before
delete  from owners
where id %10 = 1;

2000 linhas afetadas em 25 s 828 ms

Por que a segunda exclusão dos proprietários é ~5000 vezes mais lenta do que quando não tínhamos a tabela de gatos?

Já vi muitos blogs e vídeos falando sobre os 'blocos' do Postgres. São as mesmas 'páginas' em outras implementações de sistemas de gerenciamento de banco de dados?

Li com interesse sobre o otimizador de consultas genéticas do postgres. Isso está ativado por padrão? Existe alguma configuração para habilitar? Existem boas regras práticas de quando usá-lo versus quando agora?

Segue link do documento:

https://www.postgresql.org/docs/11/geqo.html

O debate MariaDB vs PostgreSQL continua a se espalhar na minha empresa :-( e é alimentado por este post de blog um tanto famoso . Encontrei algumas discussões interessantes sobre mitigações de amplificação de gravação aqui . Minha pergunta é, algo assim foi lançado como parte do PostgreSQL 10 ou 11? Eu pesquisaria a documentação, mas não tenho certeza de como se chamaria.

Eu gostaria de usar um CTE de modificação para inserir alguns valores. A instrução insert insere dados de uma instrução select. Eu uso a palavra-chave de retorno para retornar os valores inseridos (incluindo colunas de incremento automático). No entanto, também quero que o CTE retorne outras colunas. Como posso fazer isso?

Segue um exemplo:

drop table if exists customers;

CREATE TABLE customers (
 customer_id serial PRIMARY KEY,
 name VARCHAR UNIQUE,
 email VARCHAR NOT NULL,
 active bool NOT NULL DEFAULT TRUE
);


INSERT INTO customers (NAME, email)
VALUES
 ('IBM', '[email protected]'),
 (
 'Microsoft',
 '[email protected]'
 ),
 (
 'Intel',
 '[email protected]'
 );

drop table if exists customers2;

CREATE TABLE customers2 (
 customer_id serial PRIMARY KEY,
 name VARCHAR UNIQUE,
 email VARCHAR NOT NULL
);


with x as (
    INSERT INTO customers2 (NAME, email)
    select name, email from customers
    returning customer_id, name, email, active
    )
select * from x
;

Gostaria que a última instrução retornasse as colunas customer_id, name, email, active. Mas recebo um erro:

Error: ERROR: column "active" does not exist
  Position: 123
SQLState:  42703
ErrorCode: 0
Error occurred in:
with x as (
    INSERT INTO customers2 (NAME, email)
    select name, email from customers
    returning customer_id, name, email, active
    )
select * from x

Eu tenho um banco de dados PostgreSQL no qual tenho uma grande carga em massa em execução. Desejo que esta carga seja o mais rápida possível. Já estou usando o comando copy etc.

Eu tenho lido sobre timescaledb e como ele oferece melhor desempenho de inserção. No entanto, gostaria de saber se há alguma desvantagem em usar hipertabelas em vez de tabelas regulares, se eu me importar apenas com o desempenho da inserção?

É possível criar um ponto nomeado no histórico de transações do banco de dados, de modo que uma conexão subsequente possa recuperar dados nesse ponto nomeado no log?

Por exemplo, digamos que há um processo de carregamento que está gravando em várias tabelas com bastante frequência. Em algum momento, um processo de leitura cria um ponto nomeado no log, vamos chamá-lo de ponto A. O carregador faz mais algumas atualizações e agora estamos no ponto B. Em seguida, outro processo de leitura aparece e pode selecionar dados como ponto de A .

Isso é possível no SQL Server?

Infelizmente, as tabelas / loader não possuem uma estrutura bitemporal, então o leitor não pode fornecer uma data e hora como predicado nas instruções select.