Обсуждение: function indexes, index only scan and sorting

Can someone confirm a suspicion for me ?

I have a moderately sized table (20+ columns, 3MM rows) that tracks "tags".

I have a lower(column) function index that is used simplify case-insensitive lookups.

    CREATE INDEX idx_tag_name_lower ON tag(lower(name));

I have a few complex queries that need to join back to this table (via the `id` primary key) and sort on `lower(name)`.

I'm not selecting `lower(name)`, just using it for an order-by.

The only way I seem to be able to avoid a Sequential Scan and run an index-only scan is with another index -- this one
specifically(and I've run queries against 8 index permutations): 

    CREATE INDEX idx_tag_joins ON tag(id, name_display);

Am I correct in observing that the value of a function index can't be used for 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

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.

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

Hmm, well, I get the same result from 9.2.9, as well as every branch back
to 8.4 (too lazy to check further back).  Perhaps your 9.2.4 installation
is using non-default cost parameters that discourage the planner from
choosing this plan?

            regards, tom lane