Web Developer, PHP, Drupal, Symfony.

select relname, seq_scan, seq_tup_read
from pg_stat_user_tables
order by seq_scan desc

Hoje aprendi um recurso muito interessante no PostgreSQL, criação de índices com função. Com este recurso é possível fazer consultas utilizando índices mesmo quando você estiver utilizando funções.

Vamos para um exemplo simples:

clientes: nome, sobrenome, email, cidade, etc..

Utilizando os filtros da tua aplicação você normalmente utilizaria o filtro abaixo para procurar um cadastro.

SELECT * FROM clientes 
WHERE nome ilike 'Nei' AND sobrenome ilike 'Santos'
ORDER BY nome desc LIMIT 20

Devido a utilização da função ILIKE, o postgres não irá utilizar os índices que temos na tabela nome e sobrenome.

Então como resolver essa consulta já que não sabemos como o cliente digitou o nome.

A solução é criar índices para campos com função:

Exercicio 1

Estrutura das tabelas:
Produto ( pnome: string, preco: integer, categoria: string, fabricante: string )
Compra ( comprador: string, vendedor: string, loja: string, produto: string )
Companhia ( cnome: string, valorAcao: integer, pais: string )
Pessoa ( nomePess: string, tel: string, cidade: string )

O PostgreSQL possui um módulo chamado fuzzystrmatch que está no pacote contrib, este módulo possui diversas funções para trabalhar com consultas aproximadas, muito útil quando queremos fazer buscas em palavras com erro de digitação.

Segundo Euler na pgcon 2008 (), esse tipo de consula possui um custo computacional alto..

Utilizando o pager do doctrine observei uma inconsistência nos resultados paginados, e ao pesquisar descobri o seguinte:

o paginator gerava o código:

SELECT ... ORDER BY weight DESC LIMIT 10 OFFSET 10

acontece que o mesmo registro que aparece na página 2 aparecia também na página 5, isso porque o Offset do postgres necessita obrigatóriamente de um order by único para o offset funcionar corretamente.

Meus registros estavam todos com o weight = 0, ou seja, não havia uma ordem única e fazia com que a paginação não funcionasse corretamente.