Postgres 9.6 parallel XPath

Question

I installed Postgres 9.6 and checked on a large table of random integers that work with parallel queries. However, a simple XPath query in the XML column of another table is always consistent. Both XPath functions are marked as Parallel safe in Postgres. I tried to change the cost of XPath, so the expected value soared up, but did not change anything. What am I missing?

Example DDL table: CREATE TABLE "test_table" ("xml" XML );

Example query: SELECT xpath('/a', "xml") FROM "test_table";

Sample data: <a></a> . Please note that the actual data contains XML 10-1000 KB in size.

 > 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 

Answer 1

The relevant clause here is probably the difference between the “size ratio” and the “total size of the relationship”:

 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 

Large column values ​​like these are not stored in the main relation, but are instead transferred to the corresponding TOAST table. This external storage is not taken into account in relation to pg_relation_size() , which, according to the optimizer, is compared with min_parallel_relation_size when evaluating a parallel plan:

 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)