如何在PostgreSQL中跟踪查询进度？

Question

在PostgreSQL中有没有可以跟踪长查询进度的插件或脚本？

我的意思是，我需要在Java中设置与Postgres中的一些更新查询相关的进度条值。我在互联网上搜索，但我只是找到了一些论文，没有任何正式的实现在任何RDBMS系统中。

Answer 1

我在这里找到了一个好答案：Tracking progress of an update statement

诀窍是首先创建一个序列(按您喜欢的方式命名)：

CREATE SEQUENCE query_progress START 1;

然后附加到查询的WHERE部分：

AND NEXTVAL('query_progress')!=0

现在可以查询进度了：

SELECT NEXTVAL('query_progress');

最后，别忘了去掉序列：

DROP SEQUENCE query_progress;

请注意，这很可能会使您的查询运行得更慢，并且每次您检查进度时，它都会额外增加该值。上面的链接建议创建一个临时序列，但PostgreSQL似乎没有使它们在会话中可见。

Answer 2

我已经想出了一种可能有用的方法。但是，如果您希望将其实现到Java等代码中，则可能需要进一步的处理。

方法是检查页面内容以跟踪进度。

Postgresql有一个名为page的扩展，可以检查特定表的页面信息。

详情请访问：https://www.postgresql.org/docs/current/pageinspect.html

我还要花一些时间在这里了解postgresql的页面布局

https://www.postgresql.org/docs/current/storage-page-layout.html

请特别查看xmin、xmax和ctid

我假设表的插入是按照一定的顺序进行的。就像桌子的pkey一样。而且任何长的更新都可能会附加新的页面。

我还假设主键id大部分是连续的，几乎没有间隙。由于这只是一个估计，我认为在这种情况下是可以的。

但是，你不能通过执行SELECT relname, relpages FROM pg_class来找出总页数，因为它没有更新。

如果strage中不存在页面索引，您将遇到一个异常(但您会找到该页面，即使它没有在pg_class中更新或更新)，所以在"page_index“上做一个小的”二进制搜索“来查找您拥有的最大的页面。不需要太精确。

使用

SELECT backend_xid FROM pg_stat_activity WHERE pid = process-id

查找您当前的交易id。

使用

SELECT lp,t_xmin,t_xmax,t_ctid,t_bits,t_data FROM heap_page_items(get_raw_page('relation_name', page_index));

在我正在处理的示例中，它可能如下所示

SELECT lp，t_xmin，t_xmax，t_ctid，t_bits，t_data

heap_page_items(get_raw_page('foo'，3407000))；

lp | t_xmin | t_xmax | t_ctid | t_bits | t_data

1| 592744 | 592744 | (3407000,1) | 110000000111000000000000 | \xd1100000000000000e4400000000000054010000611b0000631b0000

2| 592744 | 592744 | (3407000,2) | 110000000111000000000000 | \xd110000000000000104400000000000040010000611b0000631b0000

3| 592744 | 592744 | (3407000,3) | 110000000111000000000000 | \xd11000000000000011440000000000007c010000611b0000631b0000

t_data是数据。lp是项目列表中的元组索引。t_xmin和t_xmax是交易id。t_ctid是指向元组本身内的元组的点。如果元组中有空值，则t_bits是空位图。

首先检查t_min = t_max，t_ctid (page_index，tuple_id)和lp是否相同。如果是，请检查t_xmin是否与您的交易id相同。如果是这样，请检查数据。

注意字节顺序和空位图。在我的例子中，它是big-endian (LSB优先)。

在我的示例中，第一行是有效的。第一个BIGINT (8个字节，16个十六进制数)是我正在查找的排序id。因此，第一行的数据是

\xd110000000000000

转换为0x101d (检查字节顺序) --> 4305

我知道我的最大id是18209，smallest_id是2857。我把工作分成8个部分

(18209 - 2857) /8= 1919

这是我运行的第一部分。所以

2857 + 1919 = 4776

这意味着我的子作业从2857id开始，当前是4305。如果达到4776，这个线程就完成了！

这是

(4305 - 2857)/ 1919 = 75.5%完成

限制

这将不适用于哈希值更新。在我的例子中，id恰好与pkey一样按顺序排序。并且计划器触发顺序读取。如果规划器正在执行某种类型的btree索引扫描以进行更新，这也应该起作用。

如果您对按索引顺序对物理行进行排序感兴趣，请查看CLUSTER。

同样，这种方法是不精确的。并且上面突出显示了这个假设。如果在程序中使用，则应稀疏使用，以防止磁盘I/O的额外开销

Answer 3

我不确定这是否是人们所寻找的确切答案，但是我做了一个简单的函数，通过测量一段时间内的页面大小来报告表插入的当前状态。这不是正在发生的事情的直接窗口，但它是正在发生的事情/是否发生任何事情的一个很好的近似值。它也是底线(一个表被“填满”的速度有多快)的可靠度量。

此函数返回表及其所有相关索引的当前大小(以字节和人类可读的单位表示)和增长率的表名列表。

**奖励:它还包括临时文件活动

我特别使用它来查看加载表的进度以及它被加载的速度，这对于估计需要多长时间是很好的(尽管对于大的加载越来越不是线性的)。

下面是一个可移植的函数：

CREATE OR REPLACE FUNCTION table_build_monitor(
    IN table_or_schema_list TEXT[] DEFAULT NULL
,   IN sample_period INT DEFAULT 10
)
RETURNS TABLE (
    table_name TEXT
,   table_size TEXT
,   index_size TEXT
)
AS
$$
DECLARE
    table_list TEXT[];
    schema_list TEXT[];
BEGIN

DROP TABLE IF EXISTS table_sizes_loop;
CREATE TEMP TABLE table_sizes_loop (
    table_name_loop TEXT
,   table_size_bytes BIGINT
,   indexes_size_bytes BIGINT
)
;

select
    array_remove(array_agg(case when split_part(poo, '.',2) = '*' then split_part(poo, '.',1) else NULL end), NULL::TEXT)
,   array_remove(array_agg(case when split_part(poo, '.',2) = '*' then NULL else poo end), NULL::TEXT)
FROM unnest(array[table_or_schema_list]) poo
INTO schema_list, table_list
;

INSERT INTO table_sizes_loop

SELECT
    pg_tables.schemaname||'.'|| pg_tables.tablename as table_name
,   pg_relation_size(pg_tables.schemaname||'.'|| pg_tables.tablename) AS table_size_bytes
,   pg_indexes_size(pg_tables.schemaname||'.'|| pg_tables.tablename) AS indexes_size_bytes
FROM pg_tables
WHERE
    pg_tables.schemaname = ANY(schema_list)
OR  (pg_tables.schemaname||'.'|| pg_tables.tablename)::text = ANY(table_list)

UNION

SELECT
    'temp_files'
,   temp_bytes
,   NULL
FROM pg_stat_database
WHERE
    datname = current_database()
;

PERFORM pg_sleep(sample_period);

RETURN QUERY

with
    base AS
(
SELECT
    pg_tables.schemaname||'.'|| pg_tables.tablename as table_name_loop
,   pg_relation_size(pg_tables.schemaname||'.'|| pg_tables.tablename) AS table_size_bytes
,   pg_indexes_size(pg_tables.schemaname||'.'|| pg_tables.tablename) AS indexes_size_bytes

FROM pg_tables
WHERE
    pg_tables.schemaname::text = ANY(schema_list)
OR  (pg_tables.schemaname||'.'|| pg_tables.tablename)::text = ANY(table_list)

UNION

SELECT
    'temp_files'
,   temp_bytes
,   NULL
FROM pg_stat_database
WHERE
    datname = current_database()

)
SELECT
    table_name_loop
,   CASE WHEN table_name_loop = 'temp_files' THEN
        pg_size_pretty((base.table_size_bytes - tsl.table_size_bytes)/sample_period) || '/s'
    ELSE
            base.table_size_bytes
        || ' (' || pg_size_pretty((base.table_size_bytes))
        || ') - ' || pg_size_pretty((base.table_size_bytes - tsl.table_size_bytes)/sample_period) || '/s'
    END as table_size
,       base.table_size_bytes
    || ' (' || pg_size_pretty((base.indexes_size_bytes))
    || ') - ' || pg_size_pretty((base.indexes_size_bytes - tsl.indexes_size_bytes)/sample_period) || '/s'
    as table_size
FROM table_sizes_loop tsl
JOIN base USING (table_name_loop)
ORDER BY base.table_size_bytes DESC
;

END
$$
LANGUAGE plpgsql
;

要查看它，可以使用如下select语句，传递一个模式限定的表列表，或者为整个模式传递类似于" schema .*“的内容-以及可选的样本周期(缺省值为10s)。

select * from table_build_monitor('{public.*}', 3);