Como exemplo, vamos pegar o PostgreSQL e dizer que temos duas tabelas com relacionamento um para muitos. Fazer o INNER JOIN produz "duplicatas" da primeira tabela. Essas linhas duplicadas estão realmente retornando como cópias dos mesmos dados do banco de dados no nível da conexão do banco de dados ou são apenas uma representação dos dados no cliente para caber esses dados em uma tabela?
Bom dia!
tbl
| eu ia | nome | resultado |
|---|---|---|
| 1 | v | 10 |
| 2 | c | 11 |
| 3 | x | 9 |
| 4 | sim | 7 |
| 5 | z | 15 |
tbl2
| eu ia | nome | resultado | diferença de resultado |
|---|---|---|---|
| 1 | v | 10 | 0 |
| 2 | c | 11 | +1 |
| 3 | x | 9 | -1 |
| 4 | sim | 7 | -3 |
| 5 | z | 15 | +5 |
coluna result-diffsão baseados na diferença entre a idlinha menos a partir do topo id.
Eu realmente não tenho ideia de como fazer isso. Agradeço antecipadamente
Eu tenho várias tabelas que normalmente possuem colunas de data e hora "CreatedDate" e/ou "ModifiedDate" anuláveis. Eu gostaria de uma consulta que pudesse selecionar qualquer tabela única onde existisse uma, ambas ou nenhuma coluna de data e hora e cada coluna recebesse BETWEENintervalos (separados).
Aqui está um exemplo aproximado (não funcional). Isso tenta usar COL_LENGTHpara determinar se existe uma coluna. Porém, se uma coluna não estiver presente, será gerado um erro, acredito porque o todo WHEREainda é avaliado.
SELECT * FROM tableName
WHERE 1=1 AND
(COL_LENGTH(tableName, 'CreatedDate') IS NULL OR CreatedDate BETWEEN @CreatedFrom AND @CreatedTo) AND
(COL_LENGTH(tableName, 'ModifiedDate') IS NULL OR ModifiedDate BETWEEN @ModifiedFrom AND @ModifiedTo)
Isso pode ser resolvido com SQL dinâmico. No entanto, neste caso estou restrito a permissões focadas na leitura. Portanto, não EXEC, CREATE ou INSERT.
Isso é possível com SQL estático?
Tabelas de exemplo
Com ambas as colunas:
DECLARE @Table1 table
(
Id int NOT NULL,
CreatedDate datetime NULL,
ModifiedDate datetime NULL
);
INSERT @Table1
(Id, CreatedDate, ModifiedDate)
VALUES
(1, NULL, NULL),
(2, NULL, '20230101 01:23:45.678'),
(3, '20230101 02:34:56.789', NULL),
(4, '20230101 03:45:00.891', '20230101 04:56:00.789');
DECLARE
@CreatedFrom datetime = {TS '2023-01-01 01:30:00.000'},
@CreatedTo datetime = {TS '2023-12-31 23:30:00.000'},
@ModifiedFrom datetime = {TS '2023-01-01 06:00:00.000'},
@ModifiedTo datetime = {TS '2023-12-31 18:00:00.000'};
Com uma coluna:
DECLARE @Table2 table
(
Id int NOT NULL,
CreatedDate datetime NULL
);
INSERT @Table2
(Id, CreatedDate)
VALUES
(1, NULL),
(2, '20230101 02:34:56.789');
DECLARE
@CreatedFrom datetime = {TS '2023-01-01 01:30:00.000'},
@CreatedTo datetime = {TS '2023-12-31 23:30:00.000'};
Obrigado @PaulWhite pelo exemplo inicial .
Como listar parâmetros não padrão no MySQL?
Estou procurando algo como \dconfig+depostgresql
Eu tentei fazer CREATE EXTENSION citus;. No entanto, recebi o seguinte erro:
ERROR: Citus can only be loaded via shared_preload_libraries
HINT: Add citus to shared_preload_libraries configuration variable in postgresql.conf in master and workers. Note that citus should be at the beginning of shared_preload_libraries.
No entanto, não tenho acesso root. Como posso criar a extensão neste caso?
Estou usando o código-fonte do PSQL para configurar o banco de dados. Existe um arquivo específico aqui que eu possa modificar para permitir a extensão?
Aqui está a lista de arquivos na minha postgresql-15.3pasta de código-fonte:
COPYRIGHT GNUmakefile.in INSTALL README config config.status configure.ac doc
GNUmakefile HISTORY Makefile aclocal.m4 config.log configure contrib src
Recentemente, descobri que a instância de banco de dados do meu aplicativo de produção atinge cerca de 100% da CPU e, quando observo as consultas travadas, vejo muitas consultas travadas que envolvem uma tabela que tem uma coluna contendo uma lista de ancestrais, e basicamente fazendo uma substring de expressão regular para encontrar o ID pai dessa coluna e, em seguida, executando um NOT INpara excluir linhas que são pais ...
SELECT fp.*, admin_folders.id AS folder_id FROM folder_permissions fp RIGHT OUTER JOIN (
SELECT f.* FROM folders f WHERE f.deleted = FALSE AND ((SUBSTRING(f.ancestry FROM '([^/]*)$')::integer NOT IN (SELECT f2.id FROM folders f2 WHERE deleted = FALSE)) OR (f.ancestry IS NULL))
) admin_folders
ON admin_folders.id = fp.folder_id
AND fp.user_id = 12345 ORDER BY lower(admin_folders.name);
Inicialmente pensei que talvez a substring com o regex estivesse causando o problema, então tentei adicionar uma coluna ltree e alterá-la para ((ltree2text(subpath(f.path, -2, 1))::integer... Isso pareceu torná-lo um pouco mais rápido em ambientes inferiores, mas novamente o banco de dados de produção trava indefinidamente quando esta consulta é feito.
O que é interessante para mim é que, nos ambientes inferiores, se eu fizer essa consulta com um ID de usuário que tenha mais de 100.000 registros de permissão de pasta, ela será instantânea.
----------------------------------------------------------------------------------------------------------------------------------
Sort (cost=24.49..24.50 rows=5 width=73) (actual time=4.286..4.289 rows=8 loops=1)
Sort Key: (lower((f.name)::text))
Sort Method: quicksort Memory: 25kB
-> Nested Loop Left Join (cost=10.22..24.43 rows=5 width=73) (actual time=3.810..3.997 rows=8 loops=1)
Join Filter: (f.id = fp.folder_id)
-> Seq Scan on folders f (cost=10.22..20.62 rows=5 width=520) (actual time=3.448..3.624 rows=8 loops=1)
Filter: ((NOT deleted) AND ((NOT (hashed SubPlan 1)) OR (ancestry IS NULL)))
Rows Removed by Filter: 14
SubPlan 1
-> Seq Scan on folders f2 (cost=0.00..10.20 rows=10 width=4) (actual time=0.008..0.018 rows=22 loops=1)
Filter: (NOT deleted)
-> Materialize (cost=0.00..3.72 rows=1 width=37) (actual time=0.015..0.015 rows=0 loops=8)
-> Seq Scan on folder_permissions fp (cost=0.00..3.71 rows=1 width=37) (actual time=0.120..0.120 rows=0 loops=1)
Filter: (user_id = 12345)
Rows Removed by Filter: 175
Planning Time: 2.346 ms
Execution Time: 5.266 ms
(17 rows)
No ambiente de produção, tentando com um usuário que tem apenas 8.000 registros de permissão de pasta, ele trava indefinidamente... Deixei o explicar analisar rodando por mais de 8 horas e nunca vejo a saída...
Os ambientes inferiores e superiores têm os mesmos índices e tentei reconstruí-los.
*** ATUALIZAR ***
Conforme solicitado, aqui está a explicação da máquina de pendurar:
=> explain SELECT fp.*, admin_folders.id AS folder_id FROM folder_permissions fp RIGHT OUTER JOIN (SELECT f.* FROM folders f WHERE f.deleted = FALSE AND ((SUBSTRING(f.ancestry FROM '([^/]*)$')::integer NOT IN (SELECT f2.id FROM folders f2 WHERE deleted = FALSE)) OR (f.ancestry IS NULL))) admin_folders ON admin_folders.id = fp.folder_id AND fp.user_id = 12345 ORDER BY lower(admin_folders.name);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Gather Merge (cost=332160509.79..332167328.27 rows=58440 width=73)
Workers Planned: 2
-> Sort (cost=332159509.77..332159582.82 rows=29220 width=73)
Sort Key: (lower((f.name)::text))
-> Merge Left Join (cost=17496.11..332157342.42 rows=29220 width=73)
Merge Cond: (f.id = fp.folder_id)
-> Parallel Index Scan using index_folder_id_on_undeleted_v2 on folders f (cost=0.42..332139670.56 rows=29220 width=20)
Filter: ((NOT (SubPlan 1)) OR (ancestry IS NULL))
SubPlan 1
-> Materialize (cost=0.00..11018.00 rows=140223 width=4)
-> Seq Scan on folders f2 (cost=0.00..9768.88 rows=140223 width=4)
Filter: (NOT deleted)
-> Sort (cost=17495.69..17507.71 rows=4807 width=37)
Sort Key: fp.folder_id
-> Bitmap Heap Scan on folder_permissions fp (cost=93.82..17201.72 rows=4807 width=37)
Recheck Cond: (user_id = 12345)
-> Bitmap Index Scan on index_folder_permissions_on_user_id (cost=0.00..92.62 rows=4807 width=0)
Index Cond: (user_id = 12345)
(18 rows)
E explique a análise na consulta SEM a parte NOT IN:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=410.76..410.84 rows=33 width=73) (actual time=12.446..12.483 rows=43 loops=1)
Sort Key: (lower((f.name)::text))
Sort Method: quicksort Memory: 28kB
-> Nested Loop Left Join (cost=5.11..409.92 rows=33 width=73) (actual time=0.417..12.314 rows=43 loops=1)
-> Bitmap Heap Scan on folders f (cost=4.68..130.41 rows=33 width=20) (actual time=0.392..1.518 rows=43 loops=1)
Recheck Cond: ((ancestry IS NULL) AND (NOT deleted))
Heap Blocks: exact=43
-> Bitmap Index Scan on index_folders_on_ancestry_and_not_deleted (cost=0.00..4.67 rows=33 width=0) (actual time=0.220..0.221 rows=1620 loops=1)
Index Cond: (ancestry IS NULL)
-> Index Scan using index_folder_permissions_on_folder_id_and_user_id on folder_permissions fp (cost=0.44..8.46 rows=1 width=37) (actual time=0.246..0.246 rows=0 loops=43)
Index Cond: ((folder_id = f.id) AND (user_id = 12345))
Planning Time: 0.464 ms
Execution Time: 12.581 ms
(13 rows)
E a mesma coisa na máquina não suspensa:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=556.42..556.53 rows=45 width=73) (actual time=0.685..0.726 rows=49 loops=1)
Sort Key: (lower((f.name)::text))
Sort Method: quicksort Memory: 29kB
-> Nested Loop Left Join (cost=5.20..555.19 rows=45 width=73) (actual time=0.045..0.589 rows=49 loops=1)
-> Bitmap Heap Scan on folders f (cost=4.77..174.15 rows=45 width=17) (actual time=0.025..0.178 rows=49 loops=1)
Recheck Cond: ((ancestry IS NULL) AND (NOT deleted))
Heap Blocks: exact=45
-> Bitmap Index Scan on index_folders_on_ancestry_and_not_deleted (cost=0.00..4.76 rows=45 width=0) (actual time=0.014..0.015 rows=51 loops=1)
Index Cond: (ancestry IS NULL)
-> Index Scan using index_folder_permissions_on_folder_id_and_user_id on folder_permissions fp (cost=0.43..8.46 rows=1 width=37) (actual time=0.004..0.005 rows=0 loops=49)
Index Cond: ((folder_id = f.id) AND (user_id = 12345))
Planning Time: 0.404 ms
Execution Time: 0.845 ms
(13 rows)
e por último, explique a parte da consulta que causa o travamento:
compass=> explain SELECT f.* FROM folders f WHERE f.deleted = FALSE AND ((SUBSTRING(f.ancestry FROM '([^/]*)$')::integer NOT IN (SELECT f2.id FROM folders f2 WHERE deleted = FALSE)) OR (f.ancestry IS NULL));
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Gather (cost=3547.07..332130118.64 rows=70128 width=486)
Workers Planned: 2
-> Parallel Bitmap Heap Scan on folders f (cost=2547.07..332122105.84 rows=29220 width=486)
Recheck Cond: (NOT deleted)
Filter: ((NOT (SubPlan 1)) OR (ancestry IS NULL))
-> Bitmap Index Scan on index_folder_id_on_undeleted_v2 (cost=0.00..2529.53 rows=140223 width=0)
SubPlan 1
-> Materialize (cost=0.00..11018.00 rows=140223 width=4)
-> Seq Scan on folders f2 (cost=0.00..9768.88 rows=140223 width=4)
Filter: (NOT deleted)
(10 rows)
Vejo algum comportamento estranho ao usar índices gin_trgm_opsou gist_trgm_ops. Parece haver uma grande diferença nos planos ao usar say ILIKEou ~e pesquisar frases ascii versus frases char multibyte. Como se houvesse um custo maior quando o operando é um operando não ASCII.
O que estou vendo é esperado? Qual é a razão para isso?
Eu tentei no Postgreql 12 e 13 mais recente de cada.
Aqui está um cenário:
CREATE DATABASE postgres
WITH
OWNER = postgres
ENCODING = 'UTF8'
LC_COLLATE = 'en_US.utf8'
LC_CTYPE = 'en_US.utf8'
TABLESPACE = pg_default
CONNECTION LIMIT = -1
IS_TEMPLATE = False;
-- snip
CREATE TABLE test_table (
id uuid PRIMARY KEY,
label varchar
);
-- insert 1m rows
VACUUM ANALYZE test_table;
No conjunto de dados, tenho 10 rótulos contendo 'acl'e 10 contendo '定す'.
Ao usar o índice GIN
CREATE INDEX test_table_label_gin_idx
ON test_table USING gin
(label gin_trgm_ops);
Eu vejo o seguinte.
EXPLAIN ANALYZE SELECT * FROM test_table WHERE label ILIKE '%定す%' LIMIT 100;
Limit (cost=1000.00..16573.18 rows=100 width=52) (actual time=392.153..395.095 rows=10 loops=1)
-> Gather (cost=1000.00..16728.91 rows=101 width=52) (actual time=392.135..394.830 rows=10 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on test_table (cost=0.00..15718.81 rows=42 width=52) (actual time=382.922..388.082 rows=3 loops=3)
Filter: ((label)::text ~~* '%定す%'::text)
Rows Removed by Filter: 338417
Planning Time: 0.656 ms
Execution Time: 395.233 ms
EXPLAIN ANALYZE SELECT * FROM test_table WHERE label ILIKE '%acl%' LIMIT 100;
Limit (cost=28.78..400.51 rows=100 width=52) (actual time=0.072..0.406 rows=10 loops=1)
-> Bitmap Heap Scan on test_table (cost=28.78..404.23 rows=101 width=52) (actual time=0.053..0.197 rows=10 loops=1)
Recheck Cond: ((label)::text ~~* '%acl%'::text)
Heap Blocks: exact=10
-> Bitmap Index Scan on test_table_label_gin_idx (cost=0.00..28.76 rows=101 width=0) (actual time=0.025..0.034 rows=10 loops=1)
Index Cond: ((label)::text ~~* '%acl%'::text)
Planning Time: 0.231 ms
Execution Time: 0.542 ms
Com GIST
DROP INDEX test_table_label_gin_idx;
CREATE INDEX test_table_label_gist_idx
ON test_table USING gist
(label gist_trgm_ops);
Eu vejo
EXPLAIN ANALYZE SELECT * FROM test_table WHERE label ILIKE '%定す%' LIMIT 100;
Limit (cost=13.19..384.92 rows=100 width=52) (actual time=303.772..1557.498 rows=10 loops=1)
-> Bitmap Heap Scan on test_table (cost=13.19..388.64 rows=101 width=52) (actual time=303.752..1557.286 rows=10 loops=1)
Recheck Cond: ((label)::text ~~* '%定す%'::text)
Rows Removed by Index Recheck: 1015250
Heap Blocks: exact=10431
-> Bitmap Index Scan on test_table_label_gist_idx (cost=0.00..13.17 rows=101 width=0) (actual time=301.046..301.053 rows=1015260 loops=1)
Index Cond: ((label)::text ~~* '%定す%'::text)
Planning Time: 0.215 ms
Execution Time: 1557.643 ms
EXPLAIN ANALYZE SELECT * FROM test_table WHERE label ILIKE '%acl%' LIMIT 100;
Limit (cost=13.19..384.92 rows=100 width=52) (actual time=257.385..257.751 rows=10 loops=1)
-> Bitmap Heap Scan on test_table (cost=13.19..388.64 rows=101 width=52) (actual time=257.366..257.551 rows=10 loops=1)
Recheck Cond: ((label)::text ~~* '%acl%'::text)
Heap Blocks: exact=10
-> Bitmap Index Scan on test_table_label_gist_idx (cost=0.00..13.17 rows=101 width=0) (actual time=257.319..257.328 rows=10 loops=1)
Index Cond: ((label)::text ~~* '%acl%'::text)
Planning Time: 0.377 ms
Execution Time: 257.948 ms
Apenas mudar os caracteres do operando muda bastante o plano.
Editar
SELECT show_trgm('定す');
"{0x145ed8,0x6628fa,0x6cb12d}"
SELECT encode('定す', 'escape')
\345\256\232\343\201\231
Este problema parece semelhante ao Postgresql não usar o índice trigrama GIN ao executar uma consulta LIKE não ASCII?
Como pode ser visto nesta captura de tela, estou executando a mesma consulta repetidamente e retornando com 0 linhas (conforme esperado). Por algum motivo, estou vendo tempos de espera muito grandes nas respostas do servidor ao executar isso no próprio servidor. Eu queria saber se alguém poderia me indicar a direção certa sobre por que isso pode estar ocorrendo.
Meu palpite é que é falta de recursos ou muitas outras coisas acontecendo, mas me pergunto como posso provar isso.
Servidor MSSql 2017 edição padrão, máquina de 64 núcleos, 240 GB de RAM
EDIT: saída da estatística de espera adicionada. 42 linhas.
Estive lendo a documentação do SQL Server e me deparei com a seguinte declaração sobre pontos de verificação automáticos:
Emitido automaticamente em segundo plano para atender ao limite de tempo superior sugerido pela opção de configuração do servidor de intervalo de recuperação. Os pontos de verificação automáticos são executados até a conclusão. Os pontos de verificação automáticos são limitados com base no número de gravações pendentes e se o Mecanismo de Banco de Dados detecta um aumento na latência de gravação acima de 50 milissegundos.
Estou tentando entender o que significa "Pontos de verificação automáticos executados até a conclusão". Não sou falante nativo de inglês e esta frase me parece muito estranha. Há algum ponto de verificação que não foi concluído e realiza apenas metade do seu trabalho?
Além disso, lembro-me de uma declaração de Paul Randal onde ele mencionou que o processo de checkpoint limitará IOs pendentes se a latência de IO for superior a 20 ms. Durante o desligamento, esse limite aumenta para 100 ms para agilizar o processo. No entanto, a documentação do SQL Server sugere um limite de 50 milissegundos para limitar os pontos de verificação automáticos. Houve uma alteração no valor limite ou estou entendendo mal o conceito?
Qualquer esclarecimento sobre estes pontos seria muito apreciado.
Agradeço antecipadamente!
Estou seguindo esta resposta para adicionar uma NOT NULLrestrição a uma tabela de banco de dados existente com aproximadamente 100 milhões de linhas. No entanto, quando tento executar o processo de back-end, um arquivo ShareLock.
ALTER TABLE mytable VALIDATE CONSTRAINT myfield_not_null;
Confirmei isso verificando pg_locksem outra sessão, onde posso ver o seguinte (5447 é o pid do backend tentando VALIDATE CONSTRAINT):
mydb=> select l.pid, l.mode, l.granted, l.waitstart, a.xact_start, a.query_start, a.state from pg_locks l join pg_stat_activity a on l.pid = a.pid;
pid | mode | granted | waitstart | xact_start | query_start | state
-------+--------------------------+---------+-------------------------------+-------------------------------+-------------------------------+--------
5447 | ShareLock | t | | 2023-10-06 14:58:23.133136+00 | 2023-10-06 14:58:23.355743+00 | active
5447 | ShareUpdateExclusiveLock | t | | 2023-10-06 14:58:23.133136+00 | 2023-10-06 14:58:23.355743+00 | active
Os documentos dizem explicitamente:
um comando VALIDATE CONSTRAINT pode ser emitido para verificar se as linhas existentes satisfazem a restrição. A etapa de validação não precisa bloquear atualizações simultâneas, pois sabe que outras transações imporão a restrição às linhas que inserem ou atualizam; apenas as linhas pré-existentes precisam ser verificadas. Portanto, a validação adquire apenas um bloqueio SHARE UPDATE EXCLUSIVE na tabela que está sendo alterada.
O ShareLock está bloqueando algumas consultas que precisam de RowExclusiveLock (atualizar/inserir/excluir).
Como posso validar a restrição sem usar um ShareLock?