pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedAnalyze on temp table taking very long
3+ messages / 2 participants
[nested] [flat]
* Analyze on temp table taking very long
@ 2011-03-22 16:13 Mahadevan, Mridula <Mridula.Mahadevan@ironmountain.com>
0 siblings, 1 reply; 3+ messages in thread
From: Mahadevan, Mridula @ 2011-03-22 16:13 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Analyze on temp table taking very long
@ 2011-03-22 22:56 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mahadevan, Mridula <Mridula.Mahadevan@ironmountain.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2011-03-22 22:56 UTC (permalink / raw)
To: Mahadevan, Mridula <Mridula.Mahadevan@ironmountain.com>; +Cc: pgsql-performance
"Mahadevan, Mridula" <Mridula.Mahadevan@ironmountain.com> writes:
> 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.
Is it actually *running*, as in doing something, or is it just blocked?
I can't immediately think of any reason for some other process to have
a lock on a temp table that belongs to your process; but it seems
unlikely that ANALYZE would randomly take much longer than expected
unless something was preventing it from making progress.
Look into pg_locks and/or watch the backend with strace next time this
happens.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Analyze on temp table taking very long
@ 2011-03-25 17:32 Mahadevan, Mridula <Mridula.Mahadevan@ironmountain.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Mahadevan, Mridula @ 2011-03-25 17:32 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Thanks for the tip. I'll also check in the lock, it's a customer setup and we don't get access to the box very frequently.
Also
The code was something like this.
loop
inserting data into the tmptbl
analyze tmptbl
end loop
if I replace this with
loop
inserting data into the tmptbl
end loop
analyze
It goes through fine.
-mridula
-----Original Message-----
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Tuesday, March 22, 2011 3:57 PM
To: Mahadevan, Mridula
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Analyze on temp table taking very long
"Mahadevan, Mridula" <Mridula.Mahadevan@ironmountain.com> writes:
> 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.
Is it actually *running*, as in doing something, or is it just blocked?
I can't immediately think of any reason for some other process to have
a lock on a temp table that belongs to your process; but it seems
unlikely that ANALYZE would randomly take much longer than expected
unless something was preventing it from making progress.
Look into pg_locks and/or watch the backend with strace next time this
happens.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-03-25 17:32 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-03-22 16:13 Analyze on temp table taking very long Mahadevan, Mridula <Mridula.Mahadevan@ironmountain.com>
2011-03-22 22:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-03-25 17:32 ` Mahadevan, Mridula <Mridula.Mahadevan@ironmountain.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox