My_test_db=# and (LOWER(description) LIKE '%abcde%') (Yes, I have nine extra rows from other tests - not a problem.)Įxample query and results: my_test_db=# SELECT "books".* FROM "books" WHERE "books"."published" = 'f' Verify the number of rows: my_test_db=# select count(id) from books I created a million rows contain some random mixed text data: require 'securerandom' In all other cases, an index on (description) is preferable as it supports both case-sensitive and -insensitive predicates.Īccording to my tests ( ten of each query), LOWER LIKE is about 17% faster than iLIKE. With predicates on lower(description) exclusively, the expression index is the slightly better option.
Or with the case-insensitive regexp operator (without % wildcards): description ~* 'abc'Īn index on (description) does not support queries on lower(description) like: lower(description) LIKE '%abc%' When working with said trigram index it's typically more practical to work with: description ILIKE '%abc%' Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL.Similar UTF-8 strings for autocomplete field.PostgreSQL LIKE query performance variations.With a trigram GIN index on description (typically bigger than GiST, but faster for reads), your query would use description ILIKE 'case_insensitive_pattern'. GIN and GiST indexes (using the gin_trgm_ops or gist_trgm_ops operator classes) support LIKE ( ~~), ILIKE ( ~~*), ~, ~* (and some more variants) alike. Then we talk about milliseconds instead of seconds and the difference between the above expressions is nullified. For arbitrary patterns (not only left-anchored) I suggest a trigram index using the additional module pg_trgm. This matters for sequential scans where the expression has to be evaluated for every tested row.īut for big tables like you demonstrate in your answer one would certainly use an index. The bare expression lower(description) LIKE '%abc%' is typically a bit faster than description ILIKE '%abc%', and either is a bit faster than the equivalent regular expression: description ~* 'abc'. The answer depends on many factors like Postgres version, encoding and locale - LC_COLLATE in particular.
0 kommentar(er)
