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

On Thu, Feb 01, 2007 at 10:42:30 -0800,
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>;
>
> 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.