Anders Steinlein <anders@e5r.no> writes:
> On Sat, Mar 21, 2020 at 3:26 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Not sure why you'd not have seen the same effect in your 9.4
>> installation, but maybe you had citext installed somewhere else?
> The tables segments and contacts_lists are identical on the two instances,
> i.e. both are using citext (email domain using the citext type) on both 12
> and 9.4, with the citext extension in the public schema. Is it the
> lc_collate setting citext cares about? lc_collate=nb_NO.UTF-8 on both 9.4
> and 12.
I think it depends on both lc_collate and lc_ctype, since basically
what it's doing is lower() on each string and then a strcoll()
comparison. The strcoll() part should be pretty much equivalent to
text comparisons, though ... or, hmm, maybe not. texteq() knows
it can reduce that to just a memcmp bitwise-equality test, but
citext doesn't have that optimization.
> So I don't understand this big difference? Because it does seem like citext
> is indeed the difference.
It seems odd to me too. I'm not at all surprised that citext comparison
is way slower than text, but I am surprised that you don't see that on 9.4
as well. Is lc_ctype the same in both installs? For that matter, is the
underlying libc the same? We have seen large performance discrepancies
between different libc versions in this area.
If you're interested in digging further, getting a "perf" profile while
running the problem query over and over would likely yield some insight
about where the time is going.
https://wiki.postgresql.org/wiki/Profiling_with_perf
regards, tom lane
