Re: Degression (PG10 > 11, 12 or 13)

On 5/28/21 4:12 PM, Johannes Graën wrote:
> Hi,
>
> When trying to upgrade an existing database from version 10 to 13 I came
> across a degression in some existing code used by clients. Further
> investigations showed that performance measures are similar in versions
> 11 to 13, while in the original database on version 10 it's around 100
> times faster. I could boil it down to perl functions used for sorting.
>
>>From the real data that I don't own, I created a test case that is
> sufficient to observe the degression: http://ix.io/3o7f
>

That function is pretty much just a sequence of ~120 regular
expressions, doing something similar to unaccent(). I wonder if we're
calling the function much more often, perhaps due to some changes in the
sort code (the function is immutable, but that does not guarantee it's
called just once).

It'd be interesting to see profiles from perf, both from 10 and 11.

Also, maybe try materializing the function results before doing the
sort, perhaps like this:

SELECT * FROM (select attr, func(attr) as fattr from tab offset 0) foo
ORDER BY fattr;

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company