Re: [GENERAL] What's the best way to index this table for speed?

[Bruno Wolff III]

On Thu, Feb 01, 2007 at 10:42:30 -0800,
  Carl Lerche <[EMAIL PROTECTED]> wrote:
> 
> How can I index 2 dimensional data (latitude / longitude) with a
> status_id column too (integer) so that I can perform the following
> query as fast as possible:
> 
>SELECT * FROM profiles WHERE status_id = 1 AND latitude BETWEEN
>  AND  AND longitude BETWEEN  AND ;
> 
> Obviously a btree index wouldn't work well and in the documentations
> it said an rtree index works for 2 dimensional queries, but I would
> like to filter first by status_id since that will probably eliminate
> 50%+ of the rows in the table. There are currently over 600 000 rows
> in the table so far, and it will be growing to well over a million.

The earth distance contrib will use gist indexes based on the underlying
cube data type (also in contrib).

The the location is at all selective, then you probably don't need to worry
about status as it isn't very selective. If your data is clustered on the
disk mostly by location, it might not by you much at all since the data will
like be read from disk anyway.

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] What's the best way to index this table for speed?

[Steve Wormley]

On 2/1/07 10:42 AM, "Carl Lerche" <[EMAIL PROTECTED]> wrote:

> How can I index 2 dimensional data (latitude / longitude) with a
> status_id column too (integer) so that I can perform the following
> query as fast as possible:
> 
> SELECT * FROM profiles WHERE status_id = 1 AND latitude BETWEEN
>  AND  AND longitude BETWEEN  AND ;

Depending on what your needs are, I'd look into PostGIS and make your data
points and doing bounding box queries using a GIST index either with a
partial index or indexes depending on how many status ids:

create index xyz on profiles using gist(mypoint) where status_id = 1;

Or a multicolumn index using the btree_gist contrib module:

create index xyz on profiles using gist (status_id,mypoint);

I use the first format as I only have 2 statuses, valid and not valid and it
works quite well. If PostGIS won't work for you you still can possibly use a
partial index if it works for your status_ids.

-Steve Wormley
Senior Consultant
inCode Wireless
a Verisign Company



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster