Índice
As versões atuais de PostgreSQL (incluindo 8.0) sofrem de uma fraqueza do otimizador da "query" a respeito das tabelas "TOAST". As tabelas "TOAST" são um tipo "extension room" usado para armazenar (no sentido do tamanho dos dados) os valores que não cabem em páginas de dados normais(como textos longos, imagens ou geometrias complexas com lotes de vertices), vejam http://www.postgresql.org/docs/8.0/static/storage-toast.html, para mais informações.
O problema aparece se acontecer de ter uma tabela com geometrias de preferência grandes, mas não com muitas linhas (como uma tabela que contem os limites de todos os países europeus em resolução elevada). Então a própria tabela é pequena, mas usa lotes de espaço do "TOAST". Neste exemplo, a própria tabela teve aproximadamente 80 linhas e usa somente 3 páginas de dados, mas a tabela "TOAST" usou 8225 páginas.
Agora emita uma "query" onde você use o operador de geometria && procurar por salto de caixa aqueles só os poucos grupos daquelas linhas. Agora a "query" otimizada vê que a tabela tem somente 3 páginas e 80 linhas. Estima que uma varredura seqüencial em uma tabela tão pequena é muito mais rápida do que usando um índice. E assim decide-se ignorar o índice de GIST. Geralmente, este estimativa está correta. Mas em nosso caso, o operador && tem que buscar cada geometria do disco para comparar os saltos de caixa, assim lendo todas as páginas TOAST, também.
Veja se a permissão deste erro, use o comando "EXPLAIN ANALYZE" do postgresql. Para mais informação e os detalhes técnicos, você pode ler a linha na lista de envio de desempenho do postgres: http://archives.postgresql.org/pgsql-performance/2005-02/msg00030.php
Equipes de PostgreSQL estão tentando resolver esta edição fazendo o estimativa de "queries" TOAST-aware. Agora, estão aqui duas questões a respeito deste trabalho:
O primeiro a cerca deste trabalho deve forçar o operador da "query" a usar o índice. Emita "SET enable_seqscan TO off;" ao usuário antes de emitir a "query". Isto força basicamente o operador da "query" a evitar varreduras seqüenciais sempre que possível. Assim usa o índice de GIST como usual. Mas este "flag" tem que ser ajustado em cada conexão, e faz com que o operador da "query" faça estimações em outros casos, assim que você se "SET enable_seqscan TO on;" depois da "query".
O segundo a cerca deste trabalho deve fazer a varredura seqüencial tão rápida como o operador da "query" pensa. Isto pode ser conseguido criando uma coluna adicional "caches" ao bbox, e agrupar de encontro a este. Em nosso exemplo, os comandos estão como:
SELECT addGeometryColumn('myschema','mytable','bbox','4326','GEOMETRY','2');
|
Agora altere sua "query" para usar o operador do && de encontro ao bbox em vez do geom_column, como:
SELECT geom_column FROM mytable WHERE bbox && SetSrid('BOX3D(0 0,1 1)'::box3d,4326);
|
Naturalmente, se você mudar ou adicionar linhas na tabela, você tem que manter o bbox "in sync". A maneira mais transparente de fazer isto seria "triggers", mas você também pode modificar sua aplicação para manter a coluna do bbox atual ou rodar uma "query" do UPDATE acima depois de cada modificação.
Para as tabelas que são na maior parte "read-only", e onde um único índice é usado para a maioria das "queries", PostgreSQL oferece o comando "CLUSTER". Este comando requisita novamente fisicamente todas as linhas de dados na mesma ordem que os critérios do índice, rendendo duas vantagens do desempenho: porque a escala do índice faz a varredura, o número das buscas na tabela dos dados é reduzido drasticamente. Em segundo, se seu "set" de funcionamento concentrar-se a alguns intervalos pequenos nos índices, você tem "caching" mais eficiente porque as linhas de dados são espalhadas ao longo de poucas páginas dos dados. (procure ler a documentação do comando "CLUSTER" do manual de PostgreSQL.)
Entretanto, atualmente PostgreSQL não permite "clustering" em índices PostGIS GIST porque os índices de GIST simplesmente ignoram valores NULL, você obtem uma mensagem de erro como:
lwgeom=# CLUSTER my_geom_index ON my_table; |
Porque a mensagem HINT diz, uma pode trabalhar em torno desta deficiência adicionando um constraint "not null" à tabela:
lwgeom=# ALTER TABLE my_table ALTER COLUMN the_geom SET not null; |
Naturalmente, isto não trabalhará se você de fato precisar de valores NULL em sua coluna geométrica. Adicionalmente, você deve usar o método acima para adicionar o "constraint", usando um CHECK "constraint" como "ALTER TABLE blubb ADD CHECK (geometria não é null);" não trabalhará.
Às vezes,acontece de ter os dados 3D ou 4D em sua tabela, mas sempre acessa-a usando as funções asText() ou asBinary() de OpenGIS que só sai geometrias 2D. Fazem isto internamente chamando a função force_2d(), que introduz um "overhead" significante para geometrias grandes. Para evitar estas "overhead", pode ser praticado "pre-drop" naquelas dimensões adicionais uma vez e para sempre:
UPDATE mytable SET the_geom = force_2d(the_geom); |
Note que se adicionar sua coluna geometrica usando AddGeometryColumn() terá um "constraint" na dimensão da geometria. Para contorná-la necessitará deixar cair o "constraint". Recorde que para atualizar a entrada na tabela geometry_columns e recriar mais tarde o "constraint".
No caso de tabelas grandes, pode ser dividido este UPDATE em porções confinando o UPDATE a uma parte da tabela através da cláusula WHERE e sua chave primária ou uns outros critérios praticáveis, e rodar um simples "VACUUM;" entre seus UPDATEs. Isto reduz drasticamente a necessidade para o espaço em disco provisório. Adicionalmente, se misturar dimensões geométricas, restringindo o UPDATE por "WHERE dimension(the_geom)>2" pula reescrever da geometria que já estão no 2D.