To: pgsql-performance@lists.postgresql.org
From: Silvio Moioli <moio@suse.de>
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: Increasing work_mem slows down query, why?
Message-ID: <79dd683d-3296-1b21-ab4a-28fdc2d98807@suse.de>
Date: Mon, 30 Mar 2020 08:47:05 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.5.0
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Precedence: bulk

Dear list,

here is a pretty contrived case where increasing work_mem produces a worse plan, with much worse overall query time. I wonder why that is the case.


Problem: INSERTing a thousand new rows in a table which can easily have one million rows. PK is "id", which comes from a table, and we have two columns (called "name" and "version") which do not admit duplicates.

Schema here: https://github.com/uyuni-project/uyuni/blob/Uyuni-2020.03/schema/spacewalk/common/tables/rhnPackageCapability.sql
Indices here: https://github.com/uyuni-project/uyuni/blob/Uyuni-2020.03/schema/spacewalk/postgres/tables/rhnPackageCapability_index.sql


We want one command that returns IDs given (name, version) couples. If they are already in the table, they should be SELECTed, if they are not, they should be INSERTed.

Version is NULLable and NULL should be treated as a value.

We use:

WITH wanted_capability(ordering, name, version) AS (
  VALUES (1, 'first_name', '1.0.0'), (2, 'first_name', '1.0.1'), (1, 'second_name', '1.0.0'), ...998 more...
)
missing_capability AS (
  SELECT wanted_capability.*
    FROM wanted_capability LEFT JOIN rhnPackageCapability
      ON wanted_capability.name = rhnPackageCapability.name
        AND wanted_capability.version IS NOT DISTINCT FROM rhnPackageCapability.version
    WHERE rhnPackageCapability.id IS NULL
),
inserted_capability AS (
  INSERT INTO rhnPackageCapability(id, name, version)
    SELECT nextval('rhn_pkg_capability_id_seq'), name, version FROM missing_capability ON CONFLICT DO NOTHING
    RETURNING id, name, version
)
SELECT wanted_capability.ordering, inserted_capability.id
  FROM wanted_capability JOIN inserted_capability
    ON wanted_capability.name = inserted_capability.name
      AND wanted_capability.version IS NOT DISTINCT FROM inserted_capability.version
    UNION (
      SELECT wanted_capability.ordering, rhnPackageCapability.id
        FROM wanted_capability JOIN rhnPackageCapability
          ON wanted_capability.name = rhnPackageCapability.name
            AND wanted_capability.version IS NOT DISTINCT FROM rhnPackageCapability.version
    )
  ORDER BY ordering
;


Behavior at work_mem = 5 MB is pretty good, query finishes in 200ms. Plan: https://explain.dalibo.com/plan/4u

Behavior at work_mem = 80 MB seems not equally good, query takes more than 13s. Two expensive SORTs and MERGE JOINs are done instead of HASH JOINs. Plan: thttps://explain.dalibo.com/plan/ORd

Adding one more INDEX on rhnCapability.name fixes the issue.

My question is: why are SORTs chosen if more work_mem is available, and why can't the planner predict query will be slower that way?

All of the above is reproducible on openSUSE Leap and PostgreSQL 10.12.

Ideas welcome, and thanks in advance!

Regards,
--
Silvio Moioli
SUSE Manager Development Team