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 pieces (
    id SERIAL PRIMARY KEY,
    title VARCHAR(100) NOT NULL
);

CREATE TABLE venues (
    id SERIAL PRIMARY KEY,
    title VARCHAR(100) NOT NULL
);

CREATE TABLE seasons (
    id SERIAL PRIMARY KEY,
    title varchar(100) NOT NULL
);

CREATE TABLE performances (
    id SERIAL PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    date DATE NOT NULL,
    piece_id INT NOT NULL,
    venue_id INT NOT NULL,    
    season_id INT NOT NULL,
		  
	CONSTRAINT fk_piece FOREIGN KEY(piece_id) REFERENCES pieces(id),    
	CONSTRAINT fk_season FOREIGN KEY(season_id) REFERENCES seasons(id),
	CONSTRAINT fk_venue FOREIGN KEY(venue_id) REFERENCES venues(id)
);

CREATE TABLE



CREATE TABLE



CREATE TABLE



CREATE TABLE


INSERT INTO pieces (title) VALUES ('Alice’s Piece'), ('Bob’s Piece'), ('Charlie’s Piece');
INSERT INTO seasons (title) VALUES ('Season 1980/81'), ('Season 1981/82'), ('Season 1982/83');
INSERT INTO venues (title) VALUES ('Old theater New York'), ('Globe Theatre London'), ('Edinburgh Congress Hall');

INSERT 0 3



INSERT 0 3



INSERT 0 3


INSERT INTO performances (title, date, piece_id, venue_id, season_id) VALUES
('Performance 1', '1981-01-01', 1, 1, 1),
('Performance 2', '1981-01-01', 2, 1, 1),
('Performance 3', '1981-01-01', 3, 1, 1),
('Performance 4', '1981-01-05', 2, 2, 1),
('Performance 5', '1981-01-08', 2, 3, 1),
('Performance 6', '1982-01-01', 1, 1, 2),
('Performance 7', '1982-01-01', 2, 1, 2),
('Performance 8', '1982-01-02', 3, 1, 2),
('Performance 9', '1982-01-05', 2, 2, 2),
('Performance 10', '1982-01-08', 2, 3, 2);

INSERT 0 10


SELECT
    performances.venue_id,
    performances.season_id,
    STRING_AGG(DISTINCT performances.piece_id::TEXT, ', ') AS piece_ids,
    STRING_AGG(DISTINCT performances.date::TEXT, ', ') AS dates
FROM
    performances
GROUP BY
    performances.venue_id,
    performances.season_id


venue_id
season_id
piece_ids
dates


1
1
1, 2, 3
1981-01-01

1
2
1, 2, 3
1982-01-01, 1982-01-02

2
1
2
1981-01-05

2
2
2
1982-01-05

3
1
2
1981-01-08

3
2
2
1982-01-08



SELECT 6

venue_id	season_id	piece_ids	dates
1	1	1, 2, 3	1981-01-01
1	2	1, 2, 3	1982-01-01, 1982-01-02
2	1	2	1981-01-05
2	2	2	1982-01-05
3	1	2	1981-01-08
3	2	2	1982-01-08