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


version


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



SELECT 1


CREATE TABLE IF NOT EXISTS company (
  id int NOT NULL,
  name varchar(100) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE


CREATE TABLE IF NOT EXISTS worker (
  id int NOT NULL,
  firstname varchar(50) NOT NULL,
  lastname varchar(50) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE


CREATE TABLE IF NOT EXISTS log (
  id int NOT NULL,
  worker_id int NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT l_w_id_fk FOREIGN KEY (worker_id) REFERENCES worker (id)
);

CREATE TABLE


CREATE TABLE IF NOT EXISTS log_item (
  id int NOT NULL,
  log_id int NOT NULL,
  company_id int NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT li_c_id_fk FOREIGN KEY (company_id) REFERENCES company (id),
  CONSTRAINT li_l_id_fk FOREIGN KEy (log_id)     REFERENCES log (id)
);

CREATE TABLE


INSERT INTO company (id, name) VALUES
(1, 'Google'),
(2, 'IBM'),
(3, 'HP'),
(4, 'ITC');

INSERT 0 4


INSERT INTO worker (id, firstname, lastname) VALUES
(1, 'Frantisek', 'Smith'),
(2, 'Peter', 'Duff'),
(3, 'David', 'One'),
(4, 'Erik', 'Parker');

INSERT 0 4


INSERT INTO log (id, worker_id) VALUES
(1, 1),
(2, 2),
(3, 1);

INSERT 0 3


INSERT INTO log_item (id, log_id, company_id) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 2, 1),
(4, 2, 3),
(5, 3, 1);

INSERT 0 5


--
-- Overly complex!
--

SELECT 
  DISTINCT
  ARRAY_TO_STRING
  (
    ARRAY_AGG
    (
      DISTINCT CONCAT(w.firstname, ' ', w.lastname) 
    ), 
    ', '
  ) AS "The workders"
FROM log_item li
INNER JOIN log l ON li.log_id = l.id 
INNER JOIN worker w ON l.worker_id = w.id
WHERE li.company_id = 1;


The workders


Frantisek Smith, Peter Duff



SELECT 1


--
-- Simplified
--

SELECT
  DISTINCT
  STRING_AGG
  (
    DISTINCT CONCAT(w.firstname, ' ', w.lastname), ', '
  ) AS "The workers"
FROM log_item li
INNER JOIN log l ON li.log_id = l.id 
INNER JOIN worker w ON l.worker_id = w.id
WHERE li.company_id = 1;


The workers


Frantisek Smith, Peter Duff



SELECT 1