Artigo SQL Magazine 61 - Otimizações com índices reversos

Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Para efetuar o download você precisa estar logado. Clique aqui para efetuar o login
Confirmar voto
0
 (0)  (0)

Artigo publicado Revista SQL Magazine 61.

Esse artigo faz parte da revista SQL Magazine edição 61. Clique aqui para ler todos os artigos desta edição

imagem_pdf.jpg

PostgreSQL

Otimizações com índices reversos

Explorando o poliglotismo do PostgreSQL

 

Neste artigo serão exploradas diversas linguagens procedurais disponíveis no PostgreSQL tendo como objetivo a implementação de índices do tipo reverso. Será desenvolvida uma comparação de desempenho entre as linguagens para a melhor escolha num ambiente de produção. As linguagens tratadas no estudo são C, PL/Perl, PL/pgSQL e SQL. Elas podem ser usadas em ambientes Linux, Windows e até mesmo outras plataformas. A teoria computacional envolvida em índices reversos, bem como a motivação para sua utilização, é explorada no artigo. Ao final, serão realizados testes de desempenho indicando quais as melhores opções de linguagens quando precisamos realizar consultas, inserções e atualizações utilizando índices inversos em bases com grandes volumes de dados.

 

Introdução

Os índices reversos, extremamente úteis nos casos de busca por padrões no final do texto em bancos de dados relacionais (ver Nota 1), nem sempre consistem de soluções out-of-box. Desde sua versão 8i, o Oracle implementa índices reversos nativamente. No caso do SGBD PostgreSQL, precisamos implementá-lo manualmente. Este será o tema do estudo a seguir: uma proposição dos índices reversos no PostgreSQL equivalente ao existente no Oracle.

 

Nota 1. Índice Reverso

Um índice reverso, comparado a um índice convencional, inverte os bytes da chave a ser indexada. Ele é particularmente interessante no caso de uma coluna de tabela conter valores de texto que comumente apresentam um longo prefixo.

 

Por exemplo, em uma tabela destinada a armazenar Ordens de Serviço, a coluna chave pode possuir uma forma padronizada com 12 caracteres. O primeiro documento do ano 2008 é indicado com “OS-200800001”, o segundo com “OS-200800002”, e assim por diante. Até a tabela atingir alguns milhões de registros, os primeiros sete caracteres serão redundantes e não trarão muita contribuição às buscas indexadas. Um índice reverso armazenaria estes códigos na ordem inversa, isto é, como “100008002-SO”, “200008002-SO”, “300008002-SO”, e assim por diante. Com isso, ele permite que o último caractere da chave, mais altamente variável, espalhe os valores entre as estruturas de dados do índice. Tal procedimento aumenta consideravelmente o desempenho no momento da busca.

 

Os índices reversos permitem acelerar as buscas SQL do tipo “WHERE coluna LIKE '%texto'”. Neste caso, por padrão o SGBD realiza uma varredura seqüencial (também conhecida como “sequential scan” – ver Nota DevMan 1) na tabela em questão, o que compromete seriamente o desempenho de uma aplicação. Para resolver isso, podemos emular um índice reverso através de uma função de inversão de textos (ou strings) aliada a um índice sobre esta mesma função.

 

Nota DevMan 1. Varredura de Tabela Seqüencial – Sequential Table Scan

Uma varredura de tabela seqüencial, ou apenas varredura seqüencial, é um processo que lê todas as linhas em todas as páginas de uma tabela que estão armazenadas em um banco de dados.

 

Varreduras seqüenciais surgem no plano curto e longo como correlation_name<seq>, onde correlation_name é o nome da correlação especificado na cláusula FROM, ou então o nome da tabela caso nenhum nome tenha sido especificado.

 

Este tipo de varredura é usado quando o banco de dados “acredita” que a maioria das páginas da tabela possui uma linha que atinja às condições de busca definida na query ou então quando um índex adequado não está definido.

 

Apesar de a varredura seqüencial poder ler mais páginas que varredura por índice, a carga de I/O no disco pode ser substancialmente menor, pois as páginas são lidas a partir de blocos contínuos provenientes do disco (esta melhoria de desempenho só é melhor se o arquivo com o banco de dados não está fragmentado no disco). I/O seqüencial minimiza a sobrecarga devido à latência do movimento rotacional do cabeçote do disco. Para tabelas com grande volume de dados, varredura seqüencial também lê grupos de muitas páginas ao mesmo tempo. Isto reduz ainda mais o custo de varreduras seqüenciais em relação à varredura por índices.

 

Apesar de varreduras seqüenciais poderem levar menos tempo que varredura por índice que encontra muitas linhas, elas também podem explorar a cache de forma tão eficiente quanto a varredura por índice caso a varredura seja executada muitas vezes. Visto que varreduras por índice acessam provavelmente uma quantidade menor de páginas, é mais provável que as páginas estejam disponíveis em cache, o que resultará em um acesso mais rápido. Por causa disto, é muito melhor ter uma varredura por índice para acessos de tabelas que são repetidos, tais como direito de um JOIN recursivo já armazenado em cache.

 

No artigo “Garantindo desempenho com o operador LIKE”, publicado na edição 52 da SQL Magazine, foi proposta a função reverse() em linguagem PL/Perl com o objetivo de inverter uma cadeia de caracteres (string) especificada.

Tal implementação, apesar de completamente funcional, tinha o principal defeito de ser lenta, penalizando assim gravemente o desempenho durante as gravações (INSERT e UPDATE) na tabela. Além disso, a cada atualização é necessária uma chamada a essa função para que o índice funcional seja devidamente atualizado. Isso é observado particularmente durante a criação do índice.

Sempre soubemos que códigos binários escritos na linguagem C tendem a ser muito mais rápidos do que outras linguagens, especialmente se estas últimas forem interpretadas (como Perl ou Python) ou pseudo-interpretadas (como Java). Na seção seguinte, construiremos uma função em linguagem C para ser utilizada no PostgreSQL para podermos efetuar os estudos comparativos.

 

Implementando em linguagem C

Por que enfrentar o terrível e cruel mundo da linguagem C para escrever uma simples função procedural no PostgreSQL, quando podemos fazer a mesma coisa em uma linguagem bem mais simples e segura como a PL/pgSQL?

Existem diversas respostas para essa questão:

§         Uma função em linguagem C permite proteger o código-fonte. Lembre-se de que não existem, até a versão 8.3 do PostgreSQL, meios de se criptografar o código do corpo de uma função em PL/pgSQL.

§         Se houver necessidade de se criar de um tipo de dados próprio, a utilização de funções escritas em C é obrigatória.

§         A satisfação de se conhecer um pouco melhor o funcionamento interno do SGBD PostgreSQL e poder tirar proveito disso em outras ocasiões.

§         O problema da velocidade geralmente é o fator determinante para a reescrita de uma função codificada em linguagem procedural interpretada em linguagem compilada.

 

Não espere um ganho significativo de velocidade para as requisições SQL de seleção (SELECT) utilizando a nova função. Por outro lado, as operações de gravação (sobretudo INSERT e UPDATE) podem ser fortemente auxiliadas. Ainda assim, isso ainda não se tornará evidente para uma operação unitária, mas durante operações de escrita em massa, como carga massiva de dados, alterações de muitas linhas ao mesmo tempo ou pela simples criação do índice funcional.

Portanto, neste último caso a opção de se reescrever em linguagem C torna-se altamente aconselhável.

A extensibilidade do PostgreSQL apóia-se sobre seus mecanismos de carregamento dinâmico de bibliotecas do sistema operacional. Tratam-se de Dynamic Link Libraries (DLLs) no "

A exibição deste artigo foi interrompida :(
Este post está disponível para assinantes MVP

 
Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Receba nossas novidades
Ficou com alguma dúvida?