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 product_variants (product_id int, options_xml xml);
insert into product_variants
values
(132, '<options><option_id>1</option_id><option_id>8</option_id></options>');
CREATE TABLE
INSERT 0 1
select p.product_id, t.option_id
from product_variants p
cross join xmltable('/options/option_id'
passing p.options_xml
columns
option_id integer path '.') as t
| product_id | option_id |
|---|---|
| 132 | 1 |
| 132 | 8 |
SELECT 2