Thread: LIKE pattern
Hello. 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... Both query plans use same indexes.
Владимир-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'. -- View this message in context: http://postgresql.nabble.com/LIKE-pattern-tp5902225p5902701.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
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?
Kind regards
robert
--
[guy, jim, charlie].each {|him| remember.him do |as, often| as.you_can
- without end}
http://blog.rubybestpractices.com/
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: >> Владимир-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
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