From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Pavel Kulakov <paul.kulakov@systematica.ru>
To: pgsql-performance@lists.postgresql.org
Subject: Re: PostgreSQL doesn't use index-only scan if there is an expression in index
Date: Thu, 15 Feb 2024 16:01:07 +0100
Message-ID: <0af3046ce4a3ac4cb41e987a64f2d7885f54356b.camel@cybertec.at> (raw)
In-Reply-To: <008e01da601c$8a6a3d20$9f3eb760$@systematica.ru>
References: <008e01da601c$8a6a3d20$9f3eb760$@systematica.ru>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: laurenz.albe@cybertec.at, paul.kulakov@systematica.ru, pgsql-performance@lists.postgresql.org
Subject: Re: PostgreSQL doesn't use index-only scan if there is an expression in index
In-Reply-To: <0af3046ce4a3ac4cb41e987a64f2d7885f54356b.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox