Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jIuAK-00079A-GN for pgsql-performance@arkaria.postgresql.org; Mon, 30 Mar 2020 13:09:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jIuAI-0004O1-NS for pgsql-performance@arkaria.postgresql.org; Mon, 30 Mar 2020 13:09:18 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jIuAI-0004Nu-F3 for pgsql-performance@lists.postgresql.org; Mon, 30 Mar 2020 13:09:18 +0000 Received: from mx2.suse.de ([195.135.220.15]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jIuAG-00063y-0X for pgsql-performance@lists.postgresql.org; Mon, 30 Mar 2020 13:09:18 +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 8D392AD32 for ; Mon, 30 Mar 2020 13:09:14 +0000 (UTC) To: pgsql-performance@lists.postgresql.org References: <79dd683d-3296-1b21-ab4a-28fdc2d98807@suse.de> <6d829078-b34a-ca4d-909a-5427aa22ae47@suse.de> 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: Date: Mon, 30 Mar 2020 15:09:12 +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: 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 12:12 PM, Pavel Stehule wrote: > Do you have some planner variables changed - like seq_page_cost? That one was not changed but another one is - cpu_tuple_cost (to 0.5). Indeed bringing it back to its default does improve the query time significantly: QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Unique (cost=36735.61..36735.63 rows=2 width=36) (actual time=357.825..358.036 rows=1100 loops=1) Output: wanted_capability.ordering, inserted_capability.id Buffers: shared hit=14443 CTE wanted_capability -> Values Scan on "*VALUES*" (cost=0.00..13.75 rows=1100 width=68) (actual time=0.001..0.355 rows=1100 loops=1) Output: "*VALUES*".column1, "*VALUES*".column2, "*VALUES*".column3 CTE missing_capability -> Hash Left Join (cost=18263.69..18347.78 rows=1 width=68) (actual time=183.826..183.826 rows=0 loops=1) Output: wanted_capability_2.ordering, wanted_capability_2.name, wanted_capability_2.version Hash Cond: (wanted_capability_2.name = (rhnpackagecapability_1.name)::text) Join Filter: (NOT (wanted_capability_2.version IS DISTINCT FROM (rhnpackagecapability_1.version)::text)) Filter: (rhnpackagecapability_1.id IS NULL) Rows Removed by Filter: 1100 Buffers: shared hit=7217 -> CTE Scan on wanted_capability wanted_capability_2 (cost=0.00..22.00 rows=1100 width=68) (actual time=0.000..0.729 rows=1100 loops=1) Output: wanted_capability_2.ordering, wanted_capability_2.name, wanted_capability_2.version -> Hash (cost=12126.64..12126.64 rows=490964 width=79) (actual time=181.477..181.477 rows=490964 loops=1) Output: rhnpackagecapability_1.name, rhnpackagecapability_1.version, rhnpackagecapability_1.id Buckets: 524288 Batches: 1 Memory Usage: 53907kB Buffers: shared hit=7217 -> Seq Scan on public.rhnpackagecapability rhnpackagecapability_1 (cost=0.00..12126.64 rows=490964 width=79) (actual time=0.009..57.663 rows=490964 loops=1) Output: rhnpackagecapability_1.name, rhnpackagecapability_1.version, rhnpackagecapability_1.id Buffers: shared hit=7217 CTE inserted_capability -> Insert on public.rhnpackagecapability rhnpackagecapability_2 (cost=0.00..0.04 rows=1 width=1080) (actual time=183.828..183.828 rows=0 loops=1) Output: rhnpackagecapability_2.id, rhnpackagecapability_2.name, rhnpackagecapability_2.version Conflict Resolution: NOTHING Tuples Inserted: 0 Conflicting Tuples: 0 Buffers: shared hit=7217 -> Subquery Scan on "*SELECT*" (cost=0.00..0.04 rows=1 width=1080) (actual time=183.827..183.827 rows=0 loops=1) Output: "*SELECT*".nextval, "*SELECT*".name, "*SELECT*".version, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP Buffers: shared hit=7217 -> CTE Scan on missing_capability (cost=0.00..0.02 rows=1 width=72) (actual time=183.827..183.827 rows=0 loops=1) Output: nextval('rhn_pkg_capability_id_seq'::regclass), missing_capability.name, missing_capability.version Buffers: shared hit=7217 -> Sort (cost=18374.04..18374.04 rows=2 width=36) (actual time=357.825..357.862 rows=1100 loops=1) Output: wanted_capability.ordering, inserted_capability.id Sort Key: wanted_capability.ordering, inserted_capability.id Sort Method: quicksort Memory: 100kB Buffers: shared hit=14443 -> Append (cost=0.03..18374.03 rows=2 width=36) (actual time=357.071..357.660 rows=1100 loops=1) Buffers: shared hit=14437 -> Hash Join (cost=0.03..26.23 rows=1 width=36) (actual time=183.847..183.847 rows=0 loops=1) Output: wanted_capability.ordering, inserted_capability.id Hash Cond: (wanted_capability.name = (inserted_capability.name)::text) Join Filter: (NOT (wanted_capability.version IS DISTINCT FROM (inserted_capability.version)::text)) Buffers: shared hit=7220 -> CTE Scan on wanted_capability (cost=0.00..22.00 rows=1100 width=68) (actual time=0.002..0.002 rows=1 loops=1) Output: wanted_capability.ordering, wanted_capability.name, wanted_capability.version -> Hash (cost=0.02..0.02 rows=1 width=1064) (actual time=183.829..183.829 rows=0 loops=1) Output: inserted_capability.id, inserted_capability.name, inserted_capability.version Buckets: 1024 Batches: 1 Memory Usage: 8kB Buffers: shared hit=7217 -> CTE Scan on inserted_capability (cost=0.00..0.02 rows=1 width=1064) (actual time=183.828..183.828 rows=0 loops=1) Output: inserted_capability.id, inserted_capability.name, inserted_capability.version Buffers: shared hit=7217 -> Hash Join (cost=18263.69..18347.78 rows=1 width=10) (actual time=173.223..173.750 rows=1100 loops=1) Output: wanted_capability_1.ordering, rhnpackagecapability.id Hash Cond: (wanted_capability_1.name = (rhnpackagecapability.name)::text) Join Filter: (NOT (wanted_capability_1.version IS DISTINCT FROM (rhnpackagecapability.version)::text)) Buffers: shared hit=7217 -> CTE Scan on wanted_capability wanted_capability_1 (cost=0.00..22.00 rows=1100 width=68) (actual time=0.000..0.070 rows=1100 loops=1) Output: wanted_capability_1.ordering, wanted_capability_1.name, wanted_capability_1.version -> Hash (cost=12126.64..12126.64 rows=490964 width=79) (actual time=172.220..172.220 rows=490964 loops=1) Output: rhnpackagecapability.id, rhnpackagecapability.name, rhnpackagecapability.version Buckets: 524288 Batches: 1 Memory Usage: 53922kB Buffers: shared hit=7217 -> Seq Scan on public.rhnpackagecapability (cost=0.00..12126.64 rows=490964 width=79) (actual time=0.008..52.573 rows=490964 loops=1) Output: rhnpackagecapability.id, rhnpackagecapability.name, rhnpackagecapability.version Buffers: shared hit=7217 Planning time: 2.145 ms Execution time: 358.773 ms Is that an unreasonable value? For the sake of this discussison, I am targeting fairly average bare-metal SSD-backed servers with recent CPUs (let's say 3 year old maximum), with ample available RAM. Thanks! Regards, -- Silvio Moioli SUSE Manager Development Team