Postgres 9.6

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 car
(
  id serial constraint car_pk primary key,
  vendor_name varchar not null,
  model_name varchar not null,
  body_type varchar not null,
  specifications_name varchar not null,
  price int4 not null
);
INSERT INTO car(vendor_name, model_name, body_type, specifications_name, price) VALUES
('Peugeot', '408', 'Sedan', 'Allure 115hp brown', 1144000),
('LADA', 'Vesta', 'Sedan', 'Luxe seawave', 635000),
('Ford', 'Focus', 'Hatchback', 'Sync gray', 1109000),
('Ford', 'Focus', 'Sedan', 'Sync white', 1250800),
('LADA', 'Vesta', 'Sedan', 'Сlassic green', 631800),
('Audi', 'A4', 'Wagon', 'yellow', 2900000),
('Ford', 'Focus', 'Hatchback', 'Special tangerine', 1126000),
('LADA', 'Granta', 'Sedan', 'Comfort gray', 520000),
('LADA', 'Vesta', 'Sedan', 'Сomfort blue', 631100),
('Ford', 'Focus', 'Sedan', 'Trend blue', 1235000),
('LADA', 'Vesta', 'Wagon', 'Comfort orange', 679000),
('Audi', 'A4', 'Sedan', 'yellow', 2000000),
('LADA', 'Granta', 'Sedan', 'Luxe Prestige green', 576000),
('Peugeot', '408', 'Sedan', 'Active red', 1177000),
('Audi', 'A4', 'Sedan', 'yellow', 2000000),
('Ford', 'Focus', 'Sedan', 'Special tangerine', 1203000),
('LADA', 'Granta', 'Sedan', 'Luxe gray', 531000),
('Peugeot', '408', 'Sedan', 'Allure 150hp white', 1122000),
('Audi', 'A4', 'Wagon', 'gray', 2900000),
('LADA', 'Vesta', 'Wagon', 'Luxe white', 680000),
('Ford', 'Focus', 'Sedan', 'Special orange', 1211000),
('Ford', 'Focus', 'Hatchback', 'Special orange', 1125000),
('LADA', 'Vesta', 'Wagon', 'Comfort plum', 630000),
('Peugeot', '408', 'Sedan', 'Allure 150hp purple', 1125000),
('Audi', 'A3', 'HatchBack', 'white', 2000000),
('Ford', 'Focus', 'Hatchback', 'Special lemon', 1088000),
('LADA', 'Vesta', 'Wagon', 'Luxe blue', 699000),
('Ford', 'Focus', 'Sedan', 'Trend green', 1230000),
('LADA', 'Vesta', 'Sedan', 'Luxe dark green', 634000),
('Ford', 'Focus', 'Sedan', 'Sync gray', 1260000),
('LADA', 'Granta', 'Wagon', 'Comfort magenta', 566000),
('LADA', 'Granta', 'Sedan', 'Comfort red', 520000),
('LADA', 'Vesta', 'Sedan', 'Сlassic brown', 631000),
('Ford', 'Focus', 'Sedan', 'Special lemon', 1201000),
('Ford', 'Focus', 'Hatchback', 'Trend blue', 1065000),
('LADA', 'Vesta', 'Wagon', 'Luxe red', 679000),
('LADA', 'Granta', 'Wagon', 'Standart white', 520000),
('Audi', 'A4', 'Wagon', 'black', 3000000),
('LADA', 'Vesta', 'Sedan', 'Сomfort impressive', 641000),
('Ford', 'Focus', 'Sedan', 'Sync black', 1250000),
('LADA', 'Granta', 'Sedan', 'Standart black', 438000),
('Audi', 'A3', 'HatchBack', 'yellow', 2000000),
('LADA', 'Granta', 'Wagon', 'Standart black', 465030),
('LADA', 'Vesta', 'Sedan', 'Сlassic white', 638005),
('LADA', 'Granta', 'Wagon', 'Standart blue', 485000),
('LADA', 'Granta', 'Wagon', 'Comfort asphalt', 566000),
('Audi', 'A4', 'Wagon', 'white', 2900000),
('Ford', 'Focus', 'Hatchback', 'Trend white', 1027000),
('LADA', 'Granta', 'Sedan', 'Standart blue', 438000),
('LADA', 'Granta', 'Wagon', 'Luxe purple', 662000),
('LADA', 'Vesta', 'Wagon', 'Comfort yellow', 679010),
('Ford', 'Focus', 'Sedan', 'Trend white', 1230000),
('Audi', 'A3', 'HatchBack', 'black', 2000000),
('LADA', 'Granta', 'Wagon', 'Comfort cyan', 566000),
('LADA', 'Granta', 'Wagon', 'Luxe brown', 662080),
('LADA', 'Granta', 'Wagon', 'Luxe like a boss', 662100),
('LADA', 'Vesta', 'Sedan', 'Сomfort navy', 631000),
('LADA', 'Vesta', 'Sedan', 'Luxe blue', 636000),
('Ford', 'Focus', 'Hatchback', 'Sync black', 1082000),
('Ford', 'Focus', 'Hatchback', 'Sync white', 1092000)
;
60 rows affected

CREATE OR REPLACE FUNCTION get_category_for_page(_min_rows int, _page_id int) RETURNS int[] AS $$
DECLARE
    _remainder int := _min_rows;
    _page_counter int := 1;
    _categories int[] = '{}';
    _temprow record;
BEGIN
    FOR _temprow IN

        SELECT                                                    -- 1
            min_price_vendor,
            min_price_model,
            min_price_body, 
            COUNT(*)
        FROM (
            SELECT
                *,
                MIN(price) OVER win_vendor min_price_vendor,
                MIN(price) OVER win_model min_price_model,
                MIN(price) OVER win_body min_price_body
            FROM
                car
            WINDOW
                win_vendor AS (PARTITION BY vendor_name),
                win_model AS (PARTITION BY vendor_name, model_name),
                win_body AS (PARTITION BY vendor_name, model_name, body_type)
            ORDER BY
                min_price_vendor,
                min_price_model,
                min_price_body,
                price,
                specifications_name
        ) s
        GROUP BY
            min_price_vendor,
            min_price_model,
            min_price_body
        ORDER BY
            min_price_vendor,
            min_price_model,
            min_price_body

    LOOP
        IF (_page_counter = _page_id) THEN                        -- 2
            _categories := _categories || _temprow.min_price_body;
        END IF;

        IF (_remainder - _temprow.count < 0) THEN                 -- 3
            _page_counter := _page_counter + 1;
            _remainder := _max_rows;
        ELSE 
            _remainder := _remainder - _temprow.count;            -- 4
        END IF;

        IF (_page_counter > _page_id) THEN                        -- 5
            EXIT;
        END IF;

    END LOOP;

    RETURN _categories;
END;
$$ LANGUAGE plpgsql;
SELECT get_category_for_page(10, 2)
ERROR:  column "_max_rows" does not exist
LINE 1: SELECT _max_rows
               ^
QUERY:  SELECT _max_rows
CONTEXT:  PL/pgSQL function get_category_for_page(integer,integer) line 50 at assignment


SELECT 
    *
FROM (
            SELECT
                *,
                MIN(price) OVER win_vendor min_price_vendor,
                MIN(price) OVER win_model min_price_model,
                MIN(price) OVER win_body min_price_body
            FROM
                car
            WINDOW
                win_vendor AS (PARTITION BY vendor_name),
                win_model AS (PARTITION BY vendor_name, model_name),
                win_body AS (PARTITION BY vendor_name, model_name, body_type)
            ORDER BY
                min_price_vendor,
                min_price_model,
                min_price_body,
                price,
                specifications_name
        ) s
WHERE 
    min_price_body = ANY(get_category_for_page(10, 2))
ERROR:  column "_max_rows" does not exist
LINE 1: SELECT _max_rows
               ^
QUERY:  SELECT _max_rows
CONTEXT:  PL/pgSQL function get_category_for_page(integer,integer) line 50 at assignment