Re: function indexes, index only scan and sorting

Jonathan Vanasco <postgres(at)2xlp(dot)com> writes:
> Am I correct in observing that the value of a function index can't be used for sorting ?

No ...

regression=# create table tt (f1 int, f2 text);
CREATE TABLE
regression=# create index on tt (lower(f2));
CREATE INDEX
regression=# explain select * from tt order by lower(f2);
QUERY PLAN
----------------------------------------------------------------------------
Index Scan using tt_lower_idx on tt (cost=0.15..65.68 rows=1230 width=36)
(1 row)

Now, whether the planner will prefer this over seqscan-and-sort is a
much harder question. Full-table index scans tend to require a lot
of random I/O so a sort is frequently seen as cheaper.

regards, tom lane