Re: function indexes, index only scan and sorting

Jonathan Vanasco <postgres@2xlp.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