Postgres 15

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 airport (
    airport_id serial PRIMARY KEY,
    name character varying(24) NOT NULL
);

CREATE TABLE area (
    areaid serial PRIMARY KEY,
    airport_id integer NOT NULL,
    name character varying(24) NOT NULL,
    CONSTRAINT fk_area_airport_id FOREIGN KEY (airport_id) REFERENCES airport (airport_id) ON UPDATE CASCADE ON DELETE SET NULL
);

CREATE TABLE booking (
    id serial PRIMARY KEY,
    areaid integer NOT NULL,
    booking_date timestamp without time zone NOT NULL DEFAULT CURRENT_DATE,
    price double precision,
    commission double precision,
    CONSTRAINT fk_booking_areaid FOREIGN KEY (areaid) REFERENCES area (areaid) ON UPDATE CASCADE ON DELETE SET NULL
);

INSERT INTO airport VALUES 
  (1, 'Frankfurt')
, (2, 'Paris')
, (3, 'Amsterdam')
;

INSERT INTO area VALUES 
  (1, 2, 'name 1')
, (2, 2, 'name 2')
, (3, 3, 'name 3')
, (4, 3, 'name 4')
;

INSERT INTO booking VALUES 
  (1, 1, '2022-09-01T10:00',  70, 12)
, (2, 2, '2022-09-02T11:00',  60, 12)
, (3, 3, '2022-09-01T20:00',  50, 15)
, (4, 3, '2022-09-01T01:00', 110, 15)
, (5, 4, '2022-09-01T22:00',  90, 14)
, (6, 1, '2022-09-01T19:00',  65, 15)
, (7, 2, '2022-09-01T12:00',  84, 16)
;

CREATE TABLE



CREATE TABLE



CREATE TABLE



INSERT 0 3



INSERT 0 4



INSERT 0 7


-- no data for airport 2, area 3, so no results
SELECT ar.name AS airport, b.com
FROM   airport ar
LEFT   JOIN LATERAL (
   SELECT b.price * b.commission / 100.0 AS com
   FROM   area    p
   JOIN   booking b USING (areaid)
   WHERE  p.airport_id = ar.airport_id  -- my assumption
   AND    p.areaid = 3
   AND    b.booking_date >= '2022-09-01'
   AND    b.booking_date <  '2022-10-01'
   ) b ON true
WHERE  ar.airport_id = 2;


airport
com


Paris
null



SELECT 1


-- area 2 has matching rows
SELECT ar.name AS airport, b.com
FROM   airport ar
LEFT   JOIN LATERAL (
   SELECT b.price * b.commission / 100.0 AS com
   FROM   area    p
   JOIN   booking b USING (areaid)
   WHERE  p.airport_id = ar.airport_id  -- my assumption
   AND    p.areaid = 2
   AND    b.booking_date >= '2022-09-01'
   AND    b.booking_date <  '2022-10-01'
   ) b ON true
WHERE  ar.airport_id = 2;


airport
com


Paris
7.2

Paris
13.44



SELECT 2


-- you really want the sum?
SELECT ar.name AS airport, b.com
FROM   airport ar
LEFT   JOIN LATERAL (
   SELECT sum(b.price * b.commission) / 100.0 AS com
   FROM   area    p
   JOIN   booking b USING (areaid)
   WHERE  p.airport_id = ar.airport_id  -- my assumption
   AND    p.areaid = 2
   AND    b.booking_date >= '2022-09-01'
   AND    b.booking_date <  '2022-10-01'
   ) b ON true
WHERE  ar.airport_id = 2;


airport
com


Paris
20.64



SELECT 1


-- since that is a single result value, a (correlated!) subquery works now
SELECT ar.name AS airport
     , (SELECT sum(b.price * b.commission) / 100.0
        FROM   area    p
        JOIN   booking b USING (areaid)
        WHERE  p.airport_id = ar.airport_id
        AND    p.areaid = 2
        AND    b.booking_date >= '2022-09-01'
        AND    b.booking_date <  '2022-10-01') AS com
FROM   airport ar
WHERE  ar.airport_id = 2;


airport
com


Paris
20.64



SELECT 1