Início / dba / Perguntas / 72787
Accepted
tomka
Asked: 2014-07-31 11:13:15 +0800 CST

Bom layout de dados de pontos 3D para consultas espaciais no Postgres?

  • 772

Como mostrado em outra pergunta, eu lido com muitas (> 10.000.000) entradas de pontos em um espaço 3D. Esses pontos são definidos assim:

CREATE TYPE float3d AS (
  x real,
  y real,
  z real);

Se não me engano são necessários 3*8 bytes + preenchimento de 8 bytes ( MAXALIGNé 8) para armazenar um desses pontos. Existe uma maneira melhor de armazenar esse tipo de dados? Na pergunta mencionada acima, foi afirmado que os tipos compostos envolvem bastante sobrecarga.

Muitas vezes faço consultas espaciais como esta:

  SELECT t1.id, t1.parent_id, (t1.location).x, (t1.location).y, (t1.location).z,
         t1.confidence, t1.radius, t1.skeleton_id, t1.user_id,
         t2.id, t2.parent_id, (t2.location).x, (t2.location).y, (t2.location).z,
         t2.confidence, t2.radius, t2.skeleton_id, t2.user_id
  FROM treenode t1
       INNER JOIN treenode t2 ON
         (   (t1.id = t2.parent_id OR t1.parent_id = t2.id)
          OR (t1.parent_id IS NULL AND t1.id = t2.id))
        WHERE (t1.LOCATION).z = 41000.0
          AND (t1.LOCATION).x > 2822.6
          AND (t1.LOCATION).x < 62680.2
          AND (t1.LOCATION).y > 33629.8
          AND (t1.LOCATION).y < 65458.6
          AND t1.project_id = 1 LIMIT 5000;

Uma consulta como essa leva cerca de 160 ms, mas gostaria de saber se isso poderia ser reduzido.

Este é o layout da tabela em que a estrutura é usada:

    Column     |           Type           |                       Modifiers                    
---------------+--------------------------+-------------------------------------------------------
 id            | bigint                   | not null default nextval('location_id_seq'::regclass)
 user_id       | integer                  | not null
 creation_time | timestamp with time zone | not null default now()
 edition_time  | timestamp with time zone | not null default now()
 project_id    | integer                  | not null
 location      | float3d                  | not null
 editor_id     | integer                  |
 parent_id     | bigint                   |
 radius        | real                     | not null default 0
 confidence    | smallint                 | not null default 5
 skeleton_id   | integer                  | not null

Indexes:
    "treenode_pkey" PRIMARY KEY, btree (id)
    "treenode_parent_id" btree (parent_id)
    "treenode_project_id_location_x_index" btree (project_id, ((location).x))
    "treenode_project_id_location_y_index" btree (project_id, ((location).y))
    "treenode_project_id_location_z_index" btree (project_id, ((location).z))
    "treenode_project_id_skeleton_id_index" btree (project_id, skeleton_id)
    "treenode_project_id_user_id_index" btree (project_id, user_id)
    "treenode_skeleton_id_index" btree (skeleton_id)
postgresql datatypes

2 respostas

  1. Best Answer

    O tipo composto é um design limpo, mas não ajuda em nada no desempenho .

    Em primeiro lugar, floattraduz para float8aka double precisionno Postgres. Você está construindo sobre um mal-entendido.
    O realtipo de dados ocupa 4 bytes (não 8). Tem que ser alinhado em múltiplos de 4 bytes.

    Meça tamanhos reais com pg_column_size().

    SQL Fiddle demonstrando tamanhos reais.

    O tipo composto real3docupa 36 bytes. Isso é:

    23 byte tuple header
1 byte padding
4 bytes real x
4 bytes real y
4 bytes real z
---
36 bytes

    Se você incorporar isso em uma tabela, pode ser necessário adicionar preenchimento. Por outro lado, o cabeçalho do tipo pode ser 3 bytes menor no disco. A representação no disco é tipicamente um pouco menor do que na RAM. Não faz muita diferença.

    Mais:

    Disposição da tabela

    Use este design equivalente para reduzir substancialmente o tamanho da linha :

        Column     |           Type           |                       Modifiers
---------------+--------------------------+---------------------------------
 id            | bigint                   | not null default nextval(...
 creation_time | timestamp with time zone | not null default now()
 edition_time  | timestamp with time zone | not null default now()
 user_id       | integer                  | not null
 project_id    | integer                  | not null
 location_x    | real                     | not null
 location_y    | real                     | not null
 location_z    | real                     | not null
 radius        | real                     | not null default 0
 skeleton_id   | integer                  | not null
 confidence    | smallint                 | not null default 5
 parent_id     | bigint                   |
 editor_id     | integer                  |

    Teste antes e depois para verificar minha reivindicação:

    SELECT pg_relation_size('treenode') As table_size;

SELECT avg(pg_column_size(t) AS avg_row_size
FROM   treenode t;

    Mais detalhes:

    • 3

  2. PostGIS

    O PostGIS é fácil e oferece várias funções para você consultar. A solução PostGIS é simples e também usará um índice. Aqui usamos 3d (para acomodar seu z), mas não tenho certeza se você precisa.

    CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE t AS (
  geom  geometry(point)
);
INSERT INTO t(geom) VALUES (ST_MakePoint(x,y,z));

CREATE INDEX idx ON table USING gist(geom gist_geometry_ops_nd);

SELECT *
FROM t
WHERE geom &&& ST_3DMakeBox(
  ST_MakePoint(2822.6, 33629.8, 41000.0),
  ST_MakePoint(62680.2, 65458.6, 41000.0)
);

    Se todos os seus pontos e geoms tiverem um z = 41000, use apenas 2d geoms.

    • 1

relate perguntas