In PostgreSQL, weird issue about citext performance?

Question

In PostgreSQL manual it says that citext is simply a module that implements TEXT data type with a call to LOWER():

The citext module provides a case-insensitive character string type, citext. Essentially, it internally calls lower when comparing values. Otherwise, it behaves almost exactly like text.

On the other hand at the end of the documntation it says:

citext is not as efficient as text because the operator functions and the B-tree comparison functions must make copies of the data and convert it to lower case for comparisons. It is, however, slightly more efficient than using lower to get case-insensitive matching.

So I'm confused if it uses LOWER() how can it be "slightly more efficient than using lower"?

Answer 1

It doesn't call the SQL function lower. As the documentation says, it essentially internally calls lower.

The calls happen within the C functions which implement the citext comparison operations. And rather than actually calling lower, they go directly to the underlying str_tolower() routine. You can see this for yourself in the source code, most of which is relatively easy to follow in this case.

So what you're saving, more or less, is the overhead of two SQL function calls per comparison. Which is not insignificant, compared with the cost of the comparison itself, but you'd probably never notice either of them next to the other costs in a typical query.