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
