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 product_info
    ("id" int, "date" varchar(6), "product" varchar(6), "cost" int)
;
    
INSERT INTO product_info
    ("id", "date", "product", "cost")
VALUES
    (1, 'Aug-23', 'Laptop', 250),
    (1, 'Aug-23', 'Phone', 100),
    (1, 'Aug-23', 'Keypad', 50),
    (1, 'Sep-23', 'Laptop', 260),
    (1, 'Sep-23', 'Phone', 100),
    (1, 'Sep-23', 'Keypad', 60),
    (1, 'Sep-23', 'Ipad', 200),
    (2, 'Aug-23', 'Laptop', 220),
    (2, 'Aug-23', 'Phone', 100),
    (2, 'Aug-23', 'Keypad', 80),
    (2, 'Sep-23', 'Laptop', 100),
    (2, 'Sep-23', 'Phone', 100),
    (2, 'Sep-23', 'Keypad', 80),
    (2, 'Sep-23', 'Ipad', 90)
;

CREATE TABLE



INSERT 0 14


SELECT split_part(id, '%', 1) as id,
  split_part(id, '%', 2) as date,
  	    "Ipad" ,
    "Laptop" ,
    "Phone" ,
    "Keypad" 
  FROM crosstab(
    'SELECT  CONCAT(id,''%'',date) as id, product, sum(cost)
     FROM product_info
     GROUP BY 1, 2
     ORDER BY 1, 2'
,
    'SELECT DISTINCT product FROM product_info ORDER BY 1'
) as cte(
    id text,
    "Ipad" numeric,
    "Laptop" numeric,
    "Phone" numeric,
    "Keypad" numeric
)


id
date
Ipad
Laptop
Phone
Keypad


1
Aug-23
null
50
250
100

1
Sep-23
200
60
260
100

2
Aug-23
null
80
220
100

2
Sep-23
90
80
100
100



SELECT 4


SELECT
  id,date,
  SUM(CASE WHEN product = 'Ipad' THEN cost END ) as "Ipad",
  SUM(CASE WHEN product = 'Laptop' THEN cost END ) as "Laptop",
  SUM(CASE WHEN product = 'Phone' THEN cost END ) as "Phone",
  SUM(CASE WHEN product = 'Keypad' THEN cost END ) as "Keypad"
FROM 
product_info
GROUP BY id,date
ORDER BY id,date


id
date
Ipad
Laptop
Phone
Keypad


1
Aug-23
null
250
100
50

1
Sep-23
200
260
100
60

2
Aug-23
null
220
100
80

2
Sep-23
90
100
100
80



SELECT 4

id	date	Ipad	Laptop	Phone	Keypad
1	Aug-23	null	50	250	100
1	Sep-23	200	60	260	100
2	Aug-23	null	80	220	100
2	Sep-23	90	80	100	100