Блог

Я работаю над множеством удивительно медленных запросов к моей базе данных Postgres (PostgreSQL 10.9, скомпилированный Visual C build 1800, 64-разрядный, Windows 10). Вот только один пример: очень простое обновление, выполнение которого занимает очень много времени.

 UPDATE
    "Prescription"
SET
    "DiscountList" = TRUE
WHERE
    "PharmacyId" = '1ec0cec5-1cbc-412f-9765-ac0f010de111'
    AND "DiscountList" = FALSE
    AND ("Id" IN (
            SELECT
                discount1_."PrescriptionId"
            FROM
                "Discount" discount1_
            WHERE
                discount1_."PharmacyId" = '1ec0cec5-1cbc-412f-9765-ac0f010de111'
                AND discount1_."DiscountBy" = 'Prescription'));
 

Выполнение этого запроса занимает почти 52 секунды (даже если обновлять нечего)! Вы можете увидеть вывод EXPLAIN (ANALYZE, BUFFERS). Я попытался преобразовать вложенный выбор в объединение, но это только ухудшается (178 секунд). Очевидно, что эта строка выделяется при анализе: Index Scan using "Prescription_pkey" ... (actual time=0.018..0.018 rows=0 loops=2503751) . Это 45 секунд прямо здесь. Почему это занимает так много времени? Есть какие-либо рекомендации по его улучшению?

Вот определения таблиц / индексов:

 CREATE TABLE public."Prescription"
(
    "Id" uuid NOT NULL,
    "RecordVersion" integer NOT NULL DEFAULT 1,
    "RecordCreatedAt" timestamp without time zone,
    "RecordModifiedAt" timestamp without time zone,
    "CriterionType" integer NOT NULL DEFAULT 0,
    "DateCancelled" timestamp without time zone,
    "DateDispensed" timestamp without time zone,
    "DateSold" timestamp without time zone,
    "DiscountList" boolean NOT NULL DEFAULT false,
    "ExcludedReason" integer NOT NULL DEFAULT 0,
    "GroupNumber" character varying(255) COLLATE pg_catalog."default",
    "Insurer" character varying(255) COLLATE pg_catalog."default",
    "NDC" character varying(11) COLLATE pg_catalog."default",
    "PCN" character varying(255) COLLATE pg_catalog."default",
    "ProductName" character varying(255) COLLATE pg_catalog."default",
    "Quantity" numeric(19,4),
    "RxNumber" character varying(255) COLLATE pg_catalog."default",
    "ThirdPartyPaid" numeric(19,4),
    "TotalClaimPrice" numeric(19,4),
    "UPC" character varying(14) COLLATE pg_catalog."default",
    "PharmacyId" uuid,
    CONSTRAINT "Prescription_pkey" PRIMARY KEY ("Id"),
    CONSTRAINT "Prescription_PharmacyId_fkey" FOREIGN KEY ("PharmacyId")
        REFERENCES public."Pharmacy" ("Id") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE
);

CREATE INDEX "INDEX_Prescription_PharmacyIde60cffc4508643c09b6263ec4bdf0987"
    ON public."Prescription" USING btree
    ("PharmacyId")
    TABLESPACE pg_defau<

CREATE TABLE public."Discount"
(
    "Id" uuid NOT NULL,
    "RecordVersion" integer NOT NULL DEFAULT 1,
    "RecordCreatedAt" timestamp without time zone,
    "RecordModifiedAt" timestamp without time zone,
    "DiscountBy" character varying(255) COLLATE pg_catalog."default",
    "DiscountedPrice" numeric(19,4),
    "DiscountReason" integer,
    "PharmacyId" uuid NOT NULL,
    "PrescriptionId" uuid,
    CONSTRAINT "Discount_pkey" PRIMARY KEY ("Id"),
    CONSTRAINT "Discount_PharmacyId_fkey" FOREIGN KEY ("PharmacyId")
        REFERENCES public."Pharmacy" ("Id") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE
    CONSTRAINT "Discount_PrescriptionId_fkey" FOREIGN KEY ("PrescriptionId")
        REFERENCES public."Prescription" ("Id") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE
);

CREATE INDEX "INDEX_Discount_PharmacyIdc94000327c3b434caa4c2807d67e66a0"
    ON public."Discount" USING btree
    ("PharmacyId")
    TABLESPACE pg_defau<
CREATE INDEX "INDEX_Discount_PrescriptionId6cd5c38e038c47c2b3f15e9e8ae59dc7"
    ON public."Discount" USING btree
    ("PrescriptionId")
    TABLESPACE pg_defau<
 

И вот соответствующие параметры конфигурации:

 shared_buffers = 1GB
temp_buffers = 64MB
work_mem = 128MB
maintenance_work_mem = 1GB
seq_page_cost = 1.0
random_page_cost = 1.0
effective_cache_size = 32GB
 

Все таблицы были недавно очищены и проанализированы.

Первый шаг: используйте EXISTS() вместо IN() , и не повторяйте буквальное условие:

 UPDATE "Prescription" dst
SET "DiscountList" = TRUE
WHERE dst."DiscountList" = FALSE
AND "PharmacyId" = '1ec0cec5-1cbc-412f-9765-ac0f010de111'
AND EXISTS (
        SELECT *
        FROM "Discount" ex
        WHERE ex."PrescriptionId" = dst."Id"
        AND ex."PharmacyId" = dst."PharmacyId"
        AND ex."DiscountBy" = 'Prescription'
        );