Postgres 16

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 EXTENSION tablefunc;

CREATE EXTENSION


CREATE TABLE ventas (
  product text
, month int
, amount int
);

INSERT INTO ventas VALUES 
  ('AA', 1, 100)
, ('AA', 1, 150)
, ('AA', 2, 200)
, ('AA', 2, 120)
, ('BB', 2, 180)
, ('BB', 2, 220)
, ('CC', 3,  80)
RETURNING *;

CREATE TABLE




product
month
amount


AA
1
100

AA
1
150

AA
2
200

AA
2
120

BB
2
180

BB
2
220

CC
3
80



INSERT 0 7


-- base query
SELECT month, product, sum(amount) AS amount
FROM   ventas
GROUP  BY 1, 2
ORDER  BY 1, 2;


month
product
amount


1
AA
250

2
AA
320

2
BB
400

3
CC
80



SELECT 4


-- generate crosstab() query
SELECT format(
$f$  -- begin dynamic query string
SELECT * FROM crosstab(
   $q$
   SELECT month, product, sum(amount)
   FROM   ventas
   GROUP  BY 1, 2
   ORDER  BY 1, 2
   $q$
 , $c$VALUES (%s)$c$
   ) AS ct(month int, %s);
$f$  -- end dynamic query string
            , string_agg(quote_literal(sub.product), '), (')
            , string_agg(quote_ident  (sub.product), ' int, ') || ' int'
				 )
FROM  (SELECT DISTINCT product FROM ventas ORDER BY 1) sub;


format


  -- begin dynamic query string
SELECT * FROM crosstab(
   $q$
   SELECT month, product, sum(amount)
   FROM   ventas
   GROUP  BY 1, 2
   ORDER  BY 1, 2
   $q$
 , $c$VALUES ('AA'), ('BB'), ('CC')$c$
   ) AS ct(month int, "AA" int, "BB" int, "CC" int);




SELECT 1


SELECT * FROM crosstab(
   $q$
   SELECT month, product, sum(amount)
   FROM   ventas
   GROUP  BY 1, 2
   ORDER  BY 1, 2
   $q$
 , $c$VALUES ('AA'), ('BB'), ('CC')$c$
   ) AS ct(month int, "AA" int, "BB" int, "CC" int);


month
AA
BB
CC


1
250
null
null

2
320
400
null

3
null
null
80



SELECT 3

month	AA	BB	CC
1	250	null	null
2	320	400	null
3	null	null	80