Postgres 9.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE xmldata 
(
    id bigserial not null,
    documents xml
) ;
CREATE TABLE

INSERT INTO xmldata(documents)
VALUES
('
<data>
    <test color="red">Red text</test>
    <test color="green">green</test>
</data>
') ;
INSERT 0 1

SELECT 
    id, xpath('//test', documents) 
FROM 
    xmldata;


id
xpath


1
{"<test color=\"red\">Red text</test>","<test color=\"green\">green</test>"}


SELECT 1

WITH s0 AS
(
    -- Your original query
    SELECT 
        id, xpath('//test', documents) AS x
    FROM 
        xmldata
)
, s1 AS
(
    -- We unnest the array (convert it to rows)
    SELECT
        id, unnest(x) AS xml_node
    FROM
        s0
)
SELECT
    id, xml_node, (regexp_matches(xml_node::text, '<test[^>]*>(.*)<\/test>'))[1] AS data
FROM
    s1 ;
    


id
xml_node
data


1
<test color="red">Red text</test>
Red text

1
<test color="green">green</test>
green


SELECT 2

-- Compacted version
SELECT 
    id, (regexp_matches(unnest(xpath('//test', documents))::text, '<test[^>]*>(.*)<\/test>'))[1] AS xml_node
FROM 
    xmldata


id
xml_node


1
Red text

1
green


SELECT 2

`id`	`xml_node`	`data`
`1`	`<test color="red">Red text</test>`	`Red text`
`1`	`<test color="green">green</test>`	`green`

`id`	`xml_node`
`1`	`Red text`
`1`	`green`