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 ships (
    id SERIAL PRIMARY KEY,
    name text
);
INSERT INTO "ships"("id","name")
VALUES
(185,E'Royal Princess'),
(186,E'Sea Princess');
2 rows affected

CREATE TABLE ports (
    id SERIAL PRIMARY KEY,
    name text
);
INSERT INTO "ports"("id","name")
VALUES
(167,E'Hobart, Tasmania, Australia'),
(640,E'Melbourne, Australia'),
(372,E'Sydney, Australia'),
(451,E'Dunedin, New Zealand'),
(326,E'Akaroa, New Zealand'),
(168,E'Wellington, New Zealand'),
(657,E'Tauranga (Rotorua), New Zealand');
7 rows affected

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name text
);
INSERT INTO users("id","name")
VALUES
(1,E'mike'),
(2,E'mel');
2 rows affected

CREATE TABLE postings (
    id SERIAL PRIMARY KEY,
    ship_id integer,
    user_id integer,
    start_date date,
    end_date date,
    foreign key (user_id) references users(id),
    foreign key (ship_id) references ships(id));
INSERT INTO "postings"("id","ship_id","user_id","start_date","end_date")
VALUES
(1,185,1,E'2018-01-01',E'2018-01-15'),
(2,186,2,E'2018-01-04',E'2018-01-15');
2 rows affected

CREATE TABLE calls (
    id SERIAL PRIMARY KEY,
    arrive_date timestamp without time zone,
    depart_date timestamp without time zone,
    ship_id integer,
    port_id integer,
    foreign key (ship_id) references ships(id),
    foreign key (port_id) references ports(id));
INSERT INTO "calls"("id","arrive_date","depart_date","ship_id","port_id")
VALUES
(32483,E'2018-11-01 07:00:00',E'2018-11-01 17:00:00',185,167),
(32484,E'2018-11-03 08:00:00',E'2018-11-03 18:00:00',185,640),
(32485,E'2018-11-05 06:00:00',E'2018-11-05 18:00:00',185,372),
(32486,E'2018-11-09 08:00:00',E'2018-11-09 18:00:00',185,451),
(32487,E'2018-11-09 08:00:00',E'2018-11-09 18:00:00',186,451),
(32488,E'2018-11-10 08:00:00',E'2018-11-10 18:00:00',185,326),
(32489,E'2018-11-11 08:00:00',E'2018-11-11 18:00:00',185,168),
(32410,E'2018-11-13 07:00:00',E'2018-11-13 18:00:00',185,657);
8 rows affected

select
  ports.name,
  calls.arrive_date,
  calls.depart_date,
  array_agg(users.id),
  array_agg(ports.id)
from
  calls
  inner join ships on calls.ship_id = ships.id
  inner join ports on calls.port_id = ports.id
  left outer join postings on 
             (calls.ship_id = postings.ship_id and calls.ship_id = ships.id)
  inner join users on postings.user_id = users.id
group by
  ports.name,
  calls.arrive_date,
  calls.depart_date


name
arrive_date
depart_date
array_agg
array_agg


Akaroa, New Zealand
2018-11-10 08:00:00
2018-11-10 18:00:00
{1}
{326}

Dunedin, New Zealand
2018-11-09 08:00:00
2018-11-09 18:00:00
{1,2}
{451,451}

Hobart, Tasmania, Australia
2018-11-01 07:00:00
2018-11-01 17:00:00
{1}
{167}

Melbourne, Australia
2018-11-03 08:00:00
2018-11-03 18:00:00
{1}
{640}

Sydney, Australia
2018-11-05 06:00:00
2018-11-05 18:00:00
{1}
{372}

Tauranga (Rotorua), New Zealand
2018-11-13 07:00:00
2018-11-13 18:00:00
{1}
{657}

Wellington, New Zealand
2018-11-11 08:00:00
2018-11-11 18:00:00
{1}
{168}

name	arrive_date	depart_date	array_agg	array_agg
Akaroa, New Zealand	2018-11-10 08:00:00	2018-11-10 18:00:00	{1}	{326}
Dunedin, New Zealand	2018-11-09 08:00:00	2018-11-09 18:00:00	{1,2}	{451,451}
Hobart, Tasmania, Australia	2018-11-01 07:00:00	2018-11-01 17:00:00	{1}	{167}
Melbourne, Australia	2018-11-03 08:00:00	2018-11-03 18:00:00	{1}	{640}
Sydney, Australia	2018-11-05 06:00:00	2018-11-05 18:00:00	{1}	{372}
Tauranga (Rotorua), New Zealand	2018-11-13 07:00:00	2018-11-13 18:00:00	{1}	{657}
Wellington, New Zealand	2018-11-11 08:00:00	2018-11-11 18:00:00	{1}	{168}