From: Silvio Moioli <moio@suse.de>
To: pgsql-performance@lists.postgresql.org
Subject: Increasing work_mem slows down query, why?
Date: Mon, 30 Mar 2020 08:47:05 +0200
Message-ID: <79dd683d-3296-1b21-ab4a-28fdc2d98807@suse.de> (raw)
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/rhnPackageC...
Indices here: https://github.com/uyuni-project/uyuni/blob/Uyuni-2020.03/schema/spacewalk/postgres/tables/rhnPackag...
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
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, pgsql-performance@lists.postgresql.org
Subject: Re: Increasing work_mem slows down query, why?
In-Reply-To: <79dd683d-3296-1b21-ab4a-28fdc2d98807@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