From: Tom Lane <tgl@sss.pgh.pa.us>
To: Anders Steinlein <anders@e5r.no>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Slow planning time when public schema included (12 vs. 9.4)
Date: Sat, 21 Mar 2020 15:35:51 -0400
Message-ID: <4541.1584819351@sss.pgh.pa.us> (raw)
In-Reply-To: <CAC35HN=mieW9zXbJAaYkK3_n8meqgA+gtM71q57SFzy+wrEa7A@mail.gmail.com>
References: <CAC35HN=tS6zDy0FcQ-mPjsZ9eO_37fe9m3mLDQ+zWZWrqEB9rw@mail.gmail.com>
<2804.1584800777@sss.pgh.pa.us>
<CAC35HN=mieW9zXbJAaYkK3_n8meqgA+gtM71q57SFzy+wrEa7A@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, anders@e5r.no, pgsql-performance@lists.postgresql.org
Subject: Re: Slow planning time when public schema included (12 vs. 9.4)
In-Reply-To: <4541.1584819351@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox