From: Silvio Moioli <moio@suse.de>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Subject: Re: Increasing work_mem slows down query, why?
Date: Fri, 3 Apr 2020 23:46:53 +0200
Message-ID: <6619d438-9ebd-11a4-6a1e-24745cda5803@suse.de> (raw)
In-Reply-To: <18262.1585584121@sss.pgh.pa.us>
References: <79dd683d-3296-1b21-ab4a-28fdc2d98807@suse.de>
<CAFj8pRDmvK8hZXLHs9hEEyEjVOWYw0=fUSirBKu5OV5qmiu7jg@mail.gmail.com>
<6d829078-b34a-ca4d-909a-5427aa22ae47@suse.de>
<CAFj8pRADkvF1BwpX7Dv3cYOkcEFAHNNcRX1rM3eqbkGe7rbTbg@mail.gmail.com>
<18262.1585584121@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: moio@suse.de, tgl@sss.pgh.pa.us, pavel.stehule@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Increasing work_mem slows down query, why?
In-Reply-To: <6619d438-9ebd-11a4-6a1e-24745cda5803@suse.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox