Michael Lewis <mlewis@entrata.com> writes:
> On Sun, Jun 21, 2020 at 10:43 PM Sankar P <sankar.curiosity@gmail.com>
> wrote:
>> 2) CREATE INDEX idx_btree_records ON fluent USING BTREE ((record ->>
>> 'destinationServiceName'));
> If it is rarely used, create a partial index perhaps. I am a little
> surprised that the plain btree index wasn't used from my naive point of
> view.
It's not terribly surprising unfortunately. The planner will seldom
recognize that an expression index is good for anything except
searches, that is "WHERE indexed_expression indexable_operator constant".
There's some mention of this at
https://www.postgresql.org/docs/current/indexes-index-only-scans.html
The core of the problem is not wanting to expend cycles on trying to
match every subexpression in the query to every index expression;
so at least in early planning stages, only potentially-indexable
subexpressions of WHERE clauses get matched to indexes. In the
example of "select distinct expression", the planner will never notice
that that expression has anything to do with an index.
regards, tom lane
