Re: create index on function - why?

> Wei Weng wrote:
> > I can understand the rationale behind creating index on tables, it
> > speeds up the searching.
> >
> > But what is the rationale behind creating index on a function? how does
> > it help with the database performance?

Say you always use a query like this:

select * from mytable where upper(name) = 'ASDF';

Now, postgres CANNOT use its indices as it has to retrieve every single row,
convert the 'name' field to upper case, and then compare it to 'ASDF'.

Now, say you add an index like this:

create index myindex on mytable (upper(name));

Now, postgres can just use the 'myindex' index instead of having to scan
every row.

Is that any clearer?

Chris