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