Versões atuais do PostgreSQL (incluindo a 8.0) sofrem de um problem no otimizador de queries quando falamos de tabelas TOAST. As tabelas TOAST são extensões utilizadas para armazenamento de grandes valores (no sentido de tamanho do dado) que não cabem normalmente nas páginas de dados (grandes blocos de texto, imagens ou geometrias complexas com muitos vértices, veja a documentação oficial para maiores informações).
Este problema ocorre se você possui tabelas com geometrias grandes, mas não muitas linhas (uma tabela dos limites todos os países europeus em alta resolução). A tabela em si, é pequena, mas utiliza muito espaço TOAST. Em nosso exemplo, a tabela em si possuía apenas 80 linhas e utilizava apenas 3 páginas de dados, mas a tabela TOAST utilizava 8225 páginas de dados.
Emita uma pesquisa onde você utiliza o operador && para pesquisa por um retângulo envolvente que bate com poucas dessas linhas. O otimizador de pesquisas ve esta tabela contendo apenas 3 páginas e 80 linhas. Como a tabela é pequena, ele estima que um scan sequencial em uma tabela tão pequena será mais rápida do que utilizar um índice, ignorando o mesmo. Geralmente esta estimativa é correta, mas em nosso caso o operador && tem que buscar todas as geometrias em disco para comparação dos retângulos envolventes, lendo todas as páginas TOAST também.
Para visualizar se você sofre com este bug, utilize um "EXPLAIN ANALYZE" na pesquisa em questão. Para maiores informações e detalhes técnicos, você pode recorrer a lista do postgres sobre desempenho: http://archives.postgresql.org/pgsql-performance/2005-02/msg00030.php
and newer thread on PostGIS https://lists.osgeo.org/pipermail/postgis-devel/2017-June/026209.html
O pessoal responsável pelo PostgreSQL está tentando resolver esta questão por transformar o otimizador de pesquisas ciente das tabelas TOAST. Por enquanto, existem duas soluções:
A primeira solução é forçar o estimador de pesquisar a utilizar o índice. Emita um comando "SET enable_seqscan TO off" ao servidor antes de emitir a pesquisa. Isto força o estimador a evitar scans sequenciais sempre que possível, utilizando o índice GIST como de costume. Mas esta flag deve ser setada para cada conexão e causa o estimador a decidir mal em outros casos, portanto, você deve habilitar "SET enable_seqscan TO on;" após a pesquisa.
A segunda solução é fazer a pesquisa sequencial tão rápida quanto o estimador imagina. Isto pode ser feito criando uma coluna adicional que cacheia o retângulo envolvente e realizando as pesquisas em cima desta coluna. Em nosso exemplo, os comandos são:
SELECT AddGeometryColumn('myschema','mytable','bbox','4326','GEOMETRY','2');
UPDATE mytable SET bbox = ST_Envelope(ST_Force2D(geom));
Altere sua query para usar o operador && contra o retângulo envolvente ao invés da colunas geométrica, assim:
SELECT geom_column
FROM mytable
WHERE bbox && ST_SetSRID('BOX3D(0 0,1 1)'::box3d,4326);
Claro, se você alterar ou adicionar colunas a mytable, você deve manter o retângulo envolvente em sincronia. A forma mais transparente de fazer isto seria através de triggers, mas você também querer modificar sua aplicação para manter a coluna do retângulo envolvente atualizada or executar a query de UPDATE após cada modificação.
Para tabelas que são basicamente somente-leitura, e onde um único índice é utilizado pela maioria das queries, PostgreSQL oferece o comando CLUSTER. Este comando fisicamente reordena todas as linhas da tabela assim como as do índice, assim possibilitando duas melhorias de desempenho: primeiro, para pesquisas de intervalo de índice, o número de pesquisas na tabela de dados é dramaticamente reduzido. Segundo, se seu conjunto de trabalho concentra-se em pequenos intervalos nos índices, você tem um cache mais eficiente, pois todas as informações estão divididas em poucas páginas de dados. (Sinta se convidado para ler a documentação do comando CLUSTER do manual do PostgreSQL.)
Contudo, atualmente o Postgresql não permite a clusterização de índices geométricos GIST, pois estes índices simplesmente ignoram valores nulos, retornando um erro como:
lwgeom=# CLUSTER my_geom_index ON my_table; ERROR: cannot cluster when index access method does not handle null values HINT: You may be able to work around this by marking column "geom" NOT NULL.
Como a HINT da mensagem te diz, você pode adicionar uma constraint "not null" na tabela para contornar o problema.
lwgeom=# ALTER TABLE my_table ALTER COLUMN geom SET not null; ALTER TABLE
Claro, isto não vai funcionar se você de fato precisa de valores NULL em sua coluna geométrica. Adicionalmente, você deve usar o método acima para adicionar a constraint. Utilizar uma constraint do tipo CHECK como "ALTER TABLE blubb ADD CHECK (geometry is not null);" não irá funcionar.
Algumas vezes, você tem dados que são 3D ou 4D em sua tabela, mas sempre acessa-os usando métodos OpenGIS, como ST_AsText() ou ST_AsBinary(), que somente funcionam em geometrias 2D. Eles fazem isso internamente chamando a função ST_Force2D(), que introduza um gasto extra para grandes geometrias. Para evitar este gasto extra, pode ser viável dropar essas dimensões adicionais para sempre:
UPDATE mytable SET geom = ST_Force2D(geom); VACUUM FULL ANALYZE mytable;
Note que se você adicionou sua coluna geométrica utilizando o método AddGeometryColumn(), existirá uma constraint na dimensão da geometria. Para contornar isto, você precisará dropar a constraint também. Lembre-se de atualizar a entrada na tabela geometry_columns e recriar a constraint posteriormente.
No caso de grandes tabelas, pode ser sábio dividir este UPDATE em porções menores, restringindo o UPDATE a pequenas partes da tabela com o uso de uma cláusula WHERE sobre sua PRIMARY KEY ou outro critério, rodando um VACUUM, entre os UPDATEs. Isto reduz drasticamente a necessidade de espaço em disco temporário. Adicionalmente, se você tem geometrias de dimensões mistas, restrigir o UPDATE por "WHERE dimension(the_geom)>2" pula as geometrias que já estão em 2D.