L82S7w7i

1CREATE TYPE float3d AS (
2  x real,
3  y real,
4  z real);
5	
6SELECT t1.id, t1.parent_id, (t1.location).x, (t1.location).y, (t1.location).z,
7         t1.confidence, t1.radius, t1.skeleton_id, t1.user_id,
8         t2.id, t2.parent_id, (t2.location).x, (t2.location).y, (t2.location).z,
9         t2.confidence, t2.radius, t2.skeleton_id, t2.user_id
10  FROM treenode t1
11       INNER JOIN treenode t2 ON
12         (   (t1.id = t2.parent_id OR t1.parent_id = t2.id)
13          OR (t1.parent_id IS NULL AND t1.id = t2.id))
14        WHERE (t1.LOCATION).z = 41000.0
15          AND (t1.LOCATION).x > 2822.6
16          AND (t1.LOCATION).x < 62680.2
17          AND (t1.LOCATION).y > 33629.8
18          AND (t1.LOCATION).y < 65458.6
19          AND t1.project_id = 1 LIMIT 5000;
20	
21Column     |           Type           |                       Modifiers                    
22---------------+--------------------------+-------------------------------------------------------
23 id            | bigint                   | not null default nextval('location_id_seq'::regclass)
24 user_id       | integer                  | not null
25 creation_time | timestamp with time zone | not null default now()
26 edition_time  | timestamp with time zone | not null default now()
27 project_id    | integer                  | not null
28 location      | float3d                  | not null
29 editor_id     | integer                  |
30 parent_id     | bigint                   |
31 radius        | real                     | not null default 0
32 confidence    | smallint                 | not null default 5
33 skeleton_id   | integer                  | not null
34
35Indexes:
36    "treenode_pkey" PRIMARY KEY, btree (id)
37    "treenode_parent_id" btree (parent_id)
38    "treenode_project_id_location_x_index" btree (project_id, ((location).x))
39    "treenode_project_id_location_y_index" btree (project_id, ((location).y))
40    "treenode_project_id_location_z_index" btree (project_id, ((location).z))
41    "treenode_project_id_skeleton_id_index" btree (project_id, skeleton_id)
42    "treenode_project_id_user_id_index" btree (project_id, user_id)
43    "treenode_skeleton_id_index" btree (skeleton_id)
44	
4523 byte tuple header
461 byte padding
474 bytes real x
484 bytes real y
494 bytes real z
50---
5136 bytes
52	
53Column     |           Type           |                       Modifiers
54---------------+--------------------------+---------------------------------
55 id            | bigint                   | not null default nextval(...
56 creation_time | timestamp with time zone | not null default now()
57 edition_time  | timestamp with time zone | not null default now()
58 user_id       | integer                  | not null
59 project_id    | integer                  | not null
60 location_x    | real                     | not null
61 location_y    | real                     | not null
62 location_z    | real                     | not null
63 radius        | real                     | not null default 0
64 skeleton_id   | integer                  | not null
65 confidence    | smallint                 | not null default 5
66 parent_id     | bigint                   |
67 editor_id     | integer                  |
68	
69SELECT pg_relation_size('treenode') As table_size;
70
71SELECT avg(pg_column_size(t) AS avg_row_size
72FROM   treenode t;
73	
74CREATE EXTENSION IF NOT EXISTS postgis;
75
76CREATE TABLE t AS (
77  geom  geometry(point)
78);
79INSERT INTO t(geom) VALUES (ST_MakePoint(x,y,z));
80
81CREATE INDEX idx ON table USING gist(geom gist_geometry_ops_nd);
82
83SELECT *
84FROM t
85WHERE geom &&& ST_3DMakeBox(
86  ST_MakePoint(2822.6, 33629.8, 41000.0),
87  ST_MakePoint(62680.2, 65458.6, 41000.0)
88);