Hoje veremos como é possível aumentar a performance de sua consulta espacial com join, de uma maneira de certa forma “estranha”. Para este teste de desempenho com o PostGIS onde queremos saber quantos pontos estão dentro de cada polígono, temos: Uma coleção de polígonos de tamanhos variáveis ​​e uma coleção de pontos.

Esse teste é uma boa maneira de testar indexação, cálculos de pontos em polígonos e sobrecarga geral.

1. Configuração

Primeiro baixe alguns polígonos e alguns pontos:

– Países
– Lugares

Carregue os shapefiles em seu banco de dados:

shp2pgsql -s 4326 -D -I ne_10m_admin_0_countries.shp countries | psql performance
shp2pgsql -s 4326 -D -I ne_10m_populated_places.shp places | psql performance

Agora estamos prontos com 255 países e 7343 lugares.

Uma coisa a notar sobre os países é que eles são objetos bastante grandes, com 149 deles tendo vértices suficientes para serem armazenados em tuplas TOAST.

SELECT count(*) 
  FROM countries 
  WHERE ST_NPoints(geom) > (8192 / 16);

2. Desempenho de baseline

Agora podemos executar o teste de desempenho de baseline.

SELECT count(*), c.name 
  FROM countries c 
  JOIN places p 
  ON ST_Intersects(c.geom, p.geom) 
  GROUP BY c.name;

Essa consulta levou 25 segundos. Se você colocar o processo em um profiler enquanto o executa, descobrirá que mais de 20 segundos são gastos na função pglz_decompress, e não fazendo algoritmos espaciais ou geometria computacional, apenas descomprimindo a geometria antes de entregá-la ao processamento real.

Porém, existem maneiras inteligentes de evitar essa sobrecarga:

  • Correção do PostgreSQL para permitir a descompactação parcial de geometrias.
  • No formato de serialização incluir uma chave hash exclusiva na frente das geometrias.

Essas são maneiras legais de manter a compactação para geometrias grandes e ser mais rápido ao alimentá-las.

No entanto, eles ignoram uma abordagem mais brutal e facilmente testável para evitar a descompressão: apenas não comprimem em primeiro lugar.

3. Um truque estranho

O PostGIS usa a opção de armazenamento “principal” para seu tipo de geometria. A opção principal tenta manter as geometrias na tabela original até que elas fiquem muito grandes, depois as compacta e as move para TOAST.

Existe outra opção “externa” que mantém as geometrias, e se elas ficarem muito grandes, são movidas para TOAST descomprimidas. O PostgreSQL permite que você altere o armazenamento em colunas em tempo de execução, portanto não é necessário hackear ou codificar para tentar isso.

-- Altere o tipo do storage
ALTER TABLE countries
  ALTER COLUMN geom
  SET STORAGE EXTERNAL;

-- Force a reescrita da coluna
UPDATE countries
  SET geom = ST_SetSRID(geom, 4326);

-- Execute novamente a query  
SELECT count(*), c.name 
  FROM countries c 
  JOIN places p 
  ON ST_Intersects(c.geom, p.geom) 
  GROUP BY c.name;

A junção espacial agora é executada em menos de 4 segundos .

Qual é a penalidade?

  • Com um armazenamento “principal” a tabela + toast + index é de 6MB.
  • Com um armazenamento “externo” a tabela + toast + index é de 9MB.

4. Conclusão

Para uma penalidade de armazenamento de 50%, em uma tabela que possui objetos muito maiores do que a maioria das tabelas espaciais, alcançamos uma melhoria de desempenho de 500%. Talvez não devêssemos aplicar compressão à nossa geometria?

Usar o armazenamento “principal” foi principalmente uma chamada de julgamento quando decidimos, não foi aferido nem nada – é possível que estivéssemos errados. Além disso, apenas objetos grandes são compactados; uma vez que a maioria das tabelas é cheia de pequenos objetos (linhas curtas, pontos), mudar para “externo” por padrão não teria qualquer efeito no tamanho do armazenamento.

Este post foi traduzido e adaptado livremente do post originalmente escrito por Paul Ramsey, do blog CleverElephant.

Fonte: Blog CleverElephant