5

Like shown in another question, I deal with a lot (>10,000,000) entries of points in a 3D space. These points are defined like this:

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

If I am not mistaken 3*8 bytes + 8 bytes padding are needed (MAXALIGN is 8) to store one of these points. Is there a better way to store this kind of data? In the afore mentioned question it was stated that composite types involve quite some overhead.

I oftentimes do spatial queries like this:

  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;

A query like this takes about 160 ms, but I wonder if this could be reduced.

This is the table layout the structure is used in:

    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)
  • 2
    have you considered PostGIS to manage spatial data ? – simplexio Sep 19 '14 at 12:03
  • Check out my answer for a solution with PostGIS. – Evan Carroll Jan 19 '18 at 18:45
3

The composite type is clean design, but it does not help performance at all.

First of all, float translates to float8 a.k.a. double precision in Postgres. You are building on a misunderstanding.
The real data type occupies 4 byte (not 8). It has to be aligned at multiples of 4 bytes.

Measure actual sizes with pg_column_size().

SQL Fiddle demonstrating actual sizes.

The composite type real3d occupies 36 bytes. That's:

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

If you embed that into a table, padding may have to be added. On the other hand the header of the type can be 3 byte smaller on disk. Representation on disk is typically a bit smaller than in RAM. Doesn't make a lot of difference.

More:

Table layout

Use this equivalent design to reduce row size substantially:

    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                  |

Test before and after to verify my claim:

SELECT pg_relation_size('treenode') As table_size;

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

More details:

  • I see, thanks for the explanation and the SQL Fiddle example. So it seems it would help performance if I transform the real3d type (as it should be called) into three columns on of the treenode table, correct? – tomka Jul 30 '14 at 20:17
  • @tomka: Absolutely. But there's more ... Consider the update. – Erwin Brandstetter Jul 30 '14 at 20:29
  • Thanks a lot! I try to rebuild the table with your suggested layout. Is the order of the columns important for performance aside from alignment/passing issues? – tomka Jul 30 '14 at 20:31
  • @tomka: Yes. Well, it's mainly "column tetris" to reduce storage size, which helps overall performance. I optimized the order. Have a look at the linked answer. Plus, I took a couple of other considerations into account (NULL columns last helps a tiny bit. BTW, do you actually need a bigint pk? Do you expect a lot of rows over the course of time? – Erwin Brandstetter Jul 30 '14 at 20:33
  • What were your considerations for reordering the columns aside from alignment? I could imagine that often read columns should come first? Unfortunately, I do need a bigint pk. I expect the table to grow to some billions of rows. – tomka Jul 30 '14 at 20:36
1

PostGIS

PostGIS is easy and gives you a bunch of functions for you to query with. The PostGIS solution is simple and it'll use an index too. Here we use 3d (to accommodate your z), but I'm not sure if you need it.

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)
);

If all of your points and geoms have an z = 41000, just use 2d geoms.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.