<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="content-type" content="text/html;
charset=ISO-8859-1">
</head>
<body bgcolor="#ffffff" text="#000000">
Hello all,<br>
<br>
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?<br>
<br>
Here is my SQL table:<br>
<blockquote><big><tt><small><small>CREATE TABLE
feed_all.common_pos_messages</small></small></tt></big><br>
<big><tt><small><small>(</small></small></tt></big><br>
<big><tt><small><small> msg_id bigint NOT NULL,</small></small></tt></big><br>
<big><tt><small><small> msg_type smallint NOT NULL,</small></small></tt></big><br>
<big><tt><small><small> pos_accuracy boolean NOT NULL DEFAULT
false,</small></small></tt></big><br>
<big><tt><small><small> pos_raim boolean NOT NULL DEFAULT false,</small></small></tt></big><br>
<big><tt><small><small> pos_lon integer NOT NULL DEFAULT (181 *
600000),</small></small></tt></big><br>
<big><tt><small><small> pos_lat integer NOT NULL DEFAULT (91 *
60000),</small></small></tt></big><br>
<big><tt><small><small> pos_point geometry,</small></small></tt></big><br>
<big><tt><small><small> CONSTRAINT common_pos_messages_pkey
PRIMARY KEY (msg_id),</small></small></tt></big><br>
<big><tt><small><small> CONSTRAINT
common_pos_messages_msg_id_fkey FOREIGN KEY (msg_id)</small></small></tt></big><br>
<big><tt><small><small> REFERENCES feed_all.messages (msg_id)
MATCH SIMPLE</small></small></tt></big><br>
<big><tt><small><small> ON UPDATE NO ACTION ON DELETE NO
ACTION,</small></small></tt></big><br>
<big><tt><small><small> CONSTRAINT
common_pos_messages_msg_type_check CHECK (msg_type = ANY
(ARRAY[1, 2, 3, 4, 9, 11, 18, 19, 21])),</small></small></tt></big><br>
<big><tt><small><small> CONSTRAINT
common_pos_messages_pos_lat_check CHECK (pos_lat >=
((-90) * 600000) AND pos_lat <= (90 * 600000) OR
pos_lat = (91 * 600000)),</small></small></tt></big><br>
<big><tt><small><small> CONSTRAINT
common_pos_messages_pos_lon_check CHECK (pos_lon >=
((-180) * 600000) AND pos_lon <= (180 * 600000) OR
pos_lon = (181 * 600000)),</small></small></tt></big><br>
<big><tt><small><small> CONSTRAINT enforce_dims_pos_point CHECK
(st_ndims(pos_point) = 2),</small></small></tt></big><br>
<big><tt><small><small> CONSTRAINT enforce_geotype_pos_point
CHECK (geometrytype(pos_point) = 'POINT'::text OR
pos_point IS NULL),</small></small></tt></big><br>
<big><tt><small><small> CONSTRAINT enforce_srid_pos_point CHECK
(st_srid(pos_point) = 4326)</small></small></tt></big><br>
<big><tt><small><small>)</small></small></tt></big><br>
<big><tt><small><small>WITH (</small></small></tt></big><br>
<big><tt><small><small> OIDS=FALSE</small></small></tt></big><br>
<big><tt><small><small>);</small></small></tt></big><br>
<big><tt><small><small>ALTER TABLE feed_all.common_pos_messages
OWNER TO developer;</small></small></tt></big><br>
<br>
<big><tt><small><small>-- Index: feed_all.idx_msg_id</small></small></tt></big><br>
<br>
<big><tt><small><small>-- DROP INDEX feed_all.idx_msg_id;</small></small></tt></big><br>
<br>
<big><tt><small><small>CREATE UNIQUE INDEX idx_msg_id</small></small></tt></big><br>
<big><tt><small><small> ON feed_all.common_pos_messages</small></small></tt></big><br>
<big><tt><small><small> USING btree</small></small></tt></big><br>
<big><tt><small><small> (msg_id);</small></small></tt></big><br>
<br>
<big><tt><small><small>-- Index: feed_all.idx_pos</small></small></tt></big><br>
<br>
<big><tt><small><small>-- DROP INDEX feed_all.idx_pos;</small></small></tt></big><br>
<br>
<big><tt><small><small>CREATE INDEX idx_pos</small></small></tt></big><br>
<big><tt><small><small> ON feed_all.common_pos_messages</small></small></tt></big><br>
<big><tt><small><small> USING gist</small></small></tt></big><br>
<big><tt><small><small> (pos_point);</small></small></tt></big><br>
</blockquote>
Any advice will be much appreciated<br>
Kind Regards<br>
Yiannis<br>
<br>
</body>
</html>