postgresql – Postgres 9.6并行XPath
发布时间:2020-12-13 16:00:53 所属栏目:百科 来源:网络整理
导读:我已经设置了Postgres 9.6并检查了一个大型的随机整数表,并行查询正在运行. 但是,对另一个表的 XML列的简单XPath查询始终是顺序的.两个XPath函数在Postgres中都标记为并行安全.我试图改变XPath成本,因此预期成本飙升,但它没有改变任何东西. 我错过了什么?
我已经设置了Postgres 9.6并检查了一个大型的随机整数表,并行查询正在运行.
但是,对另一个表的 XML列的简单XPath查询始终是顺序的.两个XPath函数在Postgres中都标记为并行安全.我试图改变XPath成本,因此预期成本飙升,但它没有改变任何东西. 我错过了什么? 示例表DDL: 示例查询: 示例数据: > select pg_size_pretty(pg_total_relation_size('test_table')); 28 MB > explain (analyze,verbose,buffers) select xpath('/a',"xml") from test_table; Seq Scan on public.test_table (cost=0.00..64042.60 rows=2560 width=32) (actual time=1.420..4527.061 rows=2560 loops=1) Output: xpath('/a'::text,xml,'{}'::text[]) Buffers: shared hit=10588 Planning time: 0.058 ms Execution time: 4529.503 ms 解决方法
这里的相关点可能是“关系大小”和“总关系大小”之间的区别:
CREATE TABLE test_table AS SELECT ('<a>' || repeat('x',1000000) || '</a>')::xml AS "xml" FROM generate_series(1,2560); SELECT pg_size_pretty(pg_relation_size('test_table')) AS relation_size,pg_size_pretty(pg_total_relation_size('test_table')) AS total_relation_size; relation_size | total_relation_size ---------------+--------------------- 136 kB | 30 MB 像这样的大列值不存储在主关系中,而是被推送到其关联的TOAST table.此外部存储不计入pg_relation_size(),这是优化器在评估并行计划时与 SET parallel_setup_cost = 0; SET parallel_tuple_cost = 0; SET min_parallel_relation_size = '144kB'; EXPLAIN SELECT xpath('/a',"xml") FROM test_table; QUERY PLAN --------------------------------------------------------------- Seq Scan on test_table (cost=0.00..49.00 rows=2560 width=32) SET min_parallel_relation_size = '136kB'; EXPLAIN SELECT xpath('/a',"xml") FROM test_table; QUERY PLAN ------------------------------------------------------------------------------ Gather (cost=0.00..38.46 rows=2560 width=32) Workers Planned: 1 -> Parallel Seq Scan on test_table (cost=0.00..35.82 rows=1506 width=32) (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |