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

Поиск
Список
Период
Сортировка
От Anders Steinlein
Тема Re: Slow planning time when public schema included (12 vs. 9.4)
Дата
Msg-id CAC35HN=PjoxZQH3eUbS6ngf=FS--boSqZJ_sbAD53Qc_tjQt=g@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Slow planning time when public schema included (12 vs. 9.4)  (Tom Lane <tgl@sss.pgh.pa.us>)
Ответы Re: Slow planning time when public schema included (12 vs. 9.4)  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-performance
Дерево обсуждения
On Sat, Mar 21, 2020 at 8:35 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Anders Steinlein <anders@e5r.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.

В списке pgsql-performance по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Slow planning time when public schema included (12 vs. 9.4)
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Slow planning time when public schema included (12 vs. 9.4)