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

Question

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

Таблица платежей имеет около 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,receipt_id,source_system_flag,cin,consumer_nbr,cust_connection_id,cust_type_flg,receipt_type_id,mop_code,mop_details,coll_effect_date,coll_entry_date,receipt_num,receipt_amt,receipt_loc_flg,receipt_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.receipt_id,t_payments.origin_flag,lc_cin_num,t_payments.consumer_num,t_payments.cust_connection_id,t_payments.cust_type_flag,t_payments.receipt_type_id,t_payments.mop_id,t_payments.mop_details,t_payments.coll_effect_date,t_payments.coll_entry_date,t_payments.receipt_num,t_payments.receipt_amt,t_payments.receipt_flag,t_payments.receipt_date,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_receipt_id = t_payments.receipt_id;

open cursor_2 for select * from ccdb_stg.o_payment_head_dtls_stg a where a.mbc_receipt_id = t_payments.receipt_id;

for i IN 1..rowcount1 loop

fetch cursor_2 into t_payments_details;

insert into ccdb_dummy.payment_head_dtls(payment_id,mbc_receipt_id,charge_head_code,amount,tariff_id,creation_dt,created_by)
values (payment_no,t_payments_details.mbc_receipt_id,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;

Answer 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,receipt_id,source_system_flag,cin, ... ,pm_amount,ref_transaction_id,creation_dt,created_by)
   VALUES(t.payment_no,t.receipt_id,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_receipt_id = t.receipt_id
   LOOP
      INSERT INTO ccdb_dummy.payment_head_dtls(payment_id,mbc_receipt_id,charge_head_code,amount,tariff_id,creation_dt,created_by)
      VALUES (t.payment_no,t1.mbc_receipt_id,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,  receipt_id,   ...  ,   pm_amount, ref_transaction_id,creation_dt,created_by)   
   SELECT nextval('ccdb_stg.payments_seq'),c.cin,p.receipt_id,   ...  , 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, receipt_id
   )
INSERT INTO ccdb_dummy.payment_head_dtls(
         payment_id,  mbc_receipt_id,  charge_head_code,  amount,  tariff_id,creation_dt,created_by)
SELECT i.payment_id,h.mbc_receipt_id,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_receipt_id = i.receipt_id;

$func$  LANGUAGE sql;

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

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