Postgres 13

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 (
  product_id  integer primary key,
  reference   varchar,
  price       decimal(13,4)
);

insert into product (product_id, reference, price) values 
(1001, 'MX-232',    100.00),
(1011, 'AX-232',     20.00),
(1003, 'KKK 11',     11.00),
(1004, 'OXS SUPER',   0.35),
(1005, 'ROR-MOT',   200.00),
(1006, '234PPP',     30.50),
(1007, 'T555-NS',   110.25),
(1008, 'LM234-XS',  101.20),
(1009, 'MOTOR-22',   12.50),
(1010, 'MOTOR-11',   30.00),
(1002, 'XUL-XUL1',   40.00);
11 rows affected

CREATE OR REPLACE FUNCTION list_products (VARIADIC tid int[]) 
  RETURNS table (
   id        integer,
   reference varchar,
   price     decimal(13,4)
   )
  LANGUAGE sql STABLE AS
$func$
  SELECT product_id, p.reference, p.price
  FROM   unnest(tid) WITH ORDINALITY AS t(product_id, ord)
  LEFT   JOIN   product p USING (product_id)
  ORDER  BY t.ord
$func$;
SELECT * FROM list_products (VARIADIC '{1011,1010,1009,1008,1007,1006,1005,1004,1003,1002,1001}');


id
reference
price


1011
AX-232
20.0000

1010
MOTOR-11
30.0000

1009
MOTOR-22
12.5000

1008
LM234-XS
101.2000

1007
T555-NS
110.2500

1006
234PPP
30.5000

1005
ROR-MOT
200.0000

1004
OXS SUPER
0.3500

1003
KKK 11
11.0000

1002
XUL-XUL1
40.0000

1001
MX-232
100.0000


SELECT * FROM list_products (1011,1010,1009,1008,1007,1006,1005,1004,1003,1002,1001);


id
reference
price


1011
AX-232
20.0000

1010
MOTOR-11
30.0000

1009
MOTOR-22
12.5000

1008
LM234-XS
101.2000

1007
T555-NS
110.2500

1006
234PPP
30.5000

1005
ROR-MOT
200.0000

1004
OXS SUPER
0.3500

1003
KKK 11
11.0000

1002
XUL-XUL1
40.0000

1001
MX-232
100.0000


-- ID 666 got deleted?
SELECT * FROM list_products (1011,1010,666,1009,1008,1007,1006,1005,1004,1003,1002,1001);


id
reference
price


1011
AX-232
20.0000

1010
MOTOR-11
30.0000

666
null
null

1009
MOTOR-22
12.5000

1008
LM234-XS
101.2000

1007
T555-NS
110.2500

1006
234PPP
30.5000

1005
ROR-MOT
200.0000

1004
OXS SUPER
0.3500

1003
KKK 11
11.0000

1002
XUL-XUL1
40.0000

1001
MX-232
100.0000

id	reference	price
1011	AX-232	20.0000
1010	MOTOR-11	30.0000
1009	MOTOR-22	12.5000
1008	LM234-XS	101.2000
1007	T555-NS	110.2500
1006	234PPP	30.5000
1005	ROR-MOT	200.0000
1004	OXS SUPER	0.3500
1003	KKK 11	11.0000
1002	XUL-XUL1	40.0000
1001	MX-232	100.0000