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?.

CREATE TABLE public.customerorder (
	order_id serial4 NOT NULL,
	customer_id int4 NOT NULL,
	order_placed_on timestamptz NOT NULL,
	order_description varchar(180) NOT NULL
);

CREATE TABLE


INSERT INTO public.customerorder
(order_id,customer_id, order_placed_on, order_description)
VALUES(51658260,2579927 ,'2022-11-10 16:56:48.750 +0100', 'TEST SAMPLE');

INSERT 0 1


CREATE OR REPLACE FUNCTION public.customerOrders
  (_customer_id integer
  , _startperiod timestamp with time zone
  , _endperiod timestamp with time zone
  ,  _sort_field_and_direction character varying
  , _limit integer
  , _offset integer
  , OUT id integer
  , OUT customerid integer
  , OUT description character varying
  , OUT placedon timestamp with time zone)
 RETURNS SETOF record
 LANGUAGE plpgsql
AS $function$
DECLARE
    f_string            TEXT;
    f_max_rows          INTEGER := 100;
BEGIN
  RETURN QUERY 
    SELECT order_id, customer_id, order_description, order_placed_on 
    FROM public.customerorder
    WHERE order_placed_on >= COALESCE(_startperiod, (select current_date - 30));
END;
$function$
;

CREATE FUNCTION


SELECT * FROM public.customerOrders('2579927',null::date,'2022-11-05'::date,'placedOn DESC','50','0');


id
customerid
description
placedon


51658260
2579927
TEST SAMPLE
2022-11-10 15:56:48.75+00



SELECT 1


SELECT * FROM public.customerOrders('2579927','2022-11-01'::date,'2022-11-05'::date,'placedOn DESC','50','0');


id
customerid
description
placedon


51658260
2579927
TEST SAMPLE
2022-11-10 15:56:48.75+00



SELECT 1


SELECT * FROM public.customerOrders('2579927','2022-11-11'::date,'2022-11-05'::date,'placedOn DESC','50','0');


id
customerid
description
placedon



SELECT 0