Re: sorting problem

Chris Smith <chris(at)interspire(dot)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:

select * from x where lower(lastname) between ? and ? order by lower(lastname)

or

select * from x order by lower(lastname) offset ? limit ?

Though this will eventually switch to sorting when the offset is large.
Better is to use something like:

select * from x where lower(lastname) > ? order by lower(lastname) limit ?

or perhaps something like this if a merge join with fast start is useful:

select * from x join y on (x.lower(lastname)=y.lower(lastname))

But

--
greg