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 settings
AS
SELECT '[{
  "Quantity": 2,
  "SKU": "abc123"
}, {
  "Quantity": 1,
  "SKU": "def456"
}]'::jsonb stone_data;
1 rows affected

CREATE TABLE accent_stones
AS
SELECT 'abc123'::text sku
UNION ALL
SELECT 'def456'::text sku;
2 rows affected

SELECT *
       FROM settings
            LEFT JOIN accent_stones
                      ON accent_stones.sku IN (SELECT e->>'SKU'
                                                      FROM jsonb_array_elements(stone_data) ja (e));


stone_data
sku


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

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

stone_data	sku
[{"SKU": "abc123", "Quantity": 2}, {"SKU": "def456", "Quantity": 1}]	abc123
[{"SKU": "abc123", "Quantity": 2}, {"SKU": "def456", "Quantity": 1}]	def456