Trigger taking time to insert data in postgres (column count 300)-CodePudding

I have created a trigger, it is taking more time while inserting multiple records. Insetting 1 or 2 records is working. But if the records are more than 1000 then not fast, still running query from 2 hours.

I have created only 15 columns in below table. My actual table has 300 columns.

Is any other way to insert multiple records on the trigger table.?

Table

create table patients (
  id serial,
  name character varying (50),
  daily varchar (8),
  month varchar (6),
  quarter varchar (6),
  registration_date timestamp,
  age integer,
  address text,
  country text,
  city text,
  phone_number integer,
  Education text,
  Occupation text,
  Marital_Status text,"E-mail" text
);

trigger function

CREATE OR REPLACE FUNCTION update_data_after_insert_data_into_patients() 
RETURNS trigger AS
$$BEGIN
   
    update patients t1 
    set quarter=t2.quarter
    from (SELECT (extract(year from registration_date)::text || 'Q' || extract(quarter from registration_date)::text) as quarter,registration_date
    from patients) t2 where t1.registration_date =t2.registration_date;

    update patients t1 
    set month=t2.month
    from (select (extract(year from registration_date)::text || '' || to_char(registration_date,'MM')) as month,registration_date
    from patients) t2 where t1.registration_date =t2.registration_date;

    update patients t1 
    set daily=t2.daily
    from (select extract(year from registration_date) || '' ||to_char(registration_date,'MM') || '' || to_char(registration_date,'DD') as daily,registration_date
    from patients) t2 where t1.registration_date =t2.registration_date;


   RETURN new;
END;

$$ LANGUAGE plpgsql;

Trigger definition

create TRIGGER trigger_update_data_after_insert_patients
  AFTER insert ON patients
  FOR EACH ROW 
EXECUTE PROCEDURE update_data_after_insert_data_into_patients();

insert multiple records into patients table

INSERT INTO public.patients
("name", daily, "month", quarter, registration_date, age, address, country, city, phone_number, education, occupation, marital_status, "E-mail")
VALUES('Adam', '20221215', '202212', '2022Q4', '2022-08-17 19:01:10-08', 24, '', '', '', 1245578, '', '', '', '');

select statement

select * from patients;

CodePudding user response：

You are updating all rows in the table with the same registration date as the one provided in the insert three times - just to calculate those generated columns.

You can do this more efficiently by assigning the generated values to the NEW record in a BEFORE trigger.

CREATE OR REPLACE FUNCTION update_data_after_insert_data_into_patients() 
RETURNS trigger AS
$$
BEGIN
  new.quarter := to_char(new.registration_date, 'yyyy"Q"q');
  new.month := to_char(new.registration_date, 'yyyy mm');
  new.daily := to_char(new.registration_date, 'yyyymmdd');
  RETURN new;
END;

$$ 
LANGUAGE plpgsql;

create TRIGGER trigger_update_data_after_insert_patients
  BEFORE insert ON patients
  FOR EACH ROW 
  EXECUTE PROCEDURE update_data_after_insert_data_into_patients();

However I don't see the need to store these calculated values when you can easily format the registration_date when retrieving the data. I would get rid of those columns and the trigger and create a VIEW that does the formatting.