LOWER LIKE vs iLIKE

by

The answer depends on many factors like Postgres version, encoding and locale – LC_COLLATE in particular.

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'. This matters for sequential scans where the expression has to be evaluated for every tested row.

But for big tables like you demonstrate in your answer one would certainly use an index. For arbitrary patterns (not only left-anchored) I suggest a trigram index using the additional module pg_trgm. Then we talk about milliseconds instead of seconds and the difference between the above expressions is nullified.

GIN and GiST indexes (using the gin_trgm_ops or gist_trgm_ops operator classes) support LIKE (~~), ILIKE (~~*), ~, ~* (and some more variants) alike. With a trigram GIN index on description (typically bigger than GiST, but faster for reads), your query would use description ILIKE 'case_insensitive_pattern'.

Related:

Basics for pattern matching in Postgres:

When working with said trigram index it’s typically more practical to work with:

description ILIKE '%abc%'

Or with the case-insensitive regexp operator (without % wildcards):

description ~* 'abc'

An index on (description) does not support queries on lower(description) like:

lower(description) LIKE '%abc%'

And vice versa.

With predicates on lower(description) exclusively, the expression index is the slightly better option.

In all other cases, an index on (description) is preferable as it supports both case-sensitive and -insensitive predicates.

More Related Contents:

  1. postgresql COUNT(DISTINCT …) very slow
  2. Speed up the loop operation in R
  3. One could use a profiler, but why not just halt the program? [closed]
  4. Spark functions vs UDF performance?
  5. Adding a redundant assignment speeds up code when compiled without optimization
  6. Why is this SSE code 6 times slower without VZEROUPPER on Skylake?
  7. How many Include I can use on ObjectSet in EntityFramework to retain performance?
  8. Faster way to find the first empty row in a Google Sheet column
  9. 32-byte aligned routine does not fit the uops cache
  10. Why is SSE scalar sqrt(x) slower than rsqrt(x) * x?
  11. Haskell foldl’ poor performance with (++)
  12. Which Intel microarchitecture introduced the ADC reg,0 single-uop special case?
  13. Storing Documents as Blobs in a Database – Any disadvantages?
  14. Can modern x86 implementations store-forward from more than one prior store?
  15. timing code in matlab
  16. Minimizing overhead due to the large number of Numpy dot calls
  17. Simulating Slow Internet Connection [closed]
  18. What is the difference between spark.sql.shuffle.partitions and spark.default.parallelism?
  19. What’s the actual effect of successful unaligned accesses on x86?
  20. How to speed up Compile Time of my CMake enabled C++ Project?
  21. Assembly – How to score a CPU instruction by latency and throughput
  22. Getting total row count from OFFSET / FETCH NEXT
  23. Content-Length header versus chunked encoding
  24. Does Scala support tail recursion optimization?
  25. MongoDB ‘count()’ is very slow. How do we refine/work around with it?
  26. Move all odd positioned element to left half and even positioned to right half in-place
  27. Client-side logic OR Server-side logic?
  28. Trouble converting long list of data.frames (~1 million) to single data.frame using do.call and ldply
  29. Projecting a list of lists efficiently in F#
  30. MongoDB’s performance on aggregation queries

Leave a Comment