Обсуждение: multicolumn index vs two indexes

Hello:

I have a table whith an id field, a date field and 5 millions rows. I
need make queries like

select *on table where id='001' and fecha >='1-1-1999' and fecha
<'1-1-2000';

What is better? To create a multicolumn btree index with (date, id) or
two indexes, one btree for the date and a hash for the id? Where can I
find mor accurate info about indexes? (the manual is a little bit short
of it).

Thanks

--
Salva

Salvador Mainé writes:

> I have a table whith an id field, a date field and 5 millions rows. I
> need make queries like
>
> select *on table where id='001' and fecha >='1-1-1999' and fecha
> <'1-1-2000';
>
> What is better? To create a multicolumn btree index with (date, id) or
> two indexes, one btree for the date and a hash for the id?

A query can only use at most one index, so two indices is out.  Perhaps it
would also make sense to create an index on "id" only, depending on what
the selectivity is.

> Where can I find mor accurate info about indexes? (the manual is a
> little bit short of it).

Try the 7.1 manual:
http://www.postgresql.org/devel-corner/docs/postgres/indices.html

--
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/