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;BEGINSELECT ccdb_view_cntINTO vw_cntFROM ccdb.update_qtableWHERE status_flag = 1AND ddu_area = 2AND ddu_action = 2AND section_code = $1;IF vw_cnt > 0 THEN--delete from bills the records which are being updatedDELETE FROM ccdb.billsWHERE 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 updatedINSERT 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   pFULL 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.idAND p.ddu_action = 2AND uq.ddu_action = 2AND uq.ddu_area = 2AND uq.status_flag = 1WHERE  p.section_code = $1;--insertion into bill head detailsINSERT 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 iJOIN ccdb.o_bills_details_update_stg h ON h.mbc_bill_id = i.mbc_bill_idJOIN ccdb.update_qtable uq ON h.ccdb_qtable_id = uq.idAND h.ddu_action = 2AND uq.ddu_action = 2AND uq.ddu_area = 2AND uq.status_flag = 1AND uq.section_code = $1;               -- Updating the update qtableSELECT COUNT(*)INTO countFROM ccdb.o_bills_update_stg pu,ccdb.update_qtable uqWHERE pu.ddu_action = 2AND pu.ccdb_qtable_id = uq.idAND uq.ddu_area = 2AND uq.ddu_action = 2AND uq.status_flag = 1AND uq.section_code = $1;UPDATE ccdb.update_qtable uqSET     status_flag = 2    ,ccdb_ucount = count    ,ccdb_ulmdts = now()    ,ccdb_remarks = 'Success'FROM ccdb.o_bills_update_stg buWHERE uq.ddu_area = 2 AND bu.ddu_action = 2AND uq.ddu_action = 2AND uq.status_flag = 1AND uq.id = bu.ccdb_qtable_idAND 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 TableCREATE 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 DetailsCREATE 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 StagingCREATE 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 StagingCREATE 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 QtableCREATE 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. 
Taking data from staging table and inserting into target tables
performance;sql;postgresql
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 updatedDELETE FROM ccdb.billsWHERE 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_billedSo 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.