Re: function indexes, index only scan and sorting

On Dec 12, 2014, at 4:58 PM, Tom Lane wrote:

> 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)


Thank you so much for posting this test.

I got a seq scan on my local machine, so I checked the version... still running 9.2.4.
I tried it on production (which is 9.3.x) and got the same result as you.

Looking at the 9.3 release notes, I'm guessing this behavior is from one of the Optimizer fixes.