On Thu, 21 Aug 2003, scott.marlowe wrote: > On Thu, 21 Aug 2003, Kris Kiger wrote: > > > I would appreciate it if I could get some thoughts on indexing a field > > with only two values? For example, I have a table with a few million > > rows in it. All items in this table are broken up into two categories > > using 'T' or 'F'. It seems logical to me that an index on this field > > would create two logical 'buckets', so that one could say, "I want all > > 'T' values", or "I want all 'F' values" and merely have to look in the > > appropriate bucket, rather than have to perform a sequential scan > > through three million items every time a request is made based on 'T' or > > 'F'. If I were to create an index on a char(1) field that contains only > > values of 'T' or 'F', would the query analyzer override the use of this > > index? How does Postgres address this problem and what are all of your > > thoughts on this issue? I appreciate the help! > > Often the best approach here is to make a partial index: > > create index table_dx on table (bool_field) where bool_field IS TRUE; > > This works well if you have a large portion of the boolean fields set to > FALSE, and want to find the few that are TRUE. Reverse the TRUE and false > for other situations.
A more general question: Can the planner tell which index to use if there is more than one that fits the bill? Like if there is a full index and one or more partial indexes on a field, can it determine which to use for a given query? -- Sam Barnett-Cormack Software Developer | Student of Physics & Maths UK Mirror Service (http://www.mirror.ac.uk) | Lancaster University ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
