O índice de cobertura não é apenas outro recurso que pode ser útil. Essa coisa é puramente prática. Sem eles, o Index Only Scan pode não dar uma vitória. Embora o índice de cobertura em diferentes situações seja eficaz de diferentes maneiras.
Não se trata realmente de cobrir índices: a rigor, os chamados índices inclusivos apareceram no Postgres. Mas, em ordem: um índice de cobertura é um índice que contém todos os valores de coluna exigidos pela consulta; no entanto, o acesso à própria tabela não é mais necessário. Quase. Você pode
ler sobre “quase” e outras nuances em um artigo de
Yegor Rogov , incluído em sua série de índices de 10 (!) Partes. E o
índice inclusivo é criado especificamente para pesquisar em consultas típicas: os valores dos campos que não podem ser pesquisados são adicionados ao índice de pesquisa, eles são necessários apenas para não consultar a tabela novamente. Esses índices são formados com a palavra-chave INCLUDE.
Anastasia Lubennikova (Postgres Professional) finalizou o método btree para que colunas adicionais pudessem ser incluídas no índice. Esse patch foi incluído no PostgreSQL 11. Mas os patches para os métodos de acesso do GiST / SP-GiST não tiveram tempo de amadurecer antes do lançamento desta versão. No dia 12, o GiST amadureceu.
Um desejo construtivo de ter índices inclusivos para o GiST surgiu há muito tempo: um patch de teste de Andrey Borodin foi
oferecido à comunidade em meados de abril de 2018. Ele fez todo o trabalho básico e muito difícil.
No início de agosto de 2019, Alexander Korotkov adicionou melhorias cosméticas e comprometeu o patch.
Para demonstração e algumas pesquisas, geraremos um conjunto de 3 milhões de retângulos. Ao mesmo tempo, algumas palavras sobre o tipo de caixa, pois nem todas as manipulações são intuitivas.
O tipo de caixa - ou seja, o retângulo - está no Postgres há muito tempo, é definido por 2 pontos (o ponto do tipo geométrico) - os vértices opostos do retângulo (ou seja, o retângulo não pode ser oblíquo, desarrumado ao lado). Lemos na
documentação : “os valores do tipo box são escritos em uma das seguintes formas:
( ( x1 , y1 ) , ( x2 , y2 ) ) ( x1 , y1 ) , ( x2 , y2 ) x1 , y1 , x2 , y2
Na prática, você deve escrever, digamos, assim:
SELECT box('1,2', '3,4'); box
Primeiro, o Postgres mostra o vértice superior direito e depois o inferior esquerdo. Se escrevermos assim,
SELECT box('5,2', '3,4'); box
garantiremos que o Postgres não tenha atingido os picos que eles deram a ele. Ele calculou o canto superior direito e o canto inferior esquerdo do canto superior esquerdo e do canto inferior direito. Essa é uma propriedade conveniente quando a localização dos vértices não é conhecida antecipadamente - no caso de geração aleatória, por exemplo. A notação '1,2', '3,4' é equivalente ao ponto (1,2), ponto (3,4). Este formulário às vezes é mais conveniente.
Para empresas: pesquise em 3 milhões de retângulos
CREATE TABLE boxes(id serial, thebox box, name text);
Geraremos 3 milhões de retângulos aleatórios. Queremos uma distribuição normal, mas, para não usar a extensão de função de
tabela , usamos a abordagem “ruim”: usamos random () - random (), que também fornece uma boa imagem (veja a fig.) Com retângulos, quanto maior, mais, mais próximos eles estão do centro. Seus centros de gravidade também são aleatórios. Tais distribuições são características de alguns tipos de dados reais da cidade. E aqueles que desejam se aprofundar nas leis da estatística ou atualizar memórias podem ler sobre a diferença de variáveis aleatórias, por exemplo,
aqui .
INSERT INTO boxes(thebox, name) SELECT box( point( random()-random(), random()-random() ), point( random()-random(), random()-random() ) ), 'box no.' || x FROM generate_series(1,3000000) AS g(x);
O tamanho da tabela que mostra
\dt+ é 242MB. Agora você pode iniciar a pesquisa.
Estamos procurando sem um índice:
EXPLAIN ANALYZE SELECT thebox, name FROM boxes WHERE thebox @> box('0.5, 0.4','0.3, 0.2'); QUERY PLAN
Vemos que há uma varredura sequencial Parallel Seq - sequencial (embora paralelizada).
Crie um índice regular e não inclusivo:
CREATE INDEX ON boxes USING gist(thebox);
O tamanho do índice
boxes_thebox_idx , que mostra
\di+ , 262MB. Em resposta à mesma solicitação, obtemos:
EXPLAIN ANALYZE SELECT thebox, name FROM boxes WHERE thebox @> box('0.5, 0.4','0.3, 0.2'); QUERY PLAN
O tempo de pesquisa foi reduzido em um fator de três e, em vez da verificação Seq paralela, eles receberam uma verificação de índice de bitmap. Não é paralelo, mas funciona mais rápido.
Agora mate o índice antigo e crie um abrangente:
CREATE INDEX ON boxes USING spgist(thebox) INCLUDE(name);
Índice de
boxes_thebox_name_idx mais gordo: 356MB. Vamos lá:
EXPLAIN ANALYZE SELECT thebox, name FROM boxes WHERE thebox @> box('0.5, 0.4','0.3, 0.2'); QUERY PLAN
A digitalização apenas de índice é usada, mas a imagem é triste: o tempo é quase duas vezes maior do que sem ela. Lemos o manual do criador dos índices, na
parte I :
‹Os índices do PostgreSQL não contêm informações que permitem avaliar a visibilidade das linhas. Portanto, o método de acesso retorna todas as versões de linhas que se enquadram na condição de pesquisa, independentemente de estarem visíveis para a transação atual ou não. No entanto, se o mecanismo de indexação tivesse que examinar a tabela todas as vezes para determinar a visibilidade, esse método de verificação não seria diferente da verificação de índice comum. O problema é resolvido pelo fato do PostgreSQL suportar o chamado mapa de visibilidade para tabelas, em que o processo a vácuo marca páginas nas quais os dados não foram alterados o tempo suficiente para que todas as transações o vejam, independentemente da hora de início e do nível de isolamento. Se o identificador da linha retornada pelo índice se referir a essa página, a visibilidade não poderá ser verificada. ››
Nós fazemos VACUUM. Repita:
EXPLAIN ANALYZE SELECT thebox, name FROM boxes WHERE thebox @> box('0.5, 0.4','0.3, 0.2'); QUERY PLAN
Uma questão completamente diferente! O dobro do ganho em comparação com o índice não inclusivo.
Seletividade e ganho
O desempenho de índices inclusivos é altamente dependente da seletividade das condições nas consultas. Para investigar um pouco essa dependência, resolveremos o problema inverso: geraremos um rótulo com um índice do tipo point e procuraremos quantos pontos cairão na caixa especificada. Espalhe os pontos uniformemente ao quadrado.
CREATE TABLE test_covergist(id serial, tochka point, name text);
INSERT INTO test_covergist(tochka, name) SELECT point(trunc(1000000*random()), trunc(1000000*random())), 'point no.' || gx FROM generate_series(1,3000000) AS g(x);
O tamanho da tabela é 211 MB.
CREATE INDEX on test_covergist USING gist(tochka);
Tamanho 213 MB.
Obviamente, levaremos todos os pontos disponíveis para um quadrado:
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','3000000,3000000') @> tochka; QUERY PLAN
Pedimos EXPLAIN para mostrar os buffers. Será útil. Agora, o tempo de execução da solicitação é superior a 2 segundos; pode ser visto que Buffers: leitura compartilhada = 54287. Em outra situação, pudemos ver uma mistura de leitura compartilhada e ocorrência compartilhada - ou seja, alguns buffers são lidos do disco (ou do cache do SO) e outros do cache do buffer. Sabemos o tamanho aproximado da tabela e dos índices, portanto, nos protegeremos configurando buffers compartilhados para que tudo se encaixe - reinicie o Postgres com a opção
-o "-c shared_buffers=1GB"
Agora:
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','3000000,3000000') @> tochka; QUERY PLAN
Ou seja, a leitura compartilhada se tornou um hit compartilhado e o tempo foi reduzido três vezes.
Outro detalhe importante em EXPLAIN: são devolvidos 3 milhões de pontos e a previsão do número de registros retornados é de 3.000. Spoiler: esse número não será alterado com nenhuma seletividade. O otimizador não sabe como avaliar a cardinalidade ao trabalhar com tipos de caixa ou ponto. E o plano não será alterado: para qualquer tamanho do retângulo, haverá uma verificação de índice de bitmap em test_covergist_tochka_idx.
Aqui estão mais duas medidas com o número de registros emitidos, diferindo por ordens de magnitude:
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','300000,300000') @> tochka; QUERY PLAN
Ele retorna 10 vezes menos registros (reais ... linhas = 269882), o tempo diminuiu cerca de 5 vezes.
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','30000,30000') @> tochka; QUERY PLAN
O conteúdo de um quadrado de 30K × 30K (2780) é contado em apenas 16 ms. E quando existem dezenas de registros, eles já são extraídos em frações de um ms e essas medidas não são muito confiáveis.
Por fim, meça o mesmo com o índice inclusivo:
CREATE INDEX on test_covergist USING gist(tochka) INCLUDE(name);
Tamanho 316 MB.
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','3000000,3000000') @> tochka; QUERY PLAN
O tempo é quase o mesmo que com um índice convencional, embora a varredura apenas de índice.
Mas:
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','300000,300000') @> tochka; QUERY PLAN
E foram 151 ms. E, consequentemente:
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','300000,300000') @> tochka; QUERY PLAN
Isso já é uma fração de ms para os mesmos registros de 2780 pontos.
Amortecedores como armas
Uma explicação pode ser encontrada e encontrada em uma espingarda que ainda não disparou, mas que estava pendurada na parede: o número de blocos lidos. No caso de um índice inclusivo, apenas blocos do próprio índice são lidos (Heap Fetches: 0). Em três casos, eram os números 40492, 3735 e 52. Mas, ao usar o índice regular, os blocos lidos consistem na soma dos bits lidos no índice Bitmap Heap Scan (54248 com 3 milhões de registros) e nos que precisavam ser lidos do heap (27223) , já que o campo de nome não pode ser extraído de um índice regular. 54248 + 27223 = 81471. O exclusivo era 40492. Para outros dois casos: 29534 + 2510 = 31044 e 2655 + 31 = 2686. No caso de um índice regular, mais blocos são lidos de qualquer maneira, mas com uma melhoria na seletividade, o número de blocos lidos começa a diferir por ordens de magnitude em vez de 2 vezes devido ao fato de que o número de blocos necessários de um heap diminui mais lentamente do que a leitura de blocos de índice.
Mas talvez o ponto não seja a seletividade, mas simplesmente o tamanho da tabela? Por precaução, repetimos as mesmas etapas, gerando uma tabela com 300 mil e não 3 milhões de registros:
CREATE TABLE test_covergist_small(id serial, tochka point, name text); INSERT INTO test_covergist_small(tochka, name) SELECT point(trunc(1000000*random()), trunc(1000000*random())), 'point no.' || gx FROM generate_series(1,300000) AS g(x); CREATE INDEX ON test_covergist_small USING gist(tochka); EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist_small WHERE box('0,0','3000000,3000000') @> tochka; QUERY PLAN
Em seguida, repita o mesmo para o índice inclusivo. Aqui estão os resultados:
No caso de 100% de cobertura de pontos, a consulta foi um pouco mais lenta que no índice usual. Além disso, como no caso de 3 milhões, tudo se encaixou. Ou seja, a seletividade é importante.
Nossa empresa testou índices inclusivos de GiST em dados reais - um conjunto com vários milhões de retângulos no mapa de Moscou. A conclusão é a mesma: em muitas situações, esses índices aceleram sensivelmente as consultas. Mas o artigo não pode ser ilustrado com fotos e número de testes: esses dados não são de domínio público.
Em vez de uma conclusão
Vamos voltar por um momento para retângulos aleatórios. Vamos tentar fazer o mesmo com o spgist. Você pode se lembrar ou descobrir o que é entender as diferenças entre o SP-GiST e o GiST lendo o artigo
Indexes in PostgreSQL - 6 . Crie um índice inclusivo:
CREATE INDEX ON boxes USING spgist(thebox) INCLUDE(name); ERROR: access method "spgist" does not support included columns
Infelizmente, para o SP-GiST, índices inclusivos ainda não foram implementados.
Portanto, há espaço para melhorias!