Jeff Janes <jeff.janes@xxxxxxxxx> writes: > On Thu, May 12, 2016 at 8:13 AM, Robert Klemme > <shortcutter@xxxxxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
