Postgres 9.5

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 9.5.24 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit


CREATE TABLE parent 
(
  parent_id INTEGER PRIMARY KEY, 
  parent_name VARCHAR(20
));
CREATE TABLE child 
(
  child_id INTEGER PRIMARY KEY, 
  parent_id INTEGER, 
  child_name VARCHAR(20),
  
  CONSTRAINT child_parent_fk FOREIGN KEY (parent_id)
    REFERENCES parent (parent_id)
);

CREATE TABLE toy 
(
  toy_id INTEGER PRIMARY KEY, 
  toy_name VARCHAR(40)
);
CREATE TABLE child_toy 
(
  ct_child_id INTEGER, 
  ct_toy_id INTEGER,
  
  CONSTRAINT ct_child_fk FOREIGN KEY (ct_child_id)
    REFERENCES child (child_id),

  CONSTRAINT ct_toy_fk   FOREIGN KEY (ct_toy_id)
    REFERENCES toy (toy_id)

);
INSERT INTO parent VALUES (1, 'Fred');
INSERT INTO parent VALUES (2, 'Mary');
INSERT INTO parent VALUES (3, 'Joe');
1 rows affected

1 rows affected

1 rows affected

INSERT INTO child VALUES (1, 1, 'Jimmy');
INSERT INTO child VALUES (2, 1, 'Gerry');
INSERT INTO child VALUES (3, 1, 'Billy');
INSERT INTO child VALUES (4, 2, 'Mick');
INSERT INTO child VALUES (5, 2, 'James');
INSERT INTO child VALUES (6, 2, 'Larry');
INSERT INTO child VALUES (7, 3, 'Jim');
INSERT INTO child VALUES (8, 3, 'Jack');
INSERT INTO child VALUES (9, 3, 'Helen');
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

INSERT INTO toy VALUES (1, 'Action Man');
INSERT INTO toy VALUES (2, 'Barbie');
INSERT INTO toy VALUES (3, 'Monopoly');
INSERT INTO toy VALUES (4, 'Cluedo');
INSERT INTO toy VALUES (5, 'Hurley');
INSERT INTO toy VALUES (6, 'Soccer ball');
INSERT INTO toy VALUES (7, 'Rugby ball');
INSERT INTO toy VALUES (8, 'Tennis ball');
INSERT INTO toy VALUES (9, 'Tennis raquet');
INSERT INTO toy VALUES (10, 'Tricycle');
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

-- child_toy:

INSERT INTO child_toy VALUES(1, 2);
INSERT INTO child_toy VALUES(1, 3);
INSERT INTO child_toy VALUES(1, 4);
INSERT INTO child_toy VALUES(1, 5);
INSERT INTO child_toy VALUES(2, 3);
INSERT INTO child_toy VALUES(2, 9);
INSERT INTO child_toy VALUES(3, 1);
INSERT INTO child_toy VALUES(3, 2);
INSERT INTO child_toy VALUES(4, 6);
INSERT INTO child_toy VALUES(5, 7);
INSERT INTO child_toy VALUES(6, 9);
INSERT INTO child_toy VALUES(7, 3);
INSERT INTO child_toy VALUES(8, 4);
INSERT INTO child_toy VALUES(9, 5);
INSERT INTO child_toy VALUES(9, 6);
INSERT INTO child_toy VALUES(9, 7);
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

SELECT 
  p.parent_name, 
  c.child_name,
  t.toy_name
FROM parent p
  INNER JOIN child c
  ON p.parent_id = c.parent_id
  INNER JOIN child_toy ct 
  ON c.child_id = ct.ct_child_id
  INNER JOIN toy t
  ON ct.ct_toy_id = t.toy_id;


parent_name
child_name
toy_name


Fred
Jimmy
Barbie

Fred
Jimmy
Monopoly

Fred
Jimmy
Cluedo

Fred
Jimmy
Hurley

Fred
Gerry
Monopoly

Fred
Gerry
Tennis raquet

Fred
Billy
Action Man

Fred
Billy
Barbie

Mary
Mick
Soccer ball

Mary
James
Rugby ball

Mary
Larry
Tennis raquet

Joe
Jim
Monopoly

Joe
Jack
Cluedo

Joe
Helen
Hurley

Joe
Helen
Soccer ball

Joe
Helen
Rugby ball


SELECT 
  p.parent_name, 
  c.child_name,
  COUNT(t.toy_id) AS cnt
FROM parent p
  INNER JOIN child c
  ON p.parent_id = c.parent_id
  INNER JOIN child_toy ct 
  ON c.child_id = ct.ct_child_id
  INNER JOIN toy t
  ON ct.ct_toy_id = t.toy_id
GROUP BY p.parent_name, c.child_name
ORDER BY cnt DESC;


parent_name
child_name
cnt


Fred
Jimmy
4

Joe
Helen
3

Fred
Gerry
2

Fred
Billy
2

Joe
Jack
1

Mary
Larry
1

Mary
Mick
1

Mary
James
1

Joe
Jim
1


SELECT 
  p.parent_name, 
  c.child_name,
  COUNT(t.toy_id) cnt
FROM parent p
  INNER JOIN child c
  ON p.parent_id = c.parent_id
  INNER JOIN child_toy ct 
  ON c.child_id = ct.ct_child_id
  INNER JOIN toy t
  ON ct.ct_toy_id = t.toy_id
GROUP BY p.parent_name, c.child_name
HAVING COUNT(t.toy_id) > 3
ORDER BY cnt DESC;


parent_name
child_name
cnt


Fred
Jimmy
4

parent_name	child_name	toy_name
Fred	Jimmy	Barbie
Fred	Jimmy	Monopoly
Fred	Jimmy	Cluedo
Fred	Jimmy	Hurley
Fred	Gerry	Monopoly
Fred	Gerry	Tennis raquet
Fred	Billy	Action Man
Fred	Billy	Barbie
Mary	Mick	Soccer ball
Mary	James	Rugby ball
Mary	Larry	Tennis raquet
Joe	Jim	Monopoly
Joe	Jack	Cluedo
Joe	Helen	Hurley
Joe	Helen	Soccer ball
Joe	Helen	Rugby ball