[QUESTIONS] slow "select lo_unlink(..) where ...;" ?

Started by Park, Chul-Suover 27 years ago1 messages
#1Park, Chul-Su
pcs@mhlx01.kek.jp

Hi Hackers,

When I record ~ 1000 records && blobs and try to remove, it takes
forever!
e.g.

create table PNT (
id int not null, -- database record number
exp int not null, -- experiment number
run int not null, -- run
run_to int not null, -- run to(valid range)
version int not null, -- version number
datatype text default 'blob', -- data type
created timestamp default current_timestamp, --
creation time
modified timestamp default current_timestamp, --
modification time
owner name default getpgusername(), -- owner
loid oid default 0, -- reference to pnt bank

constraint PNT_con check(run>0 AND run<=run_to AND version>0)
);

... and deposit ~ 1000 blobs(large objects with size ~ 10k), it takes ~
2 sec/record seems to be reonable.
But, deleting with

result = PQexec(conn,
"DECLARE pntcur CURSOR FOR "
"SELECT count(lo_unlink(int4(oid_text(loid)))) "
"FROM PNT"
";");
PQclear(result);
result = PQexec(conn, "FETCH 1 IN pntcur;");

OR

sprintf(cmd,
"DECLARE pntcur CURSOR FOR "
"SELECT count(lo_unlink(int4(oid_text(loid)))) "
"FROM PNT WHERE exp = %d"
";"
, exp);
result = PQexec(conn, cmd);
PQclear(result);
result = PQexec(conn, "FETCH 1 IN pntcur;");

takes forever! "destroydb" also... something to do with inefficient
"inv-tree"?

is there any plan to recall "simple unix file" based blobs? I guess
that "inv" based blob seems to be
really inefficient... any comment?

best regards, cs