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 company
( company_name text not null, 
  job text not null, 
  name text not null,
  primary key (name, company_name, job)
) ;

CREATE TABLE


insert into company
  (name, company_name, job)
values
  ('Bob Ross',    'Painters',  'director'),
  ('Carl',        'Painters',  'manager'),
  ('Carl',        'Sunny-D',   'administrator'),
  ('Celine Dion', 'Sunny-D',   'director'),
  ('Johnny',      'Painters',  'worker'),
  ('Johnny',      'X-factory', 'worker'),
  ('Celine Dion', 'X-factory', 'director') ;

INSERT 0 7


WITH RECURSIVE
    conn AS
    ( SELECT
          name,
          company_name,
          job,
          (ROW_NUMBER() OVER (ORDER BY company_name, job))::text AS rn,
          CONCAT_WS(', ', name, company_name, job)::text AS node,
          1 AS lvl
      FROM
          company
      WHERE
          name = 'Bob Ross'
      UNION ALL
      SELECT
          b.name,
          b.company_name,
          b.job,
          CONCAT(a.rn, '-',  (ROW_NUMBER() OVER (PARTITION BY a.rn ORDER BY b.name, b.company_name, b.job))::text),
          CONCAT(a.node, ' - ', CONCAT_WS(', ', b.name, b.company_name, b.job)),
          lvl + 1
      FROM
          conn AS a
          JOIN company AS b
          ON (  (  a.company_name = b.company_name
               AND a.name <> b.name AND a.lvl % 2 = 1
                )
             OR  (  a.company_name <> b.company_name
               AND a.name = b.name AND a.lvl % 2 = 0
                )
             )
          AND a.name <> 'Celine Dion'
          AND b.name <> 'Bob Ross'
    )
SELECT r.*
FROM conn AS f
     JOIN conn AS r 
     ON f.rn LIKE CONCAT(r.rn, '%')
WHERE f.name = 'Celine Dion' ;


name
company_name
job
rn
node
lvl


Bob Ross
Painters
director
1
Bob Ross, Painters, director
1

Carl
Painters
manager
1-1
Bob Ross, Painters, director - Carl, Painters, manager
2

Carl
Sunny-D
administrator
1-1-1
Bob Ross, Painters, director - Carl, Painters, manager - Carl, Sunny-D, administrator
3

Celine Dion
Sunny-D
director
1-1-1-1
Bob Ross, Painters, director - Carl, Painters, manager - Carl, Sunny-D, administrator - Celine Dion, Sunny-D, director
4

Bob Ross
Painters
director
1
Bob Ross, Painters, director
1

Johnny
Painters
worker
1-2
Bob Ross, Painters, director - Johnny, Painters, worker
2

Johnny
X-factory
worker
1-2-1
Bob Ross, Painters, director - Johnny, Painters, worker - Johnny, X-factory, worker
3

Celine Dion
X-factory
director
1-2-1-1
Bob Ross, Painters, director - Johnny, Painters, worker - Johnny, X-factory, worker - Celine Dion, X-factory, director
4



SELECT 8

name	company_name	job	rn	node	lvl
Bob Ross	Painters	director	1	Bob Ross, Painters, director	1
Carl	Painters	manager	1-1	Bob Ross, Painters, director - Carl, Painters, manager	2
Carl	Sunny-D	administrator	1-1-1	Bob Ross, Painters, director - Carl, Painters, manager - Carl, Sunny-D, administrator	3
Celine Dion	Sunny-D	director	1-1-1-1	Bob Ross, Painters, director - Carl, Painters, manager - Carl, Sunny-D, administrator - Celine Dion, Sunny-D, director	4
Bob Ross	Painters	director	1	Bob Ross, Painters, director	1
Johnny	Painters	worker	1-2	Bob Ross, Painters, director - Johnny, Painters, worker	2
Johnny	X-factory	worker	1-2-1	Bob Ross, Painters, director - Johnny, Painters, worker - Johnny, X-factory, worker	3
Celine Dion	X-factory	director	1-2-1-1	Bob Ross, Painters, director - Johnny, Painters, worker - Johnny, X-factory, worker - Celine Dion, X-factory, director	4