Index only scans for expressional indices when querying for the expression

From: Danny Shemesh <dany74q(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Index only scans for expressional indices when querying for the expression
Date: 2022-08-04 09:59:42
Message-ID: CAFZC=QqFpth2mCw=vQHak_mpcY9s_M3HVP6TgChAdf6Lnq_bEg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello everyone,

Quick question here about index-only scans and expressional indices, tested
on postgres <= 14,
Say I have a column, and an expressional index on said column (fiddle
<https://www.db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/5370>) - e.g.

create table t1 (x text);
create index idx_upper on t1 (upper(x));

I see that even if I query for upper(x) in itself, I won't achieve an
index-only scan without creating a covering index that includes the
manipulated field:
create index idx_upper_covering on t1 (upper(x)) include (x);

I wonder if it would've been possible to have an index-only scan for
similar cases without having to include the base column ?
I believe the expressional index in itself could've been considered as
covering, when querying for the expression explicitly.

The thing is, indices with include clauses do not support page
deduplication, which causes the size of the index to bloat in our case,
over 20x in size at times.
Also, it could've been beneficial when creating indices on complex types,
say - indexing the first element on an array, or a specific nested element
of a jsonb column, et-al.

Appreciate your time !
Danny

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ron 2022-08-04 10:22:52 Re: How to choose new master from slaves.?
Previous Message sivapostgres@yahoo.com 2022-08-04 08:52:08 Re: Behavior of identity columns