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 settings as select '[{
  "Quantity": 2,
  "SKU": "abc123"
}, {
  "Quantity": 1,
  "SKU": "def456"
},
 {
  "Quantity": 3,
  "SKU": "ghi789"
}]' :: jsonb as stone_data;

SELECT 1


create table accent_stones as select 
* from ( values( 'abc123'::text),('def456') ) as v(sku)

SELECT 2


SELECT s.* , j->>'SKU' ,j->> 'Quantity',a.* FROM settings s 
     cross join jsonb_array_elements(stone_data) as j
    LEFT JOIN accent_stones a
ON a.sku = j->> 'SKU'


stone_data
?column?
?column?
sku


[{"SKU": "abc123", "Quantity": 2}, {"SKU": "def456", "Quantity": 1}, {"SKU": "ghi789", "Quantity": 3}]
def456
1
def456

[{"SKU": "abc123", "Quantity": 2}, {"SKU": "def456", "Quantity": 1}, {"SKU": "ghi789", "Quantity": 3}]
ghi789
3
null

[{"SKU": "abc123", "Quantity": 2}, {"SKU": "def456", "Quantity": 1}, {"SKU": "ghi789", "Quantity": 3}]
abc123
2
abc123



SELECT 3

stone_data	?column?	?column?	sku
[{"SKU": "abc123", "Quantity": 2}, {"SKU": "def456", "Quantity": 1}, {"SKU": "ghi789", "Quantity": 3}]	def456	1	def456
[{"SKU": "abc123", "Quantity": 2}, {"SKU": "def456", "Quantity": 1}, {"SKU": "ghi789", "Quantity": 3}]	ghi789	3	null
[{"SKU": "abc123", "Quantity": 2}, {"SKU": "def456", "Quantity": 1}, {"SKU": "ghi789", "Quantity": 3}]	abc123	2	abc123