Eu uso compactação de backup em todos os lugares. Recentemente, descobri que meus backups estavam ficando um pouco grandes demais. Decidi consertar isso compactando os índices no banco de dados (normalmente PAGEcompactação). O banco de dados ficou muito menor. Mas, para minha surpresa, os backups completos não ficaram menores!
Esse é um comportamento esperado?
Estou tentando acessar o LocalDB 2012 no meu PC com Windows 7 SP1.
Instalei o LocalDB com sucesso e iniciei a instância usando a linha de comando, e ele está em execução no momento.
Entretanto, quando tento me conectar a esta instância do LocalDB usando o SQL Server Management Studio (SSMS), recebo o seguinte erro.
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (.Net SqlClient Data Provider)
Verifiquei as seguintes configurações e elas parecem estar funcionando corretamente, conforme mencionado no Link
Observe também que tentei conectar usando os seguintes nomes de instância, mas sem sucesso:
Tentei fazer login usando a autenticação do Windows.
(localdb)\v11.0
(localdb)\mssqllocaldb
Considerando tudo isso, qual poderia ser a causa do problema? Como posso resolver esse problema?
Eu estava pensando por que o sp_blitz está recomendando fazer backup de certificados TDE que não foram copiados nos últimos 30 dias? Se o certificado não foi alterado e foi copiado após a criação, há necessidade de fazer mais backups?
Devo fazer backups regulares dos certificados TDE mesmo que eles não tenham sido alterados desde o último backup?
Estou usando a imagem docker mais recente do PostgreSQL para criar um BD local (em uma máquina Apple M1 Pro - MacOS Sonoma 14.5). Crio uma tabela table0com uma única coluna col0dentro dela e a preencho com strings aleatórias de 2 a 16 caracteres. Crio um trigram-index em col0e vacuum (analyze). Passos exatos:
create table public.table0 (
col0 varchar(25)
);
select setseed(0.12343);
insert into table0 (col0)
select substring(md5(random()::text), 1, (2 + (random() * 14))::int)
from generate_series(1, 12345678);
create extension pg_trgm;
create index col0_gin_trgm_idx on table0 using gin (col0 gin_trgm_ops);
vacuum (analyze) table0;
Examino o plano de consulta para selecionar 200 linhas contendo a string abc:
explain analyze
select * from table0 where col0 like '%abc%' limit 200;
Saída, confirmando que o índice do trigrama não é, mas uma varredura sequencial é usada:
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..351.78 rows=200 width=10) (actual time=0.313..15.640 rows=200 loops=1)
-> Seq Scan on table0 (cost=0.00..216614.79 rows=123154 width=10) (actual time=0.312..15.620 rows=200 loops=1)
Filter: ((col0)::text ~~ '%abc%'::text)
Rows Removed by Filter: 115643
Planning Time: 4.401 ms
Execution Time: 15.841 ms
(6 rows)
Entretanto, se em vez de procurar por linhas contendo abceu procurar por linhas contendo bcd:
explain analyze
select * from table0 where col0 like '%bcd%' limit 200;
Então obtenho um plano de consulta diferente, que agora inclui uma varredura de índice:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=52.34..764.83 rows=200 width=10) (actual time=7.032..7.230 rows=200 loops=1)
-> Bitmap Heap Scan on table0 (cost=52.34..4394.94 rows=1219 width=10) (actual time=7.031..7.220 rows=200 loops=1)
Recheck Cond: ((col0)::text ~~ '%bcd%'::text)
Heap Blocks: exact=169
-> Bitmap Index Scan on col0_gin_trgm_idx (cost=0.00..52.04 rows=1219 width=0) (actual time=5.100..5.100 rows=21264 loops=1)
Index Cond: ((col0)::text ~~ '%bcd%'::text)
Planning Time: 0.521 ms
Execution Time: 7.366 ms
(8 rows)
Esta configuração pode não ser totalmente reproduzível na primeira tentativa, mesmo que setseed(0.12343);seja usada, já que analyze"coleta estatísticas com base em sua própria seleção aleatória de linhas" (veja o segundo parágrafo aqui ). Recriei a situação acima várias vezes e nunca precisei tentar as etapas de configuração mais de 4 vezes, então espero que seja facilmente reproduzível, mesmo que o código que forneço não seja totalmente determinístico. (Removi e reiniciei o contêiner docker entre as tentativas.)
Esta resposta dá uma explicação básica do porquê uma vez o scan sequencial e por que às vezes o scan de índice é usado. Ela também tem 2 sugestões sobre como desencorajar scans sequenciais: modificando random_page_coste STATISTICSvalores.
Eu defino random_page_costpara 1.1 (via ALTER DATABASE postgres SET random_page_cost = 1.1;). Eu também "aumento a quantidade de estatísticas coletadas" por analyze(via ALTER TABLE table0 ALTER COLUMN col0 SET STATISTICS 1000;). Depois de outro vacuum (analyze) table0;eu reexecuto:
explain analyze
select * from table0 where col0 like '%abc%' limit 200;
e dessa vez o col0_gin_trgm_idxíndice trigrama é de fato usado. Depois disso, recriei o cenário acima, e sem modificar random_page_costou STATISTICSexecutar novamente vacuum (analyze) table0;- isso também modificou o comportamento e causou uma troca de varredura sequencial para varredura de índice. Acredito que isso se deva à natureza não determinística das estatísticas coletadas por analyze.
Desta vez, em vez de poder disparar o uso do índice (o que posso fazer agora, principalmente graças à resposta mencionada ), gostaria de entender os detalhes sobre como a decisão é tomada entre a varredura sequencial e a varredura de índice . Idealmente, gostaria de poder prever se a consulta:
explain analyze
select * from table0 where col0 like '%xyz%' limit 200;
irá disparar uma varredura de índice ou uma varredura sequencial, sabendo xyz, e qualquer coisa relacionada a estatísticas ou configurações do banco de dados. Anteriormente, no contexto de uma pergunta semelhante, fui aconselhado a verificar SELECT name, setting FROM pg_settings WHERE name = ANY ( '{shared_buffers, effective_cache_size, random_page_cost, effective_io_concurrency, work_mem}'::text[]);. Ele retorna (antes de modificar as configurações padrão):
name | setting
--------------------------+---------
effective_cache_size | 524288
effective_io_concurrency | 1
random_page_cost | 4
shared_buffers | 16384
work_mem | 4096
(5 rows)
Acho que esses valores têm um efeito na decisão de varredura sequencial vs varredura de índice. Espero que exista uma função f com duas saídas possíveis: varredura sequencial ou varredura de índice . Imagino que f pegue xyz, random_page_cost, estatísticas coletadas por analyze, etc como entradas. Gostaria de entender a lista de entradas (ou seja, o que é etc?) e o que f faz com elas.
Como posso prever se um plano de consulta envolverá o uso de um índice?
Tenho uma tabela com aproximadamente 80 colunas. Várias delas são indexadas. Elas incluem age, shoesizee height- todas são inteiros.
Tenho então uma consulta que estou tentando otimizar:
select * from people where age > 18 and shoesize > 10 and height > 150
Para tentar acelerar a consulta, criei um novo índice combinado das três colunas em questão - age, shoesizee height. Chamei esse índicecombinedIndex
Se eu então executar:
explain select * from people where age > 18 and shoesize > 10 and height > 150
Então vejo esta saída:
id = 1
select_type = SIMPLE
table = people
type = ALL
possible_keys = age, shoesize, height, combinedIndex
key = NULL
key_len = NULL
ref = NULL
rows = 8947017
extra = using where
Então parece que nenhum índice está sendo usado ao executar minha consulta.
Eu esperava que meu combinedIndexfosse usado com certeza.
Estou usando o MariaDB 10.6.19
Onde foi que eu errei?
Conforme sugerido neste post, proponho aqui minha pergunta.
Tenho um MongoDB collections spots que tem documentos que representam a amizade entre dois usuários
{
"_id": "64a337de4538a04610900f0c",
"user1": "6468022102781d442b82afd7",
"user2": "648c7b6b75231cd99e43d7ab",
"areFriends": true
}
Meu objetivo é escrever uma agregação que, dados dois IDs de usuários, retorne o grau de separação deles. Para ser claro, algo assim:
Me deparei com o exemplo acima de $graphLookup e agora estou pensando em como implementar algo semelhante.
A diferença entre meu caso e o outro post é que meus documentos não têm uma relação unidirecional (de-para), mas bidirecional (usuário1, usuário2); portanto, não há garantia de que o usuário desejado seja armazenado como usuário1 ou usuário2.
Em outras seções do meu código, resolvo esse problema ordenando os IDs de entrada ou usando condições como esta:
$cond: {
if: { $eq: ['$user1', userId] },
then: '$user2',
else: '$user1',
},
No entanto, entendi que as condições não são suportadas conforme arquivadas (como connectFromField ) em $graphLookup
Alguém tem alguma pista sobre como enfrentar esse problema em particular? Ficaria muito agradecido. Obrigado.
EDIT esta é uma versão revisada do pipeline de agregação proposto por Ray
{ $match: { user1: id1 } },
{
$graphLookup: {
from: 'spots',
startWith: '$user1',
connectFromField: 'user2',
connectToField: 'user1',
as: 'friendshipsViewLookup',
depthField: 'degree',
maxDepth: 2,
},
},
{
$unwind: {
path: '$friendshipsViewLookup',
preserveNullAndEmptyArrays: true,
},
},
{ $match: { 'friendshipsViewLookup.user2': id2 } },
{
$project: {
user1: 1,
user2: '$friendshipsViewLookup.user2',
degree: {
$add: ['$friendshipsViewLookup.degree', 1],
},
},
},
{
$group: {
_id: { user1: '$user1', user2: '$user2' },
minDegree: { $min: '$degree' },
},
},
{
$project: {
user1: '$_id.user1',
user2: '$_id.user2',
degree: '$minDegree',
},
}
Estou trabalhando com PostgreSQL 17. Como exemplo, tenho uma tabela table0com uma coluna col0, com strings geradas aleatoriamente como valores indexados por GIN . Eu uso o seguinte setup.sqlpara criar tal tabela:
create table public.table0 (
col0 varchar(25)
);
select setseed(0.12345);
insert into table0 (col0)
select substring(md5(random()::text), 1, (2 + (random() * 14))::int)
from generate_series(1, 12345678);
create extension pg_trgm;
create index col0_gin_trgm_idx on table0 using gin (col0 gin_trgm_ops);
vacuum (full, analyze) table0;
Usar select setseed(0.12345);garante que a tabela criada seja a mesma em cada execução de setup.sql. Gostaria de observar o plano de execução para uma consulta de correspondência de string parcial simples. Para fazer isso, uso query.sql:
explain (analyze, buffers)
select * from table0 where col0 like '%abc%' limit 500;
Para minha surpresa, o plano de execução da consulta não é constante, e aparentemente depende do tempo entre a criação da tabela e a execução da consulta. Para demonstrar isso, eu crio o seguinte script bash:
#!/bin/bash
set -o errexit
set -o nounset
set -o pipefail
rm -f records.txt
for i in {1..9}; do
docker run \
--name postgres-db \
--env POSTGRES_DB=postgres \
--env POSTGRES_USER=postgres \
--env POSTGRES_PASSWORD=mysecretpassword \
--publish 5432:5432\
--detach postgres
sleep 2 # wait for docker container to start
PGPASSWORD=mysecretpassword psql \
--host=localhost \
--port=5432 \
--username=postgres \
--dbname=postgres \
--set=ON_ERROR_STOP=1 \
--file=setup.sql
sleep $((RANDOM % 10))
PGPASSWORD=mysecretpassword psql \
--host=localhost \
--port=5432 \
--username=postgres \
--dbname=postgres \
--set=ON_ERROR_STOP=1 \
--file=query.sql >> records.txt
docker rm --force postgres-db
done
Este script configura a tabela, espera uma quantidade aleatória de até 10 segundos e, em seguida, executa o acima query.sql. Ele faz isso várias vezes. Ele salva query.sqlas saídas em records.txt. Eu olhei records.txte descobri que às vezes a varredura sequencial, às vezes a varredura de índice é usada para executar a consulta . Uma cat records.txt | grep "\->"versão filtrada (via ) de records.txt:
-> Bitmap Heap Scan on table0 (cost=52.34..4394.93 rows=1219 width=10) (actual time=11.526..15.200 rows=500 loops=1)
-> Bitmap Index Scan on col0_gin_trgm_idx (cost=0.00..52.04 rows=1219 width=0) (actual time=9.559..9.559 rows=20852 loops=1)
-> Seq Scan on table0 (cost=0.00..216612.01 rows=122742 width=9) (actual time=0.068..17.788 rows=500 loops=1)
-> Bitmap Heap Scan on table0 (cost=52.34..4394.93 rows=1219 width=9) (actual time=5.963..8.939 rows=500 loops=1)
-> Bitmap Index Scan on col0_gin_trgm_idx (cost=0.00..52.04 rows=1219 width=0) (actual time=4.144..4.144 rows=20852 loops=1)
-> Bitmap Heap Scan on table0 (cost=52.32..4377.97 rows=1214 width=9) (actual time=7.447..11.406 rows=500 loops=1)
-> Bitmap Index Scan on col0_gin_trgm_idx (cost=0.00..52.01 rows=1214 width=0) (actual time=5.594..5.594 rows=20852 loops=1)
-> Bitmap Heap Scan on table0 (cost=52.33..4384.75 rows=1216 width=9) (actual time=6.660..11.991 rows=500 loops=1)
-> Bitmap Index Scan on col0_gin_trgm_idx (cost=0.00..52.02 rows=1216 width=0) (actual time=4.744..4.745 rows=20852 loops=1)
-> Bitmap Heap Scan on table0 (cost=52.34..4394.93 rows=1219 width=9) (actual time=9.153..13.563 rows=500 loops=1)
-> Bitmap Index Scan on col0_gin_trgm_idx (cost=0.00..52.04 rows=1219 width=0) (actual time=7.141..7.141 rows=20852 loops=1)
-> Bitmap Heap Scan on table0 (cost=52.31..4374.58 rows=1213 width=10) (actual time=10.078..13.199 rows=500 loops=1)
-> Bitmap Index Scan on col0_gin_trgm_idx (cost=0.00..52.01 rows=1213 width=0) (actual time=8.108..8.108 rows=20852 loops=1)
-> Bitmap Heap Scan on table0 (cost=52.33..4384.76 rows=1216 width=10) (actual time=7.322..12.073 rows=500 loops=1)
-> Bitmap Index Scan on col0_gin_trgm_idx (cost=0.00..52.02 rows=1216 width=0) (actual time=5.526..5.527 rows=20852 loops=1)
-> Seq Scan on table0 (cost=0.00..216610.51 rows=245232 width=9) (actual time=0.073..23.047 rows=500 loops=1)
O completo records.txté:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=52.34..1833.55 rows=500 width=10) (actual time=11.527..15.249 rows=500 loops=1)
Buffers: shared hit=4 read=435
-> Bitmap Heap Scan on table0 (cost=52.34..4394.93 rows=1219 width=10) (actual time=11.526..15.200 rows=500 loops=1)
Recheck Cond: ((col0)::text ~~ '%abc%'::text)
Heap Blocks: exact=428
Buffers: shared hit=4 read=435
-> Bitmap Index Scan on col0_gin_trgm_idx (cost=0.00..52.04 rows=1219 width=0) (actual time=9.559..9.559 rows=20852 loops=1)
Index Cond: ((col0)::text ~~ '%abc%'::text)
Buffers: shared hit=4 read=7
Planning:
Buffers: shared hit=51 read=13 dirtied=1
Planning Time: 4.666 ms
Execution Time: 15.657 ms
(13 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..882.39 rows=500 width=9) (actual time=0.068..17.831 rows=500 loops=1)
Buffers: shared read=1439
-> Seq Scan on table0 (cost=0.00..216612.01 rows=122742 width=9) (actual time=0.068..17.788 rows=500 loops=1)
Filter: ((col0)::text ~~ '%abc%'::text)
Rows Removed by Filter: 282649
Buffers: shared read=1439
Planning:
Buffers: shared hit=55 read=9 dirtied=1
Planning Time: 2.427 ms
Execution Time: 17.918 ms
(10 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=52.34..1833.55 rows=500 width=9) (actual time=5.965..8.988 rows=500 loops=1)
Buffers: shared hit=4 read=435
-> Bitmap Heap Scan on table0 (cost=52.34..4394.93 rows=1219 width=9) (actual time=5.963..8.939 rows=500 loops=1)
Recheck Cond: ((col0)::text ~~ '%abc%'::text)
Heap Blocks: exact=428
Buffers: shared hit=4 read=435
-> Bitmap Index Scan on col0_gin_trgm_idx (cost=0.00..52.04 rows=1219 width=0) (actual time=4.144..4.144 rows=20852 loops=1)
Index Cond: ((col0)::text ~~ '%abc%'::text)
Buffers: shared hit=4 read=7
Planning:
Buffers: shared hit=51 read=13 dirtied=1
Planning Time: 2.427 ms
Execution Time: 9.234 ms
(13 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=52.32..1833.89 rows=500 width=9) (actual time=7.448..11.454 rows=500 loops=1)
Buffers: shared hit=4 read=435
-> Bitmap Heap Scan on table0 (cost=52.32..4377.97 rows=1214 width=9) (actual time=7.447..11.406 rows=500 loops=1)
Recheck Cond: ((col0)::text ~~ '%abc%'::text)
Heap Blocks: exact=428
Buffers: shared hit=4 read=435
-> Bitmap Index Scan on col0_gin_trgm_idx (cost=0.00..52.01 rows=1214 width=0) (actual time=5.594..5.594 rows=20852 loops=1)
Index Cond: ((col0)::text ~~ '%abc%'::text)
Buffers: shared hit=4 read=7
Planning:
Buffers: shared hit=81 read=13 dirtied=1
Planning Time: 2.835 ms
Execution Time: 11.721 ms
(13 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=52.33..1833.75 rows=500 width=9) (actual time=6.662..12.059 rows=500 loops=1)
Buffers: shared hit=4 read=435
-> Bitmap Heap Scan on table0 (cost=52.33..4384.75 rows=1216 width=9) (actual time=6.660..11.991 rows=500 loops=1)
Recheck Cond: ((col0)::text ~~ '%abc%'::text)
Heap Blocks: exact=428
Buffers: shared hit=4 read=435
-> Bitmap Index Scan on col0_gin_trgm_idx (cost=0.00..52.02 rows=1216 width=0) (actual time=4.744..4.745 rows=20852 loops=1)
Index Cond: ((col0)::text ~~ '%abc%'::text)
Buffers: shared hit=4 read=7
Planning:
Buffers: shared hit=51 read=13 dirtied=1
Planning Time: 3.332 ms
Execution Time: 12.511 ms
(13 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=52.34..1833.55 rows=500 width=9) (actual time=9.154..13.621 rows=500 loops=1)
Buffers: shared hit=4 read=435
-> Bitmap Heap Scan on table0 (cost=52.34..4394.93 rows=1219 width=9) (actual time=9.153..13.563 rows=500 loops=1)
Recheck Cond: ((col0)::text ~~ '%abc%'::text)
Heap Blocks: exact=428
Buffers: shared hit=4 read=435
-> Bitmap Index Scan on col0_gin_trgm_idx (cost=0.00..52.04 rows=1219 width=0) (actual time=7.141..7.141 rows=20852 loops=1)
Index Cond: ((col0)::text ~~ '%abc%'::text)
Buffers: shared hit=4 read=7
Planning:
Buffers: shared hit=51 read=13 dirtied=1
Planning Time: 4.113 ms
Execution Time: 14.018 ms
(13 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=52.31..1833.95 rows=500 width=10) (actual time=10.079..13.249 rows=500 loops=1)
Buffers: shared hit=4 read=435
-> Bitmap Heap Scan on table0 (cost=52.31..4374.58 rows=1213 width=10) (actual time=10.078..13.199 rows=500 loops=1)
Recheck Cond: ((col0)::text ~~ '%abc%'::text)
Heap Blocks: exact=428
Buffers: shared hit=4 read=435
-> Bitmap Index Scan on col0_gin_trgm_idx (cost=0.00..52.01 rows=1213 width=0) (actual time=8.108..8.108 rows=20852 loops=1)
Index Cond: ((col0)::text ~~ '%abc%'::text)
Buffers: shared hit=4 read=7
Planning:
Buffers: shared hit=51 read=13 dirtied=1
Planning Time: 3.596 ms
Execution Time: 13.682 ms
(13 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=52.33..1833.75 rows=500 width=10) (actual time=7.323..12.126 rows=500 loops=1)
Buffers: shared hit=4 read=435
-> Bitmap Heap Scan on table0 (cost=52.33..4384.76 rows=1216 width=10) (actual time=7.322..12.073 rows=500 loops=1)
Recheck Cond: ((col0)::text ~~ '%abc%'::text)
Heap Blocks: exact=428
Buffers: shared hit=4 read=435
-> Bitmap Index Scan on col0_gin_trgm_idx (cost=0.00..52.02 rows=1216 width=0) (actual time=5.526..5.527 rows=20852 loops=1)
Index Cond: ((col0)::text ~~ '%abc%'::text)
Buffers: shared hit=4 read=7
Planning:
Buffers: shared hit=51 read=13 dirtied=1
Planning Time: 5.907 ms
Execution Time: 12.485 ms
(13 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..441.64 rows=500 width=9) (actual time=0.074..23.096 rows=500 loops=1)
Buffers: shared read=1439
-> Seq Scan on table0 (cost=0.00..216610.51 rows=245232 width=9) (actual time=0.073..23.047 rows=500 loops=1)
Filter: ((col0)::text ~~ '%abc%'::text)
Rows Removed by Filter: 282649
Buffers: shared read=1439
Planning:
Buffers: shared hit=51 read=13 dirtied=1
Planning Time: 2.087 ms
Execution Time: 23.218 ms
(10 rows)
Usei o Postgres 17 dockerizado para facilitar a reprodutibilidade.
Reiniciando o contêiner docker, executando setup.sqle então:
explain (analyze, buffers, settings)
select * from table0 where col0 like '%abc%' limit 500;
retorna:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=52.32..1833.89 rows=500 width=9) (actual time=9.476..17.923 rows=500 loops=1)
Buffers: shared hit=4 read=435
-> Bitmap Heap Scan on table0 (cost=52.32..4377.97 rows=1214 width=9) (actual time=9.475..17.858 rows=500 loops=1)
Recheck Cond: ((col0)::text ~~ '%abc%'::text)
Heap Blocks: exact=428
Buffers: shared hit=4 read=435
-> Bitmap Index Scan on col0_gin_trgm_idx (cost=0.00..52.01 rows=1214 width=0) (actual time=7.662..7.662 rows=20852 loops=1)
Index Cond: ((col0)::text ~~ '%abc%'::text)
Buffers: shared hit=4 read=7
Planning:
Buffers: shared hit=55 read=9 dirtied=1
Planning Time: 5.597 ms
Execution Time: 18.334 ms
(13 rows)
No entanto, não consigo entender por que às vezes é usada uma varredura sequencial e às vezes uma varredura de índice.
Por que um plano de consulta aparentemente depende do intervalo de tempo entre a consulta e a configuração da tabela?
Abaixo abordo algumas questões que surgiram nos comentários.
Há alguma outra atividade de gravação no BD?
Eu não iniciei nenhuma, e como é apenas um contêiner que eu executo na minha máquina local, a menos que haja algum processo automatizado do qual eu não esteja ciente, não há nenhuma atividade de gravação.
O quão ocupado está seu servidor de outra forma?
Não notei nada que não esteja funcionando bem. É um Apple M1 Pro, executando MacOS Sonoma 14.5.
Não consigo fazer com que o SQL Server utilize um índice espacial básico em um GEOGRAPHYobjeto, mesmo para as consultas mais simples.
Aqui está uma reprodução do dbfiddle.uk que demonstra como criar uma tabela simples com uma GEOGRAPHYcoluna chamada Coordinates, adicionar 1 linha com um polígono a essa tabela e, em seguida, criar um índice espacial na Coordinatescoluna.
A consulta parece que deveria ser SARGable, mas ainda recebo uma varredura de índice clusterizado, em vez de uma busca de índice não clusterizado no índice espacial:
Se eu tentar forçar a dica de índice, recebo o erro clássico:
Msg 8635, Nível 16, Estado 4, Linha 15
O processador de consulta não pôde produzir um plano de consulta para uma consulta com uma dica de índice espacial. Motivo: Índices espaciais não suportam o comparador fornecido no predicado. Tente remover as dicas de índice ou remover SET FORCEPLAN.
Portanto, não é que o SQL Server pense que a verificação do índice clusterizado seja mais eficiente, mas sim que ele não consegue sequer criar um plano de consulta para o índice espacial.
De acordo com a documentação da Microsoft sobre Índices Espaciais , a STDistance()função deve ser aplicável em um predicado para sargabilidade de índices espaciais:
Os índices espaciais suportam as seguintes formas de predicado:
- geografia1.STDistance(geografia2) <= número
Estou sendo burro?
Tenho uma tabela PostgreSQL 17 table0. Ela tem uma única coluna col0contendo strings aleatórias de 2 a 16 caracteres. Gostaria de executar consultas de correspondência parcial, portanto, criei um índice GIN comgin_trgm_ops . Para minha surpresa, descobri que posso selecionar até 1000 linhas contendo abcmuito mais rápido do que até 200 linhas contendo abc. Configuração reproduzível:
Inicie o DB usando o Docker :
docker run \
--name postgres-db \
-e POSTGRES_DB=postgres \
-e POSTGRES_USER=postgres \
-e POSTGRES_PASSWORD=mysecretpassword \
-p 5432:5432\
-d postgres
Eu executo consultas usando o DBeaver ou salvando-as em query.sqle então:
PGPASSWORD=mysecretpassword psql \
-h localhost \
-p 5432 \
-U postgres \
-d postgres \
-v ON_ERROR_STOP=1 \
-f query.sql
Montar a mesa:
create table public.table0 (
col0 varchar(25)
);
select setseed(0.12343);
insert into table0 (col0)
select substring(md5(random()::text), 1, (2 + (random() * 14))::int)
from generate_series(1, 12345678);
create extension pg_trgm;
create index col0_gin_trgm_idx on table0 using gin (col0 gin_trgm_ops);
vacuum (full, analyze) table0;
Verifique o plano de execução e o tempo de execução da seleção de 200 linhas contendo abc:
explain analyze
select * from table0 where col0 like '%abc%' limit 200;
Saída:
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..352.27 rows=200 width=9) (actual time=0.672..49.646 rows=200 loops=1)
-> Seq Scan on table0 (cost=0.00..216621.29 rows=122985 width=9) (actual time=0.671..49.599 rows=200 loops=1)
Filter: ((col0)::text ~~ '%abc%'::text)
Rows Removed by Filter: 114081
Planning Time: 4.540 ms
Execution Time: 50.960 ms
(6 rows)
Verifique o plano de execução e o tempo de execução da seleção de até 1000 linhas contendo abc:
explain analyze
select * from table0 where col0 like '%abc%' limit 1000;
Saída:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=848.36..1369.03 rows=1000 width=9) (actual time=17.373..26.987 rows=1000 loops=1)
-> Bitmap Heap Scan on table0 (cost=848.36..64883.21 rows=122985 width=9) (actual time=17.371..26.931 rows=1000 loops=1)
Recheck Cond: ((col0)::text ~~ '%abc%'::text)
Heap Blocks: exact=846
-> Bitmap Index Scan on col0_gin_trgm_idx (cost=0.00..817.62 rows=122985 width=0) (actual time=14.689..14.690 rows=21318 loops=1)
Index Cond: ((col0)::text ~~ '%abc%'::text)
Planning Time: 2.165 ms
Execution Time: 27.356 ms
(8 rows)
Como é visível, quando eu uso LIMIT 200, o mecanismo está fazendo um Seq Scan on table0, mas quando eu tenho LIMIT 1000, Bitmap Index Scan on col0_gin_trgm_idxé usado. Isso explica superficialmente por que a consulta usando LIMIT 200levou 4.540+50.960= 55.5 milissegundos, enquanto a LIMIT 1000consulta levou menos, 2.165+27.356=29.521 milissegundos.
Eu li (veja isto ou isto ) que idealmente eu não deveria tentar forçar o uso de índices em um ambiente de produção. Ingenuamente, parece que usar o índice para pesquisar por 200 linhas contendo abcseria mais rápido do que a varredura sequencial usada atualmente, assim como usar o índice para pesquisar por 1000 linhas é mais rápido do que pesquisar por 200 linhas usando varredura sequencial.
No meu cenário do mundo real ( instância do Aurora PostgreSQL em execução no AWS RDS ), essa diferença é limitante: quando preciso selecionar 25 linhas da minha tabela, é muito mais rápido selecionar 100 delas e então filtrar essas 100 por outros meios postgresql(ou posso simplesmente modificar o aplicativo para que selecionar 100 linhas em vez de 25 também seja aceitável).
Gostaria de saber se estou fazendo algo abaixo do ideal com a indexação ou se estou esquecendo de algo.
O que devo fazer para que a consulta com LIMIT 200seja pelo menos tão rápida quanto a consulta com LIMIT 1000?
Estou interessado principalmente em métodos aconselháveis para uso em um ambiente de produção. É seguro assumir que table0não precisará ser modificado para editar seu conteúdo, nunca.
Claramente tenho um equívoco, mas nenhuma quantidade de documentação de consultoria resolveu isso. Considere o seguinte argumento:
O desdém popular por montes sugere que o que foi dito acima está errado, mas onde está errado?