Re: optimizing "between" queries

Kyle <kaf@nwlink.com> writes:
> I have a table where I need to select elements that are between
> certain values.  The typical query would be like:

>   select foo from bar where
>        element1 between ? and ? and  -- using some val_1a and val_1b
>        element2 between ? and ? and  -- using some val_2a and val_2b
>        ...
>        element30 between ? and ?;

> ie- we've got 30 elements here.  The runtime is acceptable now with
> 10k rows in the table (about 0.04 seconds).  However, the table size
> will grow to the milions in the near future.

> The data for element1..30 are int2's and columns element1..element30
> have been ordered by how well they discriminate.  Can I use "cluster"
> on more than one column with indexes per column to improve the search
> time?  How else might I be able to tweak this?

An indexscan can only use one index.  You could use a multicolumn index
effectively with such a query:

    create index fooi on foo(element1, element2, ...);

which will make use of the clauses
       element1 between ? and ? and
       element2 between ? and ?
as indexscan limits, with the rest checked on-the-fly.

Note that such an index is completely ineffective if you don't specify a
WHERE constraint for element1 --- in general, the system knows how to
use the first K columns of an N-column index if there are WHERE
constraints for all K columns.

I doubt it'd be worth your while to set up an index with more than, say,
half a dozen columns ... maybe not even that many.  The more columns,
the more specialized the use of the index is --- and the larger and
slower to update/search it is.  It's good advice in general not to go
overboard with creating lots of specialized indexes.  Think hard about
what fraction of your queries can really exploit a particular index.

            regards, tom lane