Postgres 12

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 country(id int, country_name text);
insert into country values (1, 'albania'), (2, 'denmark');

create table content(id int, countries text[]);
insert into content values
	(1, array['albania', 'denmark']),
	(1, array['albania', 'france']);

CREATE TABLE

INSERT 0 2

CREATE TABLE

INSERT 0 2

select * from country;

id
country_name

1
albania

2
denmark

SELECT 2

select * from content;

id
countries

1
{albania,denmark}

1
{albania,france}

SELECT 2

	select c.*, t.country_name
	from 
		content c
		cross join lateral unnest(c.countries) as t(country_name)
		left join country y on y.country_name = t.country_name
	where y.country_name is null

id
countries
country_name

1
{albania,france}
france

SELECT 1