Jeff Janes <jeff.janes@gmail.com> writes:
> On Thu, May 12, 2016 at 8:13 AM, Robert Klemme
> <shortcutter@googlemail.com> wrote:
>> On Mon, May 9, 2016 at 11:41 PM, SoDupuDupu wrote:
>>> Keep in mind then LIKE '%test_1' and LIKE 'test_1' are not equivalent, using
>>> the % as a prefix to the argument means that the scan only has to confirm
>>> that the value ends in 'test_1' where forgoing the % entirely means that you
>>> are essentially saying some_column='test_1'.
>> Yes, but wouldn't the latter test be more efficient usually since it
>> tests against a prefix - at least with a regular index?
> In theory. But the planner is imperfect, and they will have different
> estimated selectivities which could easily tip the planner into making
> a poor choice for the more selective case. Without seeing the plans,
> it is hard to say much more.
Also keep in mind that not every failure of this sort is the planner's
fault ;-). Particularly with GIN/GiST indexes, quite a lot of the
intelligence (or lack of it) is buried in the index opclass support
functions, where the planner has little visibility and even less say.
In this particular case, a whole lot depends on which set of trigrams
the pg_trgm opclass support functions will choose to search for. The set
that's potentially extractable from the LIKE pattern is well defined, but
not all of them are necessarily equally useful for searching the index.
With a reasonably late-model PG (9.4+), you might well have better luck
with a regular-expression pattern than a LIKE pattern, because more work
has been put into pg_trgm's heuristics for choosing which trigrams to use
for regexes.
(Not sure why it didn't occur to us to make that code apply to LIKE too,
but it didn't.)
regards, tom lane