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 category (id int, name text, parent_id int);
INSERT INTO category (id, name, parent_id) VALUES
   (1, 'name1', NULL),
   (2, 'name2', 1),
   (3, 'name3', 2),
   (4, 'name4', 1),
   (5, 'name5', 4),
   (6, 'name6', NULL);

CREATE TABLE cat_prod (id int, product_id int, category_id int);
INSERT INTO cat_prod (id, product_id, category_id)
VALUES
    (1, 1, 1),
    (2, 1, 2),
    (3, 2, 1),
    (4, 3, 1),
    (5, 4, 2),
    (6, 5, 2),
    (7, 6, 3);  -- ? product 6 is missing on your demo sample?


CREATE TABLE product (id int, name text);
INSERT INTO product (id, name) VALUES
    (1, 'name1'),
    (2, 'name2'),
    (3, 'name3'),
    (4, 'name4'),
    (5, 'name5');

CREATE TABLE



INSERT 0 6



CREATE TABLE



INSERT 0 7



CREATE TABLE



INSERT 0 5


-- func to return id of category and all children
CREATE OR REPLACE FUNCTION f_cats_of_id(int)
  RETURNS TABLE (category_id int) AS
$func$
WITH RECURSIVE cte AS (
   SELECT $1 AS parent_id  -- not checking existence, cheapest
  
   UNION ALL
   SELECT c.id
   FROM   cte
   JOIN   category c USING (parent_id)
   )
TABLE cte
$func$  LANGUAGE SQL;

CREATE FUNCTION


SELECT DISTINCT p.*
FROM   f_cats_of_id(1) c
JOIN   cat_prod cp USING (category_id)
JOIN   product p ON p.id = cp.product_id;


id
name


3
name3

5
name5

1
name1

2
name2

4
name4



SELECT 5