Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1racs7-00AgK2-Al for pgsql-performance@arkaria.postgresql.org; Thu, 15 Feb 2024 14:37:55 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1racs6-00DhS9-0I for pgsql-performance@arkaria.postgresql.org; Thu, 15 Feb 2024 14:37:54 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1racs5-00DhS1-I0 for pgsql-performance@lists.postgresql.org; Thu, 15 Feb 2024 14:37:53 +0000 Received: from h55.sserv.ru ([95.215.177.133]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1racs2-006tTa-01 for pgsql-performance@lists.postgresql.org; Thu, 15 Feb 2024 14:37:52 +0000 Received: from [194.190.106.52] (helo=kulakov) by h55.sserv.ru with esmtpa (Exim 4.92.3) (envelope-from ) id 1racrx-0007Ze-64 for pgsql-performance@lists.postgresql.org; Thu, 15 Feb 2024 17:37:45 +0300 From: "Pavel Kulakov" To: 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> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_008F_01DA6035.AFB85F80" X-Mailer: Microsoft Outlook 16.0 Content-Language: ru Thread-Index: AdpgHGxehjSo4iVIQrW7Ihjow2Dcuw== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_008F_01DA6035.AFB85F80 Content-Type: text/plain; charset="koi8-r" Content-Transfer-Encoding: 7bit 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 ------=_NextPart_000_008F_01DA6035.AFB85F80 Content-Type: text/html; charset="koi8-r" Content-Transfer-Encoding: quoted-printable

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 =3D = 'Item123';

explain select 1 from test where upper(s) =3D = upper('Item123');

--------------------------

Query plan = 1:

Index = Only Scan using ix_test_normal on test=9A (cost=3D0.42..8.44 rows=3D1 = width=3D4)

=9A Index Cond: (s =3D = 'Item123'::text)

=9A

Query plan 2 (SHOULD BE 'Index Only = Scan'):

Index Scan using ix_test_upper on test=9A = (cost=3D0.42..8.44 rows=3D1 width=3D4)

=9A Index Cond: (upper(s) =3D = 'ITEM123'::text)

------------------------=9A=9A

 

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

------=_NextPart_000_008F_01DA6035.AFB85F80--