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

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

> Anders Steinlein <anders(at)e5r(dot)no> writes:
> > 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.

Indeed. But also, how come this is part of the planner time? I would think
that would be part of the execution time? (Just a detail I'm curious about.)

Is lc_ctype the same in both installs?

Yes, lc_ctype is also nb_NO.UTF-8 on both installs.

> For that matter, is the underlying libc the same? We have seen large
> performance discrepancies
> between different libc versions in this area.
>

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.

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.
>

I collected a profile now, but I've never done this before so I'm unsure
how to read the report. I'll email you directly with a link to the
perf.data file, if you would be so kind as to take a quick look. From what
little I think I understand, towlower from libc seems to take up 32% of the
total time, although that by itself doesn't seem to explain almost 2 second
planner time vs. 20ms... Should really citext/libc string comparison
"issues" cause this order of magnitude slower planner time?

Best,
-- a.