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

Anders Steinlein <anders(at)e5r(dot)no> writes:
> On Sat, Mar 21, 2020 at 8:35 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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