On Sat, Mar 21, 2020 at 11:55 PM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Anders Steinlein <anders@xxxxxx> 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.
