Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jKU9c-0006JY-6l for pgsql-performance@arkaria.postgresql.org; Fri, 03 Apr 2020 21:47:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jKU9a-0005sB-81 for pgsql-performance@arkaria.postgresql.org; Fri, 03 Apr 2020 21:47:06 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jKU9a-0005s3-0e for pgsql-performance@lists.postgresql.org; Fri, 03 Apr 2020 21:47:06 +0000 Received: from mx2.suse.de ([195.135.220.15]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jKU9T-000775-GC for pgsql-performance@lists.postgresql.org; Fri, 03 Apr 2020 21:47:04 +0000 X-Virus-Scanned: by amavisd-new at test-mx.suse.de Received: from relay2.suse.de (unknown [195.135.220.254]) by mx2.suse.de (Postfix) with ESMTP id 7DF00AC90; Fri, 3 Apr 2020 21:46:55 +0000 (UTC) To: Tom Lane , Pavel Stehule Cc: Pgsql Performance References: <79dd683d-3296-1b21-ab4a-28fdc2d98807@suse.de> <6d829078-b34a-ca4d-909a-5427aa22ae47@suse.de> <18262.1585584121@sss.pgh.pa.us> From: Silvio Moioli Autocrypt: addr=moio@suse.de; prefer-encrypt=mutual; keydata= xsBNBFF2vOcBCACrpk6T6K+sY+Hqyt+F4Rvi84xWxcHJl4YEP4fUHZy0hx/NKFVfJ7SlvYR3 I5mfXPpwKl5TE8KBLMB5rgNoxF6UfEsZKv3OuL2128f/+0htintp3lrGzZM7bYCIaaym4erj II7upgDDTG9t74pDxfW/ZIX66YI4kbWafbj62sdgYdyTO3MpEnh+xR5w3v7GETYDon3MxFZD HC57qs+U8uwlDzxcnb96Hwo9VbgOnDAVZfeVoqLCPsjdUJYOSn+k+z5wE8jyp+jJIGfABPjw hqVCgLkxo/OGdSUzjjCQi4PwNSRUto/L3jPXWQRQOWCf+hQQDoWTDE4NtSCDOemZgWKhABEB AAHNJlNpbHZpbyBNb2lvbGkgKFdvcmspIDxzbW9pb2xpQHN1c2UuZGU+wsCtBBMBAgBAAhsj BwsJCAcDAgEGFQgCCQoLBBYCAwECHgECF4AWIQT0U7wp3eTEYpM+ITXVOvaPwiUFjQUCXZ7x 8QUJDgloigAhCRDVOvaPwiUFjRYhBPRTvCnd5MRikz4hNdU69o/CJQWNdl4H/0YNc95q6CIR dPODjlD/iZrLZ4hB3B9poRYKfUUSNKsZQy0xzryCpz4DeqbsqUSJZXOE76mI8Ji8bdLT/3+V KVqMwjb5mN8H1oH2e3+oEKvIoVGXZ5sO1Qm/QIAY/Zhs89WgMrEvJxpjK8arahhDN6rOhukJ hCF2yWjTaiTlZz8+lxM4Lg67mcn3tlCfDphnumpXoQ22gozgNspkqSc7dcu41BCIferQEUuJ 8PkF1seAI41oe3FLxPTBWkZx9i6+ydnMcmsy8uPDry7FLy6i4JmUGJk5KbU/QVhNscS2ogmP gfQhdfA90S3SJGLyecFbG4M+Yc2J25Pgq6Eahplb7DXOwE0EUXa85wEIAMbEFquPKImyvit/ tH+nGeTPaBAHQtur/qgX9eMqd/fxFhMqm1SKI3Vc3L0AcgSk6td1EX0kCmq3RPS3YfuPB/nT QFRWcek4y7ZnfrvsnLCjhZubiHSA//luO7WKcDfyP6j/AX+PCMz9uH5lzcuzqwc/tzf4jvUa QtKkciUMHK23NJPSmg7bYkAO6TEQ9+yeVmUaSIWRrj9V67ZVyO6kcXVKQ5j7IWeZzyp+WBql w9wKXXoU2SOo8MOp8Pil7PqojBn+r+7dQKND6VVQ7ze7zgHPeexO4tIA3GZxZhLWsjDoXLJR 3ZlVCZEf8ycempMVTQ9tMlPdziqyfGVlfrcLD3UAEQEAAcLAZQQYAQIADwUCUXa85wIbDAUJ A8JnAAAKCRDVOvaPwiUFjc6MB/94L/UJZ/uqTIZXcorYY55zb8eoQ07t3sPfobkqGS613thx /Qss9eRdPKpmdwLR6MjxQy3K3CYFrvifaFseBo5jmlw2Gk/K1X0it5fVrIHXdlQrXKTFwS3E 4eUpHN3FEpI4uceY1qnHIDgjHanLWXhXw/cUfo+NdwWbAcw3zT/OOEBPZWYixEvCEoM0faxu 1yCJUUHiWI5+JxL9e9nR8NMVjAcEcBKVm7e/9owhHchqRPKPdWsThkfQkFIlq/Sog6YPK2NF Cf7qsT4OLlJZKY/M9Tmg+sncdtbT14vQGebnuVs+nD83VfgHfIk+Lw9tPzWR2IPWkGYvcUft AQf6GNJ2wsCTBBgBAgAmAhsMFiEE9FO8Kd3kxGKTPiE11Tr2j8IlBY0FAl2e8fEFCQ4JaIoA IQkQ1Tr2j8IlBY0WIQT0U7wp3eTEYpM+ITXVOvaPwiUFjdQpCACcPlv0Wx2QwSfS5tmQ3Jdn 3kFj81/A6wY3Ucv3Ma34hV+9aSt8rF049RmULRk97+eHdR8z0KDQX2oNfs5xqjpXflnDqFrY 0bpwL4gOEoYmPhuIcHKzy0yRlph1gsaA9ZWXXv/KuMV6iZXJJurkOKzwvPmeF+3uEVEg8bpF dBkRW2S+LgPLPLXH55Fr/M+uqHYZds6palJyNPWlTosIyzQvPV8VbWErY/BcfhrPD7JlB+cE 1wRsyaeL1Tx+nGzIlghuo2WT/IhG9ZorCQhACGwu6I1Pqz9UX70heh2bzkEcppn9dStiL9Wf SajWt2ieJgrMPClGnG2bbDLHhxNO57Sa Subject: Re: Increasing work_mem slows down query, why? Message-ID: <6619d438-9ebd-11a4-6a1e-24745cda5803@suse.de> Date: Fri, 3 Apr 2020 23:46:53 +0200 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.5.0 MIME-Version: 1.0 In-Reply-To: <18262.1585584121@sss.pgh.pa.us> Content-Type: text/plain; charset=utf-8 Content-Language: en-US Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk 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