Postgres 10

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 testelement (xmldata xml);
insert into testelement values ('
<TestSequence 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
  xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
  xmlns="http://www.company.com/product/specification"
  Name="new sequence.001" 
  ID="0999fbaf-c696-4b06-b4b1-489bcc123b9a" 
  InternalId="0" 
  RepeatCounter="1">  
    <TestBlockLink Id="fff6f74e-0ee4-4c70-9cf8-f1f27e5f4391" Name="Reset" InternalId="1" 
                           IdLinkedBlock="b71c0f05-00b5-4086-a1e8-574094565986" RepeatCounter="123" />
    <TestBlockLink Id="f8c6b3a5-0491-4969-bab7-3cf606217e67" Name="Switch_1" InternalId="2" 
                           IdLinkedBlock="1b5e0458-a383-468d-ab44-1468d783aab0" />
    <TestBlockLink Id="80ffd8c7-90c2-4b99-bc3c-bffa37827784" Name="1234" InternalId="3" 
                           IdLinkedBlock="c889b9b9-a5f6-4388-8d87-8fff6734bcbc" />
</TestSequence>');
1 rows affected

select x.*
from testelement, 
     xmltable(
        xmlnamespaces('http://www.company.com/product/specification' as ps), 
        '//ps:TestBlockLink' passing xmldata
        columns id uuid path '@Id', 
                name text path '@Name', 
                loops int path '@RepeatCounter', 
                id_linked_block uuid path '@IdLinkedBlock') as x;


id
name
loops
id_linked_block


fff6f74e-0ee4-4c70-9cf8-f1f27e5f4391
Reset
123
b71c0f05-00b5-4086-a1e8-574094565986

f8c6b3a5-0491-4969-bab7-3cf606217e67
Switch_1
null
1b5e0458-a383-468d-ab44-1468d783aab0

80ffd8c7-90c2-4b99-bc3c-bffa37827784
1234
null
c889b9b9-a5f6-4388-8d87-8fff6734bcbc

id	name	loops	id_linked_block
fff6f74e-0ee4-4c70-9cf8-f1f27e5f4391	Reset	123	b71c0f05-00b5-4086-a1e8-574094565986
f8c6b3a5-0491-4969-bab7-3cf606217e67	Switch_1	null	1b5e0458-a383-468d-ab44-1468d783aab0
80ffd8c7-90c2-4b99-bc3c-bffa37827784	1234	null	c889b9b9-a5f6-4388-8d87-8fff6734bcbc