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 18:55:44 -0400
Message-ID: <24806.1584831344@sss.pgh.pa.us> (raw)
In-Reply-To: <CAC35HN=PjoxZQH3eUbS6ngf=FS--boSqZJ_sbAD53Qc_tjQt=g@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>
<4541.1584819351@sss.pgh.pa.us>
<CAC35HN=PjoxZQH3eUbS6ngf=FS--boSqZJ_sbAD53Qc_tjQt=g@mail.gmail.com>
Anders Steinlein <anders@e5r.no> writes:
> On Sat, Mar 21, 2020 at 8:35 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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.)
As part of estimating the size of a join, the planner will run through all
the most-common-values available from pg_stats and see which values from
one table match to which values from the other. If you have a lot of MCVs
(which'd involve a fairly flat, but not unique, data distribution and a
large stats target setting) and a slow join operator, it's not hard for
that to take a lot of time. You might care to look into pg_stats and see
just how big those arrays are for each of these columns.
But 9.4 did that too, so we're still at a loss as to why v12 is so much
slower.
> 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.
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: <24806.1584831344@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