delete in PG 8.1.5 is slow

From: "surabhi(dot)ahuja" <surabhi(dot)ahuja(at)iiitb(dot)ac(dot)in>
To: <pgsql-general(at)postgresql(dot)org>
Subject: delete in PG 8.1.5 is slow
Date: 2006-12-08 11:29:13
Message-ID: 8626C1B7EB748940BCDD7596134632BE3986BF@jal.iiitb.ac.in
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Browse pgsql-general by date

  From Date Subject
Next Message Andrew Sullivan 2006-12-08 11:42:17 Re: porting time calcs to PG
Previous Message Hannes Dorbath 2006-12-08 11:14:17 Re: tsearch2: pg8.1 to pg8.2