Как повысить производительность функции с помощью курсоров в PostgreSQL?

У меня есть функция с двумя вложенными курсорами. Внешний курсор получает платежные реквизиты клиента из источника и вставляет в цель на основе некоторой бизнес-логики. Внутренний курсор принимает данные оплаты каждого платежа, он происходит один за другим.

Таблица платежей содержит около 125000 строк и около 335000 строк для деталей платежа. Все эти строки должны быть перенесены в целевую таблицу. Выполнение функции занимает более двух часов, а загрузка ЦП базы данных достигает 99%. Я работаю с PostgreSQL 9.2.

Как повысить производительность функции?

Код, который я использую:

CREATE OR REPLACE FUNCTION ccdb_dummy.o_payments1(a integer)
 RETURNS void AS
$BODY$
DECLARE
cursor_1 refcursor;
cursor_2 refcursor;
rowcount integer;
rowcount1 integer;
payment_no bigint;
query_1 character varying(250);
lc_cin_num bigint;
test character varying(50);
t_payments ccdb_stg.o_payments_stg%ROWTYPE;
t_payments_details ccdb_stg.o_payment_head_dtls_stg%ROWTYPE;
BEGIN
rowcount := 0;
open cursor_1 for select * from ccdb_stg.o_payments_stg WHERE section_code = a;
select count(1) into rowcount from ccdb_stg.o_payments_stg WHERE section_code = a;
for i IN 1..rowcount loop
fetch cursor_1 into t_payments;
payment_no= nextval('ccdb_stg.payments_seq');
select cin into lc_cin_num from ccdb_dummy.consumers a where a.consumer_num = t_payments.consumer_num;
insert into ccdb_dummy.payments(payment_id,**********,source_system_flag,cin,consumer_nbr,cust_connection_id,cust_type_flg,***************,mop_code,mop_details,coll_effect_date,coll_entry_date,***********,***********,***************,************,cancel_flag,acc_type_id,cust_section_code,coll_section_code,remarks,pm_paydate,pm_amount,ref_transaction_id,creation_dt,created_by) values(payment_no,t_payments.**********,t_payments.origin_flag,lc_cin_num,t_payments.consumer_num,t_payments.cust_connection_id,t_payments.cust_type_flag,t_payments.***************,t_payments.mop_id,t_payments.mop_details,t_payments.coll_effect_date,t_payments.coll_entry_date,t_payments.***********,t_payments.***********,t_payments.************,t_payments.************,t_payments.cancel_flag,t_payments.acc_type_flag,t_payments.cust_section_code,t_payments.coll_section_code,t_payments.remarks,t_payments.pm_paydate,t_payments.pm_amount,null,now(),'system');
select count(1) into rowcount1 from ccdb_stg.o_payment_head_dtls_stg a where a.mbc_********** = t_payments.**********;
open cursor_2 for select * from ccdb_stg.o_payment_head_dtls_stg a where a.mbc_********** = t_payments.**********;
for i IN 1..rowcount1 loop
fetch cursor_2 into t_payments_details;
insert into ccdb_dummy.payment_head_dtls(payment_id,mbc_**********,charge_head_code,amount,tariff_id,creation_dt,created_by)
values (payment_no,t_payments_details.mbc_**********,t_payments_details.charge_head_code,t_payments_details.amount,t_payments_details.tariff_id,now(),'system');
end loop;
close cursor_2;
end loop;
close cursor_1;
END;
$BODY$
 LANGUAGE plpgsql;
1 ответ

На первом этапе я радикально упростил ваш процедурный код:

CREATE OR REPLACE FUNCTION ccdb_dummy.o_payments1(a integer)
 RETURNS void AS
$func$
DECLARE
 t record;
 t1 record;
BEGIN
FOR t IN
 SELECT *
 ,nextval('ccdb_stg.payments_seq') AS payment_no
 ,c.cin
 FROM ccdb_stg.o_payments_stg p
 LEFT JOIN ccdb_dummy.consumers c USING (consumer_num)
 WHERE p.section_code = $1
LOOP
 INSERT INTO ccdb_dummy.payments(payment_id,**********,source_system_flag,cin, ... ,pm_amount,ref_transaction_id,creation_dt,created_by)
 VALUES(t.payment_no,t.**********,t.origin_flag,t.cin, ... ,t.pm_amount,null,now(),'system');
 FOR t1 IN
 SELECT *
 FROM ccdb_stg.o_payment_head_dtls_stg h
 WHERE h.mbc_********** = t.**********
 LOOP
 INSERT INTO ccdb_dummy.payment_head_dtls(payment_id,mbc_**********,charge_head_code,amount,tariff_id,creation_dt,created_by)
 VALUES (t.payment_no,t1.mbc_**********,t1.charge_head_code,t1.amount,t1.tariff_id,now(),'system');
 END LOOP;
END LOOP;
END
$func$ LANGUAGE plpgsql;
  • Используйте неявный курсор FOR LOOP вместо громоздких явных курсоров в сочетании с избыточным числом и циклами. Гораздо проще и быстрее. Прочтите главу "Цитирование по результатам запроса" в руководстве.

  • LEFT JOIN до ccdb_dummy.consumers в первом SELECT вместо запуска отдельного выбора для каждой строки.

  • Также включите nextval('ccdb_stg.payments_seq') AS payment_no в первый SELECT. дешевле, чем множество отдельных запросов.

  • Незначительная деталь: оператор присваивания в plpgsql равен :=, а не =. Подробности здесь.

Но это далеко не идеальное, все же. Рассмотрим совершенно новый подход с операциями на основе набора вместо отдельных вставок в циклах. Гораздо чище и быстрее. Это то, как работают лучшие РСУБД.

Один оператор SQL с модифицирующим данные CTE

Включение функции SQL для замены. Редактирование данных CTE требует Postgres 9.1 или более поздней версии.

CREATE OR REPLACE FUNCTION ccdb_dummy.o_payments2(integer)
 RETURNS void AS
$func$
WITH ins1 AS (
 INSERT INTO ccdb_dummy.payments(
 payment_id, cin, **********, ... , pm_amount, ref_transaction_id,creation_dt,created_by) 
 SELECT nextval('ccdb_stg.payments_seq'),c.cin,p.**********, ... , p.pm_amount, null, now(), 'system'
 FROM ccdb_stg.o_payments_stg p
 LEFT JOIN ccdb_dummy.consumers c USING (consumer_num)
 WHERE p.section_code = $1
 RETURNING payment_id, **********
 )
INSERT INTO ccdb_dummy.payment_head_dtls(
 payment_id, mbc_**********, charge_head_code, amount, tariff_id,creation_dt,created_by)
SELECT i.payment_id,h.mbc_**********,h.charge_head_code,h.amount,h.tariff_id,now(), 'system'
FROM ins1 i
JOIN ccdb_stg.o_payment_head_dtls_stg h ON h.mbc_********** = i.**********;
$func$ LANGUAGE sql;

Точно так же, как и выше, функция plpgsql (запрет на перевод в переводе). Просто намного проще и быстрее.

Найдите больше примеров для INSERT, использующих модифицирующие данные CTE здесь, на SO.

licensed under cc by-sa 3.0 with attribution.