Thread: 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/