|
I have a function where in In a cursor loop I 1.
create a temp table (on commit drop) 2.
insert data into it 3.
Run Analyze on the table Select/update outside the loop. This has been running fine for a while on multiple setups,
large and small volumes. The setups all have the same hardware configuration. On one particular setup with about 200k records and this
analyze runs for 45min and then times out(statement timeout is set to 45 min).
typically this takes a few seconds at best. But when I move the analyze outside
the loop everything runs fine. An section of the code for reference. CREATE TEMP TABLE tmp_hierarchy_sorted ( sort_id serial,
aqu_document_id integer,parent_id integer, ancestor_id integer,
object_hierarchy character varying(255), object_hierarchy_array text[], levels
integer) ON COMMIT DROP TABLESPACE tblspc_tmp ; CREATE UNIQUE INDEX
tmp_hierarchy_sorted_aqu_document_id_idx ON tmp_hierarchy_sorted USING btree(
aqu_document_id ) TABLESPACE tblspc_index;'; execute vSQL; --get min doc number for that collection based on
existing promoted collections in the matter select coalesce(max(doc_number_max),0) into
iMin_Doc_number FROM doc_Collection c WHERE exists (SELECT 1 FROM doc_collection c1 WHERE
c1.id = iCollectionId and c1.matter_id = c.matter_id and c1.doc_number_prefix =
c.doc_number_prefix) AND status = 'PROMOTED'; --go ancestor by ancestor for ones that are not loose
files open curAncestor for select distinct id FROM aqu_document_hierarchy h
where collection_Id = iCollectionId and ancestor_id =-1 and parent_id = -1 AND EXISTS (select 1 from aqu_document_hierarchy h1
where h1.ancestor_id = h.id ) order by id ; LOOP FETCH curAncestor into iAncestor_id; EXIT WHEN NOT FOUND; --insert each ancestor into the table as this is not
part in the bulk insert vSQL := 'INSERT INTO tmp_hierarchy_sorted(
aqu_document_id, parent_id , ancestor_id , object_hierarchy,
object_hierarchy_array,levels) (select id, -1, -1, object_hierarchy,
regexp_split_to_array(object_hierarchy, ''/'') ,0 from aqu_document_hierarchy where collection_Id ='
|| iCollectionId || ' AND id = ' || iAncestor_id || ')'; execute vSQL; -- insert filtered documents for that ancestor vSQL := 'INSERT INTO tmp_hierarchy_sorted
(aqu_document_id, parent_id , ancestor_id , object_hierarchy,
object_hierarchy_array, levels) ( SELECT id, parent_id, ancestor_id,
object_hierarchy, regexp_split_to_array(object_hierarchy, ''/'') as
object_hierarchy_array, array_length(regexp_split_to_array(object_hierarchy,
''/'') ,1) as levels FROM aqu_document_hierarchy h WHERE EXISTS (SELECT
1 FROM aqu_document_error_details e where e.aqu_document_id = h.id and
e.exit_status in (2,3,4,5) ) AND ancestor_id = ' || iAncestor_id || ' ORDER BY
regexp_split_to_array(object_hierarchy, ''/'') );'; execute vSQL; ANALYZE tmp_hierarchy_sorted; END LOOP; Thanks for the help -mridula The information contained in this email message and its attachments is intended only for the private and confidential use of the recipient(s) named above, unless the sender expressly agrees otherwise. Transmission of email over the Internet is not a secure communications medium. If you are requesting or have requested the transmittal of personal data, as defined in applicable privacy laws by means of email or in an attachment to email, you must select a more secure alternate means of transmittal that supports your obligations to protect such personal data. If the reader of this message is not the intended recipient and/or you have received this email in error, you must take no action based on the information in this email and you are hereby notified that any dissemination, misuse or copying or disclosure of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by email and delete the original message. |
