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


CREATE TABLE theatre
( 
  theatre_id   INTEGER      NOT NULL PRIMARY KEY,
  theatre_name VARCHAR (25) NOT NULL
);
INSERT INTO theatre VALUES (1, 'Theatre_1'), (2, 'Theatre_2'); SELECT * FROM theatre;
2 rows affected



theatre_id
theatre_name


1
Theatre_1

2
Theatre_2


CREATE TABLE show
(
  show_id         INTEGER      NOT NULL PRIMARY KEY,
  show_theatre_id INTEGER      NOT NULL,
  show_name       VARCHAR (25) NOT NULL,
  CONSTRAINT show_theatre_fk FOREIGN KEY (show_theatre_id) REFERENCES theatre (theatre_id)
);
INSERT INTO show (show_id, show_theatre_id, show_name) VALUES 
(10, 1, 'show_10'), (11, 1, 'show_11'), (20, 2, 'show_20'), (21, 2, 'show_21');
SELECT * FROM show;
4 rows affected



show_id
show_theatre_id
show_name


10
1
show_10

11
1
show_11

20
2
show_20

21
2
show_21


CREATE TABLE ticket
(
  ticket_id SERIAL PRIMARY KEY,
  ticket_show_id INTEGER NOT NULL,
  CONSTRAINT ticket_show_fk FOREIGN KEY (ticket_show_id) REFERENCES show (show_id),
  price INT NOT NULL
  --
  -- Various other fields - seat_no, is_sold, discount... whatever
);
INSERT INTO ticket (ticket_show_id, price) VALUES 
(10, 10), (10, 10), (10, 10), (10, 10), (10, 10),                     -- 5 tickets for show 10 
(11, 11), (11, 11), (11, 11), (11, 11), (11, 11), (11, 11), (11, 11), -- 7/show 11 &c
(20, 20), (20, 20), (20, 20), (20, 20), (20, 20),                     
(20, 20), (20, 20), (20, 20), (20, 20), (20, 20),                     -- 10
(21, 21), (21, 21), (21, 21),                                         -- 3
(10, 500), (10, 500), (10, 500), (10, 500);        -- 4 EXPENSIVE tickets for show_10;
SELECT * FROM ticket t ORDER BY t.ticket_show_id, t.price;
29 rows affected



ticket_id
ticket_show_id
price


1
10
10

2
10
10

3
10
10

4
10
10

5
10
10

29
10
500

26
10
500

27
10
500

28
10
500

10
11
11

11
11
11

12
11
11

6
11
11

7
11
11

8
11
11

9
11
11

17
20
20

18
20
20

19
20
20

20
20
20

21
20
20

22
20
20

14
20
20

15
20
20

16
20
20

13
20
20

23
21
21

24
21
21

25
21
21


SELECT 
  th.theatre_id, th.theatre_name AS th_name,
  s.show_id AS s_id, s.show_theatre_id AS s_th, s.show_name AS s_name,
  t.ticket_id AS t_id, t.ticket_show_id AS t_show, t.price AS t_p
FROM 
  theatre th
JOIN show s ON th.theatre_id = s.show_theatre_id
JOIN ticket t ON s.show_id = t.ticket_show_id
ORDER BY th.theatre_id, s.show_id, t.price;


theatre_id
th_name
s_id
s_th
s_name
t_id
t_show
t_p


1
Theatre_1
10
1
show_10
1
10
10

1
Theatre_1
10
1
show_10
2
10
10

1
Theatre_1
10
1
show_10
3
10
10

1
Theatre_1
10
1
show_10
4
10
10

1
Theatre_1
10
1
show_10
5
10
10

1
Theatre_1
10
1
show_10
29
10
500

1
Theatre_1
10
1
show_10
26
10
500

1
Theatre_1
10
1
show_10
27
10
500

1
Theatre_1
10
1
show_10
28
10
500

1
Theatre_1
11
1
show_11
10
11
11

1
Theatre_1
11
1
show_11
11
11
11

1
Theatre_1
11
1
show_11
12
11
11

1
Theatre_1
11
1
show_11
6
11
11

1
Theatre_1
11
1
show_11
7
11
11

1
Theatre_1
11
1
show_11
8
11
11

1
Theatre_1
11
1
show_11
9
11
11

2
Theatre_2
20
2
show_20
17
20
20

2
Theatre_2
20
2
show_20
18
20
20

2
Theatre_2
20
2
show_20
19
20
20

2
Theatre_2
20
2
show_20
20
20
20

2
Theatre_2
20
2
show_20
21
20
20

2
Theatre_2
20
2
show_20
22
20
20

2
Theatre_2
20
2
show_20
14
20
20

2
Theatre_2
20
2
show_20
15
20
20

2
Theatre_2
20
2
show_20
16
20
20

2
Theatre_2
20
2
show_20
13
20
20

2
Theatre_2
21
2
show_21
23
21
21

2
Theatre_2
21
2
show_21
24
21
21

2
Theatre_2
21
2
show_21
25
21
21


SELECT 
  th.theatre_name AS "Theatre name", s.show_name AS "Show name",

  -- We don't care about the shows - we just want the MONEY!!!
  -- s.show_id AS s_id, s.show_theatre_id AS s_th, s.show_name AS s_name,
  -- t.ticket_id AS t_id, t.ticket_show_id AS t_show, 

  -- SUM(t.price) OVER (PARTITION BY th.theatre_id ORDER BY th.theatre_id),

  SUM(t.price) AS "Sales/show"

FROM 
  theatre th
JOIN show s ON th.theatre_id = s.show_theatre_id
JOIN ticket t ON s.show_id = t.ticket_show_id
GROUP BY th.theatre_name, s.show_name
ORDER BY th.theatre_name, s.show_name;


Theatre name
Show name
Sales/show


Theatre_1
show_10
2050

Theatre_1
show_11
77

Theatre_2
show_20
200

Theatre_2
show_21
63


SELECT 
  th.theatre_name AS "T. name", s.show_id AS "Show name", t.price AS "Tkt price",

  -- We don't care about the shows - we just want the MONEY!!!
  -- s.show_id AS s_id, s.show_theatre_id AS s_th, s.show_name AS s_name,
  -- t.ticket_id AS t_id, t.ticket_show_id AS t_show, 

  SUM(t.price) OVER (PARTITION BY th.theatre_id) AS "Sales/T.",

  SUM(t.price) OVER (PARTITION BY s.show_id ORDER BY th.theatre_id, s.show_id) AS "Sales/show",


  COUNT(t.price) OVER (PARTITION BY th.theatre_id, show_id) "Tkts/show",


  SUM(t.price) AS "Sales/ticket"  -- Seen above!
   
FROM 
  theatre th
JOIN show s ON th.theatre_id = s.show_theatre_id
JOIN ticket t ON s.show_id = t.ticket_show_id
GROUP BY th.theatre_id, th.theatre_name, s.show_id, t.price
ORDER BY th.theatre_name, s.show_id, t.price DESC;


T. name
Show name
Tkt price
Sales/T.
Sales/show
Tkts/show
Sales/ticket


Theatre_1
10
500
521
510
2
2000

Theatre_1
10
10
521
510
2
50

Theatre_1
11
11
521
11
1
77

Theatre_2
20
20
41
20
1
200

Theatre_2
21
21
41
21
1
63


SELECT 
  th.theatre_name AS "T. name", s.show_id,

  -- We don't care about the shows - we just want the MONEY!!!
  -- s.show_id AS s_id, s.show_theatre_id AS s_th, s.show_name AS s_name,
  -- t.ticket_id AS t_id, t.ticket_show_id AS t_show, 
  -- SUM(t.price) OVER (PARTITION BY th.theatre_id
  --   ORDER BY th.theatre_name, s.show_id, t.price DESC),
 
  -- SUM(t.price) OVER (PARTITION BY th.theatre_id) AS "Sales/T.",

  SUM(t.price) OVER (PARTITION BY s.show_id ORDER BY th.theatre_id, s.show_id) AS "Sales/show",

  SUM(t.price) OVER (PARTITION BY th.theatre_id) AS "Sales/T",

  COUNT(t.price) OVER (PARTITION BY th.theatre_id, show_id) "No. of difft tkts/show",

  SUM(t.price) AS "Tot sales/ticket"
  
FROM 
  theatre th
JOIN show s ON th.theatre_id = s.show_theatre_id
JOIN ticket t ON s.show_id = t.ticket_show_id
GROUP BY th.theatre_id, th.theatre_name, s.show_id
ORDER BY th.theatre_name, s.show_id, t.price DESC;
ERROR:  column "t.price" must appear in the GROUP BY clause or be used in an aggregate function
LINE 12:   SUM(t.price) OVER (PARTITION BY s.show_id ORDER BY th.thea...
               ^


  SELECT COUNT(t.ticket_show_id)
  FROM show s
  JOIN ticket t ON s.show_id = t.ticket_show_id
  GROUP BY s.show_id


count


9

7

3

10


SELECT th.theatre_id, s.show_name,
(
  SELECT COUNT(t.ticket_show_id)
  FROM show s
  JOIN ticket t ON s.show_id = t.ticket_show_id
  GROUP BY s.show_id
) AS t
FROM theatre th
JOIN show s ON th.theatre_id = s.show_theatre_id
ORDER BY th.theatre_id, s.show_name;
ERROR:  more than one row returned by a subquery used as an expression

ticket_id	ticket_show_id	price
1	10	10
2	10	10
3	10	10
4	10	10
5	10	10
29	10	500
26	10	500
27	10	500
28	10	500
10	11	11
11	11	11
12	11	11
6	11	11
7	11	11
8	11	11
9	11	11
17	20	20
18	20	20
19	20	20
20	20	20
21	20	20
22	20	20
14	20	20
15	20	20
16	20	20
13	20	20
23	21	21
24	21	21
25	21	21

theatre_id	th_name	s_id	s_th	s_name	t_id	t_show	t_p
1	Theatre_1	10	1	show_10	1	10	10
1	Theatre_1	10	1	show_10	2	10	10
1	Theatre_1	10	1	show_10	3	10	10
1	Theatre_1	10	1	show_10	4	10	10
1	Theatre_1	10	1	show_10	5	10	10
1	Theatre_1	10	1	show_10	29	10	500
1	Theatre_1	10	1	show_10	26	10	500
1	Theatre_1	10	1	show_10	27	10	500
1	Theatre_1	10	1	show_10	28	10	500
1	Theatre_1	11	1	show_11	10	11	11
1	Theatre_1	11	1	show_11	11	11	11
1	Theatre_1	11	1	show_11	12	11	11
1	Theatre_1	11	1	show_11	6	11	11
1	Theatre_1	11	1	show_11	7	11	11
1	Theatre_1	11	1	show_11	8	11	11
1	Theatre_1	11	1	show_11	9	11	11
2	Theatre_2	20	2	show_20	17	20	20
2	Theatre_2	20	2	show_20	18	20	20
2	Theatre_2	20	2	show_20	19	20	20
2	Theatre_2	20	2	show_20	20	20	20
2	Theatre_2	20	2	show_20	21	20	20
2	Theatre_2	20	2	show_20	22	20	20
2	Theatre_2	20	2	show_20	14	20	20
2	Theatre_2	20	2	show_20	15	20	20
2	Theatre_2	20	2	show_20	16	20	20
2	Theatre_2	20	2	show_20	13	20	20
2	Theatre_2	21	2	show_21	23	21	21
2	Theatre_2	21	2	show_21	24	21	21
2	Theatre_2	21	2	show_21	25	21	21

Theatre name	Show name	Sales/show
Theatre_1	show_10	2050
Theatre_1	show_11	77
Theatre_2	show_20	200
Theatre_2	show_21	63

T. name	Show name	Tkt price	Sales/T.	Sales/show	Tkts/show	Sales/ticket
Theatre_1	10	500	521	510	2	2000
Theatre_1	10	10	521	510	2	50
Theatre_1	11	11	521	11	1	77
Theatre_2	20	20	41	20	1	200
Theatre_2	21	21	41	21	1	63

ticket_id	ticket_show_id	price
1	10	10
2	10	10
3	10	10
4	10	10
5	10	10
29	10	500
26	10	500
27	10	500
28	10	500
10	11	11
11	11	11
12	11	11
6	11	11
7	11	11
8	11	11
9	11	11
17	20	20
18	20	20
19	20	20
20	20	20
21	20	20
22	20	20
14	20	20
15	20	20
16	20	20
13	20	20
23	21	21
24	21	21
25	21	21

ticket_id	ticket_show_id	price
1	10	10
2	10	10
3	10	10
4	10	10
5	10	10
29	10	500
26	10	500
27	10	500
28	10	500
10	11	11
11	11	11
12	11	11
6	11	11
7	11	11
8	11	11
9	11	11
17	20	20
18	20	20
19	20	20
20	20	20
21	20	20
22	20	20
14	20	20
15	20	20
16	20	20
13	20	20
23	21	21
24	21	21
25	21	21