Michael Lewis <mlewis@xxxxxxxxxxx> writes: > On Sun, Jun 21, 2020 at 10:43 PM Sankar P <sankar.curiosity@xxxxxxxxx> > 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
