Postgres 11

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 test_table (
  sku               text,
  manufacturer_name text,
  price             float8,
  stock             int
);


INSERT INTO test_table VALUES
  ('sku1', 'Manufacturer1', 110.00, 22)
, ('sku1', 'Manufacturer2', 120.00, 15)
, ('sku1', 'Manufacturer3', 130.00, 1)
, ('sku1', 'Manufacturer3', 30.00, 11)
, ('sku2', 'Manufacturer1', 10.00, 2)
, ('sku2', 'Manufacturer2', 9.00,  3)
, ('sku3', 'Manufacturer2', 21.00, 3)
, ('sku3', 'Manufacturer2', 1.00, 7)
, ('sku3', 'Manufacturer3', 19.00, 5);

CREATE TYPE price_stock AS (price float8, stock int);
9 rows affected

SELECT sku
     , (man1).price, (man1).stock
     , (man2).price, (man2).stock
     , (man3).price, (man3).stock
FROM   crosstab(
   'SELECT sku, manufacturer_name, (price, stock)::price_stock
    FROM   test_table t
    ORDER  BY 1,2'
  , $$VALUES ('Manufacturer1'),('Manufacturer2'),('Manufacturer3')$$
    )
       AS ct (sku text
            , man1 price_stock
            , man2 price_stock
            , man3 price_stock
    );


sku
price
stock
price
stock
price
stock


sku1
110
22
120
15
30
11

sku2
10
2
9
3
null
null

sku3
null
null
1
7
19
5


SELECT sku
     , (man1).price, (man1).stock
     , (man2).price, (man2).stock
     , (man3).price, (man3).stock
FROM   crosstab(
   'SELECT sku, manufacturer_name, t
    FROM   test_table t
    ORDER  BY 1,2'
  , $$VALUES ('Manufacturer1'),('Manufacturer2'),('Manufacturer3')$$
    )
       AS ct (sku text
            , man1 test_table
            , man2 test_table
            , man3 test_table
    );


sku
price
stock
price
stock
price
stock


sku1
110
22
120
15
30
11

sku2
10
2
9
3
null
null

sku3
null
null
1
7
19
5

sku	price	stock	price	stock	price	stock
sku1	110	22	120	15	30	11
sku2	10	2	9	3	null	null
sku3	null	null	1	7	19	5