Anders Steinlein <anders@xxxxxx> writes: > On Sat, Mar 21, 2020 at 3:26 PM Tom Lane <tgl@xxxxxxxxxxxxx> 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
