From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | Pavel Kulakov <paul(dot)kulakov(at)systematica(dot)ru>, pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: PostgreSQL doesn't use index-only scan if there is an expression in index |
Date: | 2024-02-15 15:01:07 |
Message-ID: | 0af3046ce4a3ac4cb41e987a64f2d7885f54356b.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Lars Aksel Opsahl | 2024-02-19 16:14:25 | "not related" code blocks for removal of dead rows when using vacuum and this kills the performance |
Previous Message | Pavel Kulakov | 2024-02-15 14:37:45 | PostgreSQL doesn't use index-only scan if there is an expression in index |