[postgis-users] Point as Index

[Ioannis Anagnostopoulos]

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110330/f73ea9e9/attachment.html>