PostgreSQL doesn't use index-only scan if there is an expression in index

Hello,

PostgreSQL doesn't use 'Index Only Scan' if there is an expression in index.

The documentation says that PostgreSQL's planner considers a query to be potentially executable by index-only scan only when all columns needed by the query are available from the index.

I think an example on https://www.postgresql.org/docs/16/indexes-index-only-scans.html :

SELECT f(x) FROM tab WHERE f(x) < 1;

is a bit confusing. Even the following query does not use 'Index Only Scan'

SELECT 1 FROM tab WHERE f(x) < 1;

Demonstration:

---------------------------

drop table if exists test;

create table test(s text);

create index ix_test_upper on test (upper(s));

create index ix_test_normal on test (s);

insert into test (s)

select 'Item' || t.i

from pg_catalog.generate_series(1, 100000, 1) t(i);

analyze verbose "test";

explain select 1 from test where s = 'Item123';

explain select 1 from test where upper(s) = upper('Item123');

--------------------------

Query plan 1:

Index Only Scan using ix_test_normal on test  (cost=0.42..8.44 rows=1 width=4)

  Index Cond: (s = 'Item123'::text)

Query plan 2 (SHOULD BE 'Index Only Scan'):

Index Scan using ix_test_upper on test  (cost=0.42..8.44 rows=1 width=4)

  Index Cond: (upper(s) = 'ITEM123'::text)

------------------------  

If I add 's' as included column to ix_test_upper the plan does use 'Index Only Scan'. That looks strange to me: there is no 's' in SELECT-clause, only in WHERE-clause in the form of 'upper(s)' and this is why ix_test_upper is choosen by the planner.

Thanks,

Pavel