On Thu, 2024-02-15 at 17:37 +0300, Pavel Kulakov wrote:
> 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.
You need to create the index like this:
CREATE INDEX ix_test_upper ON test (upper(s)) INCLUDE (s);
See https://www.postgresql.org/docs/current/indexes-index-only-scans.html:
"In principle, index-only scans can be used with expression indexes.
For example, given an index on f(x) where x is a table column, it
should be possible to execute
SELECT f(x) FROM tab WHERE f(x) < 1;
as an index-only scan; and this is very attractive if f() is an
expensive-to-compute function. However, PostgreSQL's planner is currently
not very smart about such cases. It considers a query to be potentially
executable by index-only scan only when all columns needed by the query
are available from the index. In this example, x is not needed except in
the context f(x), but the planner does not notice that and concludes that
an index-only scan is not possible. If an index-only scan seems sufficiently
worthwhile, this can be worked around by adding x as an included column,
for example
CREATE INDEX tab_f_x ON tab (f(x)) INCLUDE (x);
Yours,
Laurenz Albe