Bases de dados espaciais podem conter milhões de objetos geográficos. Em alguns casos, uma consulta PostGIS pode levar vários segundos — ou até vários minutos.
Esse problema geralmente acontece quando:
- os índices espaciais não são utilizados
- as geometrias são complexas
- as consultas espaciais estão mal construídas
- ou as estatísticas da base de dados não estão atualizadas.
Neste artigo, veremos as etapas clássicas para diagnosticar e otimizar uma consulta PostGIS lenta.
1. Começar analisando a consulta
O primeiro passo consiste em compreender como o PostgreSQL executa a consulta.
Para isso, utiliza-se:
EXPLAIN ANALYZE
Exemplo:
EXPLAIN ANALYZE
SELECT *
FROM routes r
JOIN communes c
ON ST_Intersects(r.geom, c.geom);
Esse comando permite verificar:
- se um índice espacial está sendo utilizado
- quantas linhas são analisadas
- quanto tempo leva cada etapa.
Se aparecer:
Seq Scan
isso significa que o PostgreSQL está percorrendo toda a tabela.
2. Verificar a presença de índices espaciais
Os índices são essenciais para consultas espaciais.
Um índice espacial normalmente é criado assim:
CREATE INDEX idx_routes_geom
ON routes
USING GIST (geom);
Os índices permitem que o PostgreSQL reduza drasticamente o número de geometrias analisadas.
Sem um índice espacial, o desempenho pode ser desastroso.
3. Utilizar operadores espaciais compatíveis com índices
Algumas funções do PostGIS utilizam automaticamente os índices espaciais.
Por exemplo:
- ST_Intersects
- ST_Within
- ST_Contains
No entanto, muitas vezes é útil adicionar um filtro de bounding box:
geom && other_geom
Exemplo:
SELECT *
FROM routes r
JOIN communes c
ON r.geom && c.geom
AND ST_Intersects(r.geom, c.geom);
O teste de bounding box é muito mais rápido.
4. Verificar a complexidade das geometrias
Geometrias muito detalhadas podem tornar algumas operações significativamente mais lentas.
Em alguns casos, pode ser útil simplificar as geometrias:
ST_Simplify(geom, tolerance)
ou criar uma tabela simplificada para determinadas análises.
5. Evitar certas funções nas cláusulas WHERE
Algumas funções impedem o uso eficiente dos índices.
Por exemplo:
ST_Buffer(geom, 100)
em uma cláusula WHERE pode tornar a consulta muito lenta.
Exemplo ruim:
WHERE ST_Intersects(ST_Buffer(geom,100), zone)
Muitas vezes é melhor reformular a consulta.
6. Verificar as estatísticas da base de dados
O PostgreSQL utiliza estatísticas para escolher o plano de execução.
Após muitas modificações nos dados, recomenda-se executar:
ANALYZE
ou
VACUUM ANALYZE
Isso ajuda a melhorar o plano de consulta.
7. Limitar os dados analisados
Frequentemente é útil reduzir o volume de dados antes das operações espaciais.
Por exemplo, utilizando um filtro por atributo:
WHERE type = ‘rodovia’
Isso reduz o número de objetos analisados.
8. Testar diferentes abordagens
Em alguns casos, diferentes formas de escrever a consulta podem produzir desempenhos muito diferentes.
Por isso, é útil testar diferentes estratégias:
- junção espacial
- subconsulta
- materialização intermediária.
9. Os 5 erros mais comuns no PostGIS que deixam uma consulta lenta
Alguns erros aparecem com muita frequência em bases de dados espaciais.
Ausência de índice espacial
Essa é a causa mais comum.
Sem índice espacial, o PostgreSQL precisa analisar todas as geometrias da tabela.
Criar um índice:
CREATE INDEX idx_routes_geom
ON routes
USING GIST (geom);
Ordem incorreta das operações
Algumas consultas executam diretamente uma operação espacial custosa em toda a tabela.
Exemplo ineficiente:
SELECT *
FROM edificios
WHERE ST_Intersects(geom, ST_Buffer(:zone,100));
Nesse caso, a função ST_Buffer é recalculada para cada linha.
Função espacial utilizada sem filtro espacial
Uma junção espacial pode ser muito lenta se não houver um filtro de bounding box.
Exemplo recomendado:
SELECT *
FROM routes r
JOIN communes c
ON r.geom && c.geom
AND ST_Intersects(r.geom, c.geom);
O operador && utiliza o índice espacial.
Geometrias excessivamente complexas
Algumas geometrias contêm milhares de vértices.
Em certos casos pode ser útil utilizar:
ST_Simplify(geom, tolerance)
ou armazenar uma versão simplificada.
Estatísticas PostgreSQL desatualizadas
Se a tabela foi fortemente modificada, o PostgreSQL pode escolher um plano de execução ruim.
Atualizar estatísticas:
VACUUM ANALYZE
Exemplo real de otimização
Suponha uma consulta que procura estradas dentro de um município.
Consulta inicial:
SELECT r.*
FROM routes r, communes c
WHERE ST_Intersects(r.geom, c.geom)
AND c.nom = ‘Paris’;
Tempo de execução: 45 segundos
Versão otimizada
SELECT r.*
FROM routes r
JOIN communes c
ON r.geom && c.geom
AND ST_Intersects(r.geom, c.geom)
WHERE c.nom = ‘Paris’;
Com:
- índice espacial
- filtro de bounding box
Tempo de execução: menos de um segundo
Checklist rápida para diagnosticar uma consulta PostGIS lenta
Quando uma consulta demora demais, verifique:
✔ a consulta com EXPLAIN ANALYZE
✔ a presença de um índice espacial
✔ o uso de operadores compatíveis com índices
✔ a complexidade das geometrias
✔ a presença de um filtro por atributo
✔ as estatísticas PostgreSQL (ANALYZE)
Conclusão
Quando uma consulta PostGIS é lenta, é importante seguir uma abordagem progressiva:
- analisar a consulta com EXPLAIN ANALYZE
- verificar os índices espaciais
- utilizar operadores compatíveis com índices
- simplificar as geometrias quando necessário
- manter as estatísticas da base de dados atualizadas.
Com essas etapas simples, muitas vezes é possível reduzir o tempo de execução de vários minutos para apenas alguns segundos.
Na maioria dos casos, uma consulta PostGIS lenta está relacionada a um índice ausente ou a um plano de execução inadequado. Uma análise metódica com EXPLAIN ANALYZE quase sempre permite identificar o problema.
