postgresql - 使用 CTE 调试性能问题

Question

我们的应用程序中有一个用例，我们首先将一个 csv 文件复制到一个staging 表 - 然后将经过验证的数据插入第二个表 participants。新创建的参与者 ID(主键)随后会在 staging 表中更新以供进一步处理。

在我们的应用程序中，我们经常遇到性能问题。有时此过程可在 15-20 秒内处理 100 000 行。有时它永远不会在正常时间内完成(pg_cancel_backend 来拯救)。

当我尝试创建一个非常有值(value)的最小测试用例时，我无法重现问题:/。因此，这是尝试获得一些建议，了解如何进一步调试或重写底层查询。

• 带有 Doctrine DBAL 的 PHP 应用
• Postgres 10.5

我们用 CTE 来做这件事——基本上是这样的:

WITH inserted_participants AS (
    INSERT INTO participants (email, project_id, survey_token, participant_uname)
    SELECT
        staging.email,
        1,
        staging.generated_token,
        staging.email -- is used as uname
    FROM
        staging
    RETURNING
        participants.participant_id,
        participants.participant_uname
) -- Update existing staging data with newly created participant_id
UPDATE
    staging  AS stage_update
SET
    resulting_participant_id = inserted_participants.participant_id
FROM
    inserted_participants
WHERE stage_update.email = inserted_participants.participant_uname;

再次声明:我无法重现此测试用例的性能问题。我怀疑它与 CTE 有关。

这是否可以在不使用 CTE 的情况下重写，并且仍然可以安全地返回新创建的行并在暂存表中更新这些行？

这是最小测试用例的表结构:

CREATE EXTENSION IF NOT EXISTS citext;

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

DROP TABLE IF EXISTS public.staging;

CREATE TABLE public.staging
(
    staging_id serial,
    email citext COLLATE pg_catalog."default",
    generated_token character varying(255) COLLATE pg_catalog."default",
    resulting_participant_id integer,
    CONSTRAINT staging_pkey PRIMARY KEY (staging_id),
    CONSTRAINT unique_generated_token UNIQUE (generated_token)
);

CREATE INDEX ON public.staging (email);
CREATE INDEX ON public.staging (generated_token);

DROP TABLE IF EXISTS public.participants;

CREATE TABLE public.participants
(
    participant_id serial,
    email citext COLLATE pg_catalog."default" NOT NULL,
    project_id integer NOT NULL,
    survey_token character varying(255) COLLATE pg_catalog."default" NOT NULL,
    participant_uname citext COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT participants_pkey PRIMARY KEY (participant_id),
    CONSTRAINT participants_participant_uname_project_id_key UNIQUE (participant_uname, project_id),
    CONSTRAINT participants_project_id_email_key UNIQUE (project_id, email),
    CONSTRAINT participants_project_id_participant_uname_key UNIQUE (project_id, participant_uname),
    CONSTRAINT participants_survey_token_key UNIQUE (survey_token)
);

CREATE INDEX ON public.participants (participant_uname);
CREATE INDEX ON public.participants (project_id);

以及我使用的虚拟数据:

INSERT INTO 
    staging (staging_id, email, generated_token)
SELECT 
    generate_series(1,100000),
    gen_random_uuid()::citext,
    gen_random_uuid()::TEXT;

Answer 1

你应该首先确定你是否被锁住了。 pg_locks 是否包含具有长时间运行的后端进程 ID 和 granted = FALSE 的行？

如果不是这样，找到瓶颈。后端进程是否使 CPU 饱和？您的 I/O 子系统是否一直很忙？

您还应该使用 EXPLAIN 来检查执行计划。有什么可疑的东西可以解释持续时间吗？

在完成查询的较小数据集上测试查询会非常有帮助。这将使您能够运行 EXPLAIN (ANALYZE, BUFFERS)，这是调试查询的最佳起点。不过，请先检查您是否有相同的执行计划。

索引可以大大减慢数据修改的速度。你有很多吗？通常，在批量更新之前删除所有索引和约束并在之后重新创建它们是最快的。