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
