Analyze on temp table taking very long

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.