Re: Slow planning time when public schema included (12 vs. 9.4)

On Sat, Mar 21, 2020 at 11:55 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Anders Steinlein <anders(at)e5r(dot)no> writes:
> > This they most definitely are not. 9.4 was running on an old box, Ubuntu
> > 12.04, while 12 is on an up-to-date Ubuntu 18.04 LTS. AFAICS, 2.15 on the
> > 9.4 box and 2.27 on the 12 box.
>
> I'm suspicious that the root issue has to do with libc differences,
> but I haven't any hard data to back that up with.
>
> Another possibility perhaps is that v12's ANALYZE is collecting a lot
> more "common" values than 9.4 did. Whether it is or not, the advice
> you already got to ratchet down the stats target would likely be
> helpful to reduce the planning time.
>

Yes, indeed, lowering the statistics target to the default 100 decreased
the planning time a lot -- to sub-10m! Thanks for the guidance, although
the excessive difference between the two boxes/libc versions are
disappointing, to say the least.

Do you have any insight into how the Postgres 12 nondeterministic collation
feature (with ICU) compares performance-wise in general? Although having a
much lower statistics target "fixed" this, I'm concerned joins and sorting
is slower in general after having uncovered this (we haven't dug into that
performance numbers yet), since email (citext) are PKs in a lot of our
tables. Would changing our email domain using citext to instead be a domain
over text using a case-insensitive collation be a better choice?

Thanks again,
-- a.