Re: LIKE pattern

On Thu, May 12, 2016 at 8:13 AM, Robert Klemme
<shortcutter(at)googlemail(dot)com> wrote:
> On Mon, May 9, 2016 at 11:41 PM, SoDupuDupu wrote:
>> Владимир-3 wrote
>>> It seems my quite complex query runs 10 times faster on "some_column
>>> LIKE '%test_1' " vs "some_column LIKE 'test_1' "
>>> So I just add "%" to the pattern...
>>
>> 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.

Cheers,

Jeff