Postgres 16

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 restaurants (
  id int PRIMARY KEY,
  name text UNIQUE,
  type text
);
CREATE TABLE food (
  id int PRIMARY KEY,
  restaurant_id int REFERENCES restaurants (id) NOT NULL,
  waste_type_id smallint NOT NULL,
  product_id int NOT NULL,
  waste_kgs decimal NOT NULL, 
  prepared_kgs decimal NOT NULL,
  customers smallint NOT NULL,
  days date NOT NULL
);
CREATE TABLE tags (
  id int PRIMARY KEY,
  restaurant_id int REFERENCES restaurants (id),
  name text
);

CREATE TABLE



CREATE TABLE



CREATE TABLE


insert into restaurants values 
(1, 'test1'),
(2, 'test2'),
(3, 'test3');

INSERT 0 3


insert into food values 
(1, 1, 1, 1, 1.7, 8.0, 96, '2023-9-11'),
(2, 1, 1, 7, 0.5, 7.0, 96, '2023-9-11'),
(3, 1, 1, 15, 0.3, 1.0, 96, '2023-9-11'),
(4, 1, 1, 8, 0.16, 1.5, 96, '2023-9-11'),
(5, 1, 1, 3, 0.3, 0.6, 96, '2023-9-11'),
(6, 1, 1, 12, 0.1, 0.85, 96, '2023-9-11'),
(7, 1, 1, 17, 0.3, 9.0, 96, '2023-9-11'),
(8, 1, 2, 26, 1.1, 0.0, 96, '2023-9-11'),
(9, 1, 1, 1, 0.4, 15.0, 97, '2023-9-12'),
(10, 1, 1, 8, 0.4, 2.0, 97, '2023-9-12'),
(11, 2, 1, 8, 0.1, 1.0, 97, '2023-9-24'),
(12, 2, 1, 3, 0.2, 0.5, 97, '2023-9-24'),
(13, 2, 1, 17, 5.0, 10.0, 97, '2023-9-24'),
(14, 2, 1, 12, 9.0, 1.7, 97, '2023-9-24'),
(15, 2, 1, 8, 0.0, 0.3, 97, '2023-9-24'),
(16, 2, 2, 26, 0.8, 0.0, 97, '2023-9-24'),
(17, 2, 1, 17, 8.0, 3.0, 39, '2023-9-25'),
(18, 2, 1, 12, 7.0, 0.8, 39, '2023-9-25'),
(19, 2, 1, 13, 1.1, 5.0, 39, '2023-9-25'),
(20, 2, 1, 11, 0.5, 1.5, 39, '2023-9-25'),
(21, 3, 1, 8, 0.0, 0.3, 97, '2023-9-24'),
(22, 3, 2, 26, 0.8, 0.0, 97, '2023-9-24'),
(23, 3, 1, 17, 8.0, 3.0, 39, '2023-9-25'),
(24, 3, 1, 12, 7.0, 0.8, 39, '2023-9-25'),
(25, 3, 1, 13, 1.1, 5.0, 39, '2023-9-25'),
(26, 3, 1, 11, 0.5, 1.5, 39, '2023-9-25');

INSERT 0 26


insert into tags values
(1, 1, 'basic');

INSERT 0 1


-- With UNION ALL, result with tags on the first row -> using that as a result.
SELECT Total, Total - Drinks AS "Without drinks", Drinks   
FROM (
  SELECT 
  SUM (f.prepared_kgs) 
  FILTER (
    WHERE f.days BETWEEN '2000-1-1' AND '2099-1-1'
    AND f.restaurant_id = ANY (array[1, 2, 3])
  ) AS Total,
  SUM (f.prepared_kgs)
  FILTER (
    WHERE (f.product_id = 10 OR f.product_id = 17) 
    AND f.days BETWEEN '2000-1-1' AND '2099-1-1'
    AND f.restaurant_id = ANY (array[1, 2, 3])
  ) AS Drinks
  FROM food f
  INNER JOIN tags t 
  ON t.restaurant_id = f.restaurant_id 
  AND t.name = ANY (array['basic']::text[])
)
UNION ALL
SELECT Total, Total - Drinks AS "Without drinks", Drinks   
FROM (
  SELECT 
  SUM (f.prepared_kgs) 
  FILTER (
    WHERE f.days BETWEEN '2000-1-1' AND '2099-1-1'
    AND f.restaurant_id = ANY (array[1, 2, 3])
  ) AS Total,
  SUM (f.prepared_kgs)
  FILTER (
    WHERE (f.product_id = 10 OR f.product_id = 17) 
    AND f.days BETWEEN '2000-1-1' AND '2099-1-1'
    AND f.restaurant_id = ANY (array[1, 2, 3])
  ) AS Drinks
  FROM food f
);


total
Without drinks
drinks


44.95
35.95
9.0

79.35
54.35
25.0



SELECT 2


-- With UNION ALL, no tags -> the first row is null, using the second row.
(SELECT Total, Total - Drinks AS "Without drinks", Drinks   
FROM (
  SELECT 
  SUM (f.prepared_kgs) 
  FILTER (
    WHERE f.days BETWEEN '2000-1-1' AND '2099-1-1'
    AND f.restaurant_id = ANY (array[1, 2, 3])
  ) AS Total,
  SUM (f.prepared_kgs)
  FILTER (
    WHERE (f.product_id = 10 OR f.product_id = 17) 
    AND f.days BETWEEN '2000-1-1' AND '2099-1-1'
    AND f.restaurant_id = ANY (array[1, 2, 3])
  ) AS Drinks
  FROM food f
  INNER JOIN tags t 
  ON t.restaurant_id = f.restaurant_id 
  AND t.name = ANY (array[]::text[])
)
UNION ALL
SELECT Total, Total - Drinks AS "Without drinks", Drinks   
FROM (
  SELECT 
  SUM (f.prepared_kgs) 
  FILTER (
    WHERE f.days BETWEEN '2000-1-1' AND '2099-1-1'
    AND f.restaurant_id = ANY (array[1, 2, 3])
  ) AS Total,
  SUM (f.prepared_kgs)
  FILTER (
    WHERE (f.product_id = 10 OR f.product_id = 17) 
    AND f.days BETWEEN '2000-1-1' AND '2099-1-1'
    AND f.restaurant_id = ANY (array[1, 2, 3])
  ) AS Drinks
  FROM food f
))
--You can skip the null row by ordering nulls last and using limit 1 to fetch 1 only
order by 1 nulls last,2 nulls last,3 nulls last limit 1;


total
Without drinks
drinks


79.35
54.35
25.0



SELECT 1


-- Tag 'basic' provided as an argument -> correct result.
SELECT Total, Total - Drinks AS "Without drinks", Drinks   
FROM (
  SELECT 
  SUM (f.prepared_kgs) FILTER (
    WHERE f.days BETWEEN '2000-1-1' AND '2099-1-1'
      AND f.restaurant_id = ANY (array[1, 2, 3])
  ) AS Total,
  SUM (f.prepared_kgs) FILTER (
    WHERE (f.product_id = 10 OR f.product_id = 17) 
      AND f.days BETWEEN '2000-1-1' AND '2099-1-1'
      AND f.restaurant_id = ANY (array[1, 2, 3])
  ) AS Drinks
  FROM food f
  INNER JOIN tags t 
    ON t.restaurant_id = f.restaurant_id 
  AND array_position(array['basic']::text[], t.name) IS NOT null 
    OR array_length(array['basic']::text[], 1) IS null
);


total
Without drinks
drinks


44.95
35.95
9.0



SELECT 1


-- No tags as argument -> result is too big.
SELECT Total, Total - Drinks AS "Without drinks", Drinks   
FROM (
  SELECT 
  SUM (f.prepared_kgs) FILTER (
    WHERE f.days BETWEEN '2000-1-1' AND '2099-1-1'
     AND f.restaurant_id = ANY (array[1, 2, 3])
  ) AS Total,
  SUM (f.prepared_kgs) FILTER (
    WHERE (f.product_id = 10 OR f.product_id = 17) 
      AND f.days BETWEEN '2000-1-1' AND '2099-1-1'
      AND f.restaurant_id = ANY (array[1, 2, 3])
  ) AS Drinks
  FROM food f
  INNER JOIN tags t 
    ON t.restaurant_id = f.restaurant_id 
  --you made these additional JOIN conditions instead of WHERE conditions
  --which makes it catch more than it should
  AND array_position(array[]::text[], t.name) IS NOT null 
    OR array_length(array[]::text[], 1) IS null
);
-- I might have had some website refreshing issues last time etc. This is actually the correct result.


total
Without drinks
drinks


79.35
54.35
25.0



SELECT 1


SELECT Total, Total - Drinks AS "Without drinks", Drinks   
FROM (
  SELECT 
  SUM (f.prepared_kgs) FILTER (
    WHERE f.days BETWEEN '2000-1-1' AND '2099-1-1'
      AND f.restaurant_id = ANY (array[1, 2, 3])
  ) AS Total,
  SUM (f.prepared_kgs) FILTER (
    WHERE (f.product_id = 10 OR f.product_id = 17) 
      AND f.days BETWEEN '2000-1-1' AND '2099-1-1'
      AND f.restaurant_id = ANY (array[1, 2, 3])
  ) AS Drinks
  FROM food f
  INNER JOIN tags t 
    ON t.restaurant_id = f.restaurant_id 
  --here it is as a `WHERE` condition
  WHERE array_position(array[]::text[], t.name) IS NOT null 
    OR array_length(array[]::text[], 1) IS null
);
-- This result is the same as with tags. If no tags are given as arguments, 
-- the result should be calculated from all the given restaurant_ids. 
-- So your answer seems to work with my 'mistake'.


total
Without drinks
drinks


44.95
35.95
9.0



SELECT 1