Hello all,
I am involved in a heavy database design initiative where the only kind
of geometries I am dealing with are points. I have recently hit a
50million rows long table with those points and my default gist index on
the points does not seem to be working very fast (if not at all to be
honest). I have started now thinking that probably for "points" an index
may not be the best option since in a 50million rows long table most of
the points are unique so the index may just duplicate the actual table,
of course I may be wrong and I may just missing a very important part of
the concept. So while I am posting to this list I am thinking to create
some major geometries, like bounding boxes of groups of points to
provide a better index. Does this sound sensible or indeed I can get
away with my points index and I just need to do "something" more?
Here is my SQL table:
CREATE TABLE feed_all.common_pos_messages
(
msg_id bigint NOT NULL,
msg_type smallint NOT NULL,
pos_accuracy boolean NOT NULL DEFAULT false,
pos_raim boolean NOT NULL DEFAULT false,
pos_lon integer NOT NULL DEFAULT (181 * 600000),
pos_lat integer NOT NULL DEFAULT (91 * 60000),
pos_point geometry,
CONSTRAINT common_pos_messages_pkey PRIMARY KEY (msg_id),
CONSTRAINT common_pos_messages_msg_id_fkey FOREIGN KEY (msg_id)
REFERENCES feed_all.messages (msg_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT common_pos_messages_msg_type_check CHECK (msg_type =
ANY (ARRAY[1, 2, 3, 4, 9, 11, 18, 19, 21])),
CONSTRAINT common_pos_messages_pos_lat_check CHECK (pos_lat >=
((-90) * 600000) AND pos_lat <= (90 * 600000) OR pos_lat = (91 *
600000)),
CONSTRAINT common_pos_messages_pos_lon_check CHECK (pos_lon >=
((-180) * 600000) AND pos_lon <= (180 * 600000) OR pos_lon = (181 *
600000)),
CONSTRAINT enforce_dims_pos_point CHECK (st_ndims(pos_point) = 2),
CONSTRAINT enforce_geotype_pos_point CHECK
(geometrytype(pos_point) = 'POINT'::text OR pos_point IS NULL),
CONSTRAINT enforce_srid_pos_point CHECK (st_srid(pos_point) = 4326)
)
WITH (
OIDS=FALSE
);
ALTER TABLE feed_all.common_pos_messages OWNER TO developer;
-- Index: feed_all.idx_msg_id
-- DROP INDEX feed_all.idx_msg_id;
CREATE UNIQUE INDEX idx_msg_id
ON feed_all.common_pos_messages
USING btree
(msg_id);
-- Index: feed_all.idx_pos
-- DROP INDEX feed_all.idx_pos;
CREATE INDEX idx_pos
ON feed_all.common_pos_messages
USING gist
(pos_point);
Any advice will be much appreciated
Kind Regards
Yiannis
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
