Re: Increasing work_mem slows down query, why?

On 3/30/20 6:02 PM, Tom Lane wrote:
> Yeah, the run time of the slow query seems to be almost entirely expended
> in these two sort steps, while the planner doesn't think that they'll be
> very expensive. Tweaking unrelated cost settings to work around that is
> not going to be helpful. What you'd be better off trying to do is fix
> the slow sorting. Is rhnpackagecapability.name some peculiar datatype?
> If it's just relatively short text strings, as one would guess from the
> column name, then what you must be looking at is really slow locale-based
> sorting. What's the database's LC_COLLATE setting? Can you get away
> with switching it to C?

LC_COLLATE is en_US.UTF-8, and I cannot really change that for the whole database. I could, in principle, use the "C" collation for this particular column, I tried that and it helps (time goes down from ~13s to ~500ms).

Nevertheless, adding an explicit new index on the column (CREATE INDEX rhn_pkg_cap_name ON rhnPackageCapability (name)) helps even more, with the query time going down to ~60ms, no matter work_mem.

So ultimately I think I am going to remove the custom cpu_tuple_cost parameter and add the index, unless you have different suggestions.

Thank you very much so far!

Regards,
--
Silvio Moioli
SUSE Manager Development Team