From: Pavel Kulakov <paul.kulakov@systematica.ru>
To: pgsql-performance@lists.postgresql.org
Subject: PostgreSQL doesn't use index-only scan if there is an expression in index
Date: Thu, 15 Feb 2024 17:37:45 +0300
Message-ID: <008e01da601c$8a6a3d20$9f3eb760$@systematica.ru> (raw)
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
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: 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: <008e01da601c$8a6a3d20$9f3eb760$@systematica.ru>
* 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