Jonathan Vanasco <postgres@xxxxxxxx> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
