Taking data from staging table and inserting into target tables

Question

I have the below mentioned function written in plpgsql which is working fine. I want someone to review it and let me know how can I improve its performance.

This function takes data from staging table and inserts into target tables. Before it inserts, it checks for the existing records, and if any records exist, it does a DELETE CASCADE in two tables. Here I have a script for a DELETE command in bills table, which in turn deletes from the bills_head_details table as well.

It currently takes about 10-15 minutes to delete about 1200 records in bills table which has an approximate 1:6 ratio with the bills_head_details. But I feel that 15 minutes for these many records is a very long duration. How can I improve its performance?

CREATE OR REPLACE FUNCTION ccdb.o_bills_update_for_update(integer)
  RETURNS void AS
$BODY$
DECLARE 
count integer;
vw_cnt integer;
BEGIN

SELECT ccdb_view_cnt
INTO vw_cnt
FROM ccdb.update_qtable
WHERE status_flag = 1
AND ddu_area = 2
AND ddu_action = 2
AND section_code = $1;

IF vw_cnt > 0 THEN

--delete from bills the records which are being updated
DELETE FROM ccdb.bills
WHERE bill_id IN (SELECT bill_id
        FROM ccdb.bills
        WHERE mbc_bill_id IN (  SELECT bu.mbc_bill_id
        FROM   ccdb.o_bills_update_stg bu
        FULL JOIN ccdb.consumers c USING (consumer_num)
        FULL JOIN ccdb.bill_type_master bt USING (bill_type_id)
        JOIN ccdb.update_qtable uq ON bu.ccdb_qtable_id = uq.id
        AND bu.ddu_action = 2
        AND uq.ddu_action = 2
        AND uq.ddu_area = 2
        AND uq.status_flag = 1
        WHERE  bu.section_code = $1));

--insertion into bills which got updated
INSERT INTO ccdb.bills
        (   
            bill_id
            ,source_system_id
            ,mbc_bill_id
            ,mbc_bill_no
            ,cin
            ,cust_connection_id
            ,consumer_number
            ,cust_type_flag
            ,bill_type_group_code
            ,bill_type_code
            ,bill_month
            ,total_consumption
            ,bill_date
            ,due_date
            ,dc_date
            ,dues_remind_date
            ,dc_remind_date
            ,org_unit_id
            ,parent_bill_id
            ,category_flag
            ,status_flag
            ,conn_cat_subgroup_id
            ,dispute_flag
            ,inst_flag
            ,approved_date
            ,bill_amt
            ,paid_amt
            ,past_arrear
            ,arrear_collected
            ,advance_amount
            ,creation_dt
            ,created_by
            ,last_update_dt
            ,last_updated_by
        )

SELECT  nextval('ccdb.bills_seq')
    ,1
    ,p.mbc_bill_id
    ,p.mbc_bill_no
    ,c.cin
    ,p.cust_connection_id
    ,coalesce(p.consumer_num,p.applicant_num)
    ,p.cust_type_flag
    ,p.bill_type_group_code
    ,bt.bill_type
    ,p.bill_month
    ,p.total_consumption
    ,p.bill_date
    ,p.due_date
    ,p.dc_date
    ,p.dues_remaind_date
    ,p.dc_remaind_date
    ,p.section_code
    ,p.parent_bill_id
    ,p.category_flag
    ,p.status_flag
    ,p.conn_cat_subgroup_id
    ,p.dispute_flag
    ,p.inst_flag
    ,p.approved_date
    ,p.bill_amt
    ,p.paid_amt
    ,p.past_arrear
    ,p.arrear_collected
    ,p.advance_amount
    ,now()
    ,'system'
    ,now()
    ,'system'
FROM   ccdb.o_bills_update_stg   p
FULL JOIN ccdb.consumers c USING (consumer_num)
FULL JOIN ccdb.bill_type_master bt USING (bill_type_id)
JOIN ccdb.update_qtable uq ON p.ccdb_qtable_id = uq.id
AND p.ddu_action = 2
AND uq.ddu_action = 2
AND uq.ddu_area = 2
AND uq.status_flag = 1
WHERE  p.section_code = $1;

--insertion into bill head details

INSERT INTO ccdb.bill_head_details
        (   bill_id
            ,charge_head_code
            ,amount_billed
            ,amount_paid
            ,charge_head_descr
            ,ccdb_update_time
            ,creation_dt
            ,created_by
            ,tariff_id
            ,demand_date
            ,last_update_dt
            ,last_updated_by
        )

SELECT  i.bill_id
    ,h.charge_head_code
    ,h.amount_billed
    ,h.amount_paid
    ,NULL
    ,now()
    ,now()
    ,'system'
    ,h.tariff_id
    ,h.demand_date
    ,now()
    ,'system'
FROM ccdb.bills i
JOIN ccdb.o_bills_details_update_stg h ON h.mbc_bill_id = i.mbc_bill_id
JOIN ccdb.update_qtable uq ON h.ccdb_qtable_id = uq.id
AND h.ddu_action = 2
AND uq.ddu_action = 2
AND uq.ddu_area = 2
AND uq.status_flag = 1
AND uq.section_code = $1;               

-- Updating the update qtable

SELECT COUNT(*)
INTO count
FROM ccdb.o_bills_update_stg pu,ccdb.update_qtable uq
WHERE pu.ddu_action = 2
AND pu.ccdb_qtable_id = uq.id
AND uq.ddu_area = 2
AND uq.ddu_action = 2
AND uq.status_flag = 1
AND uq.section_code = $1;

UPDATE ccdb.update_qtable uq
SET     status_flag = 2
    ,ccdb_ucount = count
    ,ccdb_ulmdts = now()
    ,ccdb_remarks = 'Success'
FROM ccdb.o_bills_update_stg bu
WHERE uq.ddu_area = 2 
AND bu.ddu_action = 2
AND uq.ddu_action = 2
AND uq.status_flag = 1
AND uq.id = bu.ccdb_qtable_id
AND uq.section_code = $1; 

ELSE IF vw_cnt = 0 THEN 
    UPDATE ccdb.update_qtable uq
    SET     status_flag = 2
            ,ccdb_ucount = vw_cnt
            ,ccdb_ulmdts = now()
            ,ccdb_remarks = 'Success'
    WHERE uq.ddu_area = 2
    AND uq.ddu_action = 2
    AND uq.status_flag = 1
    AND uq.section_code = $1;
 END IF;

END IF;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

The Tables are something like this:

--Bills Table
CREATE TABLE ccdb.bills
(
  bill_id bigint NOT NULL,
  source_system_id smallint NOT NULL,
  mbc_bill_id bigint,
  mbc_bill_no numeric(13,0) NOT NULL,
  cin numeric(13,0),
  cust_connection_id bigint,
  consumer_number numeric(13,0) NOT NULL,
  cust_type_flag smallint,
  bill_type_group_code character varying(30),
  bill_type_code character varying(30),
  bill_month character varying(30),
  total_consumption numeric(12,3),
  bill_date timestamp without time zone,
  due_date timestamp without time zone,
  dc_date timestamp without time zone,
  dues_remind_date timestamp without time zone,
  dc_remind_date timestamp without time zone,
  org_unit_id smallint,
  parent_bill_id bigint,
  category_flag smallint,
  status_flag smallint NOT NULL,
  conn_cat_subgroup_id smallint,
  dispute_flag smallint,
  inst_flag smallint,
  approved_date timestamp without time zone,
  bill_amt numeric(13,2),
  paid_amt numeric(13,2),
  past_arrear numeric(13,2),
  arrear_collected numeric(13,3),
  advance_amount numeric(13,3),
  creation_dt timestamp without time zone NOT NULL DEFAULT now(),
  created_by character varying(30) NOT NULL,
  last_update_dt timestamp without time zone,
  last_updated_by character varying(30),
  CONSTRAINT bills_pk PRIMARY KEY (bill_id)
)
WITH (
  OIDS=FALSE
);


--Bill Details

CREATE TABLE ccdb.bill_head_details
(
  bill_id bigint NOT NULL,
  charge_head_code character varying(30),
  amount_billed numeric(13,3),
  amount_paid numeric(13,3),
  charge_head_descr character varying(80),
  ccdb_update_time timestamp without time zone,
  creation_dt timestamp without time zone NOT NULL DEFAULT now(),
  created_by character varying(30) NOT NULL,
  last_update_dt timestamp without time zone,
  last_updated_by character varying(30),
  tariff_id character varying(20),
  demand_date timestamp without time zone
)
WITH (
  OIDS=FALSE
);

-- Bill Details Staging
CREATE TABLE ccdb.o_bills_details_update_stg
(
  mbc_bill_id bigint,
  demand_date timestamp(6) without time zone,
  tariff_id bigint,
  charge_head_code character varying(30),
  amount_billed numeric(13,3),
  amount_paid numeric(13,3),
  ddu_action smallint,
  onet_entry_time timestamp without time zone,
  ccdb_qtable_id integer
)
WITH (
  OIDS=FALSE 
);

-- Bills Staging

CREATE TABLE ccdb.o_bills_update_stg
(
  mbc_bill_id bigint,
  mbc_bill_no numeric(13,0),
  cust_connection_id bigint,
  consumer_num numeric(13,0),
  applicant_num bigint,
  cust_type_flag smallint,
  section_code integer,
  bill_type_group_code character varying(30),
  bill_type_id integer,
  bill_type_code character varying(30),
  bill_month bigint,
  total_consumption numeric(12,3),
  bill_date timestamp without time zone,
  due_date timestamp without time zone,
  dc_date timestamp without time zone,
  dues_remaind_date timestamp without time zone,
  dc_remaind_date timestamp without time zone,
  parent_bill_id bigint,
  category_flag smallint,
  status_flag smallint,
  conn_cat_subgroup_id smallint,
  dispute_flag smallint,
  inst_flag integer,
  approved_date timestamp without time zone,
  bill_amt numeric(13,2),
  paid_amt numeric(13,2),
  past_arrear numeric(13,2),
  advance_amount numeric(13,3),
  arrear_collected numeric(13,3),
  ddu_action smallint,
  onet_entry_time timestamp without time zone,
  ccdb_qtable_id integer
)
WITH (
  OIDS=FALSE
);

--Update Qtable

CREATE TABLE ccdb.update_qtable
(
  id bigserial NOT NULL,
  entry_time timestamp(6) without time zone NOT NULL,
  section_code integer NOT NULL,
  ddu_area smallint NOT NULL,
  ddu_action smallint,
  start_id bigint,
  end_id bigint,
  total_cnt integer,
  status_flag smallint,
  ccdb_ulmdts timestamp(6) without time zone,
  onet_remarks text,
  ccdb_remarks text,
  ccdb_ucount integer,
  onet_ackts timestamp(6) without time zone,
  ccdb_view_cnt integer,
  onet_ack_cnt integer,
  ccdb_staging_time timestamp(0) without time zone,
  CONSTRAINT update_qtable_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

These are the five tables that I am using while writing this function.

What it actually does is, we get date from the client in View which is populated in the staging tables using Talend Open Studio and we use the Function to populate the target from staging. Here in this 1 record in the bills Staging table can have a new record or a record which has an updated value for a bill which already exists in the target table. One bill can have multiple records corresponding to the bill id in the bill details target table. So when I get updated bills, then I delete the old record from the bills table and the bill details table which has the old value and insert it as a new record with a new bill_id.

Answer 1

Let's take a quick look at your Delete query while we wait for your schema definition. I took the liberty of properly indenting the code block. Looks like we've got some arrow code here.

--delete from bills the records which are being updated
DELETE FROM ccdb.bills
WHERE bill_id IN (
                SELECT bill_id
                FROM ccdb.bills
                WHERE mbc_bill_id 
                    IN (  
                        SELECT bu.mbc_bill_id
                        FROM   ccdb.o_bills_update_stg bu
                        FULL JOIN ccdb.consumers c USING (consumer_num)
                        FULL JOIN ccdb.bill_type_master bt USING (bill_type_id)
                        JOIN ccdb.update_qtable uq ON bu.ccdb_qtable_id = uq.id
                            AND bu.ddu_action = 2
                            AND uq.ddu_action = 2
                            AND uq.ddu_area = 2
                            AND uq.status_flag = 1
                        WHERE  bu.section_code = $1
                        )
                );

Now alarm bells start going off for me, and anytime we're talking about performance and we see a FULL JOIN, they should be ringing inside your head. Particularly when it's buried in an IN statement. Full joins are expensive and slow. You should take a hard look at this subquery and figure out why you're using one here. You only need to return bu.mbc_bill_id. I doubt you need to use a full join. Determine if an inner join is more appropriate.

In your next query, I like the insert and select parts of your statements. You're spelling out exactly where you're inserting to. That's good. I'm also a fan of putting your commas in front like this. It makes it easier to comment things out if necessary.

SELECT  i.bill_id
    ,h.charge_head_code
    ,h.amount_billed

So it turns out that you seem to have a lot of full joins in your query. I can't offer much guidance on how to change it without knowing your table schema, but you should absolutely think about why you're using them and if they can be changed to another kind of join. I'm sure it's a point of expense in the query plan.