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
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 |