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

On 2/1/07 10:42 AM, "Carl Lerche" <carl(dot)lerche(at)gmail(dot)com> 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
> <y_1> AND <y_2> AND longitude BETWEEN <x_1> AND <x_2>;

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