[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    Re: Slow planning time when public schema included (12 vs. 9.4)
From:       Anders Steinlein <anders () e5r ! no>
Date:       2020-03-24 22:55:29
Message-ID: CAC35HNmJxvfRzxDWMdnfT4vXjsyOKhMbn+vqYpv_byYjWQyH1w () mail ! gmail ! com
[Download RAW message or body]

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

> Anders Steinlein <anders@e5r.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.

[Attachment #3 (text/html)]

<div dir="ltr"><div dir="ltr">On Sat, Mar 21, 2020 at 11:55 PM Tom Lane &lt;<a \
href="mailto:tgl@sss.pgh.pa.us" target="_blank">tgl@sss.pgh.pa.us</a>&gt; \
wrote:<br></div><div dir="ltr"><div class="gmail_quote"><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex">Anders Steinlein &lt;<a \
href="mailto:anders@e5r.no" target="_blank">anders@e5r.no</a>&gt; writes:<br> &gt; \
This they most definitely are not. 9.4 was running on an old box, Ubuntu<br> &gt; \
12.04, while 12 is on an up-to-date Ubuntu 18.04 LTS. AFAICS, 2.15 on the<br> &gt; \
9.4 box and 2.27 on the 12 box.<br> <br>
I&#39;m suspicious that the root issue has to do with libc differences,<br>
but I haven&#39;t any hard data to back that up with.<br>
<br>
Another possibility perhaps is that v12&#39;s ANALYZE is collecting a lot<br>
more &quot;common&quot; values than 9.4 did.   Whether it is or not, the advice<br>
you already got to ratchet down the stats target would likely be<br>
helpful to reduce the planning time.<br></blockquote><div><br></div><div>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.</div><div><br></div><div>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 &quot;fixed&quot; this, I&#39;m \
concerned joins and sorting is slower in general after having uncovered this (we \
haven&#39;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?</div><div><br></div><div>Thanks again,</div><div>-- a.</div></div></div> \
</div>



[prev in list] [next in list] [prev in thread] [next in thread]