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?.
select version();

version

PostgreSQL 10.23 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-15), 64-bit

SELECT 1

create table products(id int);
create table publications(name varchar, product_id int, visible boolean);
create table sales (date date, product_id int, quantity int);

CREATE TABLE

CREATE TABLE

CREATE TABLE

insert into products values(1);
insert into products values(2);
insert into publications values('online',1,'t');
insert into publications values('retail',2,'t');
insert into sales values ('2020-11-07',1,10);
insert into sales values ('2020-10-07',1,10);
insert into sales values ('2020-09-07',2,10);

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

SELECT
  products.id AS id
FROM products;
SELECT
  publications.visible AS online_visible
FROM products
INNER JOIN publications ON publications.product_id = products.id
WHERE publications.name = 'online';
SELECT
  publications.visible AS retail_visible
FROM products
INNER JOIN publications ON publications.product_id = products.id
WHERE publications.name = 'retail';
SELECT
  SUM(sales.quantity) AS year_sales
FROM products
LEFT OUTER JOIN sales ON sales.product_id = products.id
WHERE sales.date > current_date - interval '365' day
GROUP BY products.id;
SELECT
  SUM(sales.quantity) AS month_sales
FROM products
LEFT OUTER JOIN sales ON sales.product_id = products.id
WHERE sales.date > current_date - interval '30' day
GROUP BY products.id;

id

1

2

SELECT 2

online_visible

t

SELECT 1

retail_visible

t

SELECT 1

year_sales

SELECT 0

month_sales

SELECT 0

select 
pr.id "id",
coalesce(bool_or(pu.visible) filter (where pu.name='online'),false) "online_visible",
coalesce(bool_or(pu.visible) filter (where pu.name='retail'),false) "retail_visible",
coalesce(sum(sa.quantity) filter  (where sa.date > current_date - interval '365' day),0) "year_sales",
coalesce(sum(sa.quantity) filter  (where sa.date > current_date - interval '30' day),0) "month_sales"
from products pr
left join publications pu on pr.id=pu.product_id
left join sales sa on sa.product_id=pr.id
group by 1

id
online_visible
retail_visible
year_sales
month_sales

1
t
f
0
0

2
f
t
0
0

SELECT 2