On Thu, Dec 16, 2004 at 23:33:00 -0500,
Greg Stark <gsstark@mit.edu> wrote:
>
> Chris Smith <chris@interspire.com> writes:
>
> > Would doing it this way require an index:
> >
> > create index lower_lastname on table x lower(lastname);
>
> Well it doesn't *require* but it may be a good idea. It depends on your
> queries. It will NOT be useful for a query like:
>
> select * from x order by lower(lastname)
>
> where postgres won't bother with the index since it will be slower than just
> resorting the entire table. The way this index is useful is if you have
> queries of the form:
Using an index to do an order by is an order N operation. Doing a sort
is an order N log N operation. For large values of N, a index will be
faster. The index will slow down write operations, so it may still be
a bad idea in some cases.