delete in PG 8.1.5 is slow

["surabhi.ahuja" <surabhi.ahuja@xxxxxxxxxxx>]

 I have two similar datasets, on two diffrent m/cs.

One m/c has PG 8.0.0 installed the other has PG 8.1.5 installed

 

i vaccum analyzed both and i see that the delete performance in PG 8.1.5 is one third and sometimes one fifth of PG 8.0.0

 

please see that the number of rows etc is similar and both were vacuum analyzed.

 

I dont understand whts causing it?

 

The stored procedure that I am trying to execute is as follows:

 

CREATE OR REPLACE FUNCTION remove_exam(bigint) RETURNS text AS '

        DECLARE
                cnt INTEGER;
                pideid text;
                pid bigint;

        BEGIN
                select into pid patient_id from exam where exam_id = $1; // exam_id the primary key in the exam table.
                if not found
                then
                        raise notice ''exam not found'';
                        pideid := ''e'';
                else

                        SELECT INTO cnt count(*) from exam where patient_id = pid;
                        IF(cnt = 1)
                        THEN
                                -- this is the last exam under the patient
                                pideid := remove_patient(pid); //this procedure just does delete from patient where patient_id = pid
                        else
                                delete from exam where exam_id = $1;
                                pideid := ''p'' || pid || ''/e'' || $1;
                        END IF;
                end if;
                RETURN pideid;
        END;
'LANGUAGE 'plpgsql';

Please tell if this is an issue with PG 8.1.5

 

thanks,

regards

Surabhi

This message has been scanned by the Trend Micro IGSA and found to be free of known security risks.