pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedLong running query: How to monitor the progress
2+ messages / 2 participants
[nested] [flat]
* Long running query: How to monitor the progress
@ 2015-08-25 07:22 Johann Spies <johann.spies@gmail.com>
2015-08-25 13:50 ` Re: Long running query: How to monitor the progress Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Johann Spies @ 2015-08-25 07:22 UTC (permalink / raw)
To: pgsql-performance
Working with 9.4.
We are in the process of unpacking complicated XML-data into tables.
XML-data are already in a table with two fields (id, xml) - 47+ million
records.
Some of hour queries to extract the data and insert it in other tables runs
for days and in one case we have created a table with 758million unique
records.
Now my question. Is there a way to monitor the progress of a long running
query like this?
I have recently read that it is probably better for processes like this to
copy result of the query to a csv-file and then import it again with copy
as an insert. Next time I will try that.
The following query has been running for 6 days now and are still running
(I have anonymized it a little bit) on a server with 768 GB RAM. It has
created 44 temporary files so far:
INSERT INTO table_a_link(uid,gn_id)
WITH p AS
(SELECT ARRAY[ARRAY['t','some_xpath']] AS some_xpath),
q AS
(SELECT (xpath('//t:UID/text()',xml,some_xpath))[1] uid,
unnest(xpath('//t:grant',xml,some_xpath)) AS gr
FROM source.xml_data a,
p
WHERE xpath_exists('//t:grant/t:grant_agency', xml ,some_xpath)),
r AS
(
SELECT
CASE WHEN
xpath_exists('//t:grant_ids', gr, some_xpath)
THEN
unnest(xpath('//t:grant_ids', gr, some_xpath))
ELSE
NULL
END
AS GR_ids
FROM q,
p ) ,
y as (SELECT A.UUID AS FO_ID,
/* unnest(xpath('//t:grant_agency/text()',GR,ns))::citext AS agency,
*/ CASE WHEN
xpath_exists('//t:grant_id', gr_ids, some_xpath)
THEN
unnest(xpath('//t:grant_id/text()', gr_ids, some_xpath))::citext
ELSE
NULL
END
grant_NO,
uid::varchar(19)
from WOS.FUNDING_ORG A, p,q
left join r on (xpath('//t:grant/t:grant_ids/t:grant_id/text()',gr,
ARRAY[ARRAY['t','some_xpath']])::citext =
xpath('//t:grant_id/text()',GR_IDS,ARRAY[ARRAY['t','some_xpath']])::citext)
WHERE A.FUNDING_ORG =
(xpath('//t:grant_agency/text()',GR,some_xpath))[1]::CITEXT
)
select distinct y.uid, B.uuid gn_id
from y, table_b B
where
y.fo_id = B.fo_id
and
y.grant_no is not distinct from b.grant_no
Regards.
Johann
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Long running query: How to monitor the progress
2015-08-25 07:22 Long running query: How to monitor the progress Johann Spies <johann.spies@gmail.com>
@ 2015-08-25 13:50 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2015-08-25 13:50 UTC (permalink / raw)
To: Johann Spies <johann.spies@gmail.com>; +Cc: pgsql-performance
Johann Spies <johann.spies@gmail.com> writes:
> Some of hour queries to extract the data and insert it in other tables runs
> for days and in one case we have created a table with 758million unique
> records.
> Now my question. Is there a way to monitor the progress of a long running
> query like this?
You could watch how fast the target table is physically growing, perhaps.
Or I think contrib/pgstattuple could be used to count uncommitted tuples
in it.
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2015-08-25 13:50 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-08-25 07:22 Long running query: How to monitor the progress Johann Spies <johann.spies@gmail.com>
2015-08-25 13:50 ` Tom Lane <tgl@sss.pgh.pa.us>
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