Untitled

create table "ratesOfficesAgg"
(
    "rateId" integer,
    offices  integer[],
    primary key ("rateId")
);

--стоимость index only/index lookup кажется мне почти одинаковой, так как там выборка условно 1000 строк не более
--create index "ratesOfficesFullIdx" on ratesofficesagg("rateId", offices);

insert into "ratesOfficesAgg" (select "ro"."programRateId" as "rateId", array_agg("officeId") offices
                             from "ratesOffices" as "ro"
                             group by "ro"."programRateId");


explain analyse
select "p"."id", "p"."bankId"
from "programs" as "p"
         inner join "programRates" as "pr"
                    on "p"."id" = "pr"."programId" and "pr"."deletedAt" is null and "pr"."term"[1] <= 20 and
                       "pr"."term"[2] >= 20 and "pr"."creditSum"[1] <= 6240000 and "pr"."creditSum"[2] >= 6240000 and
                       ("pr"."firstPay"[1]::float <= 20) and "pr"."firstPay"[2]::float >= 20
         inner join "ratesOfficesAgg" as "ro" on "pr"."id" = "ro"."rateId" and (
            "pr"."exclusive"
            and not "ro"."offices" && '{3}'::integer[]
        or
            not "pr"."exclusive"
                and "ro"."offices" && '{3}'::integer[]
    )
where "p"."currency" = 1
  and "p"."showOnWebsite" = true
  and "p"."deletedAt" is null
  and (select count(distinct "pfv"."programFieldId")
       from "programFieldValues" as "pfv"
                inner join "programFieldValuePrograms" as "pfvp" on "pfvp"."programFieldValueId" = "pfv"."id"
       where "pfvp"."programId" = "p"."id"
         and CASE "pfv"."programFieldId"

                 WHEN 30 THEN "pfv"."value"::int = ANY (ARRAY [translate('{26,27}', '[]', '{}')::INT[]])
                 WHEN 29 THEN "pfv"."value" @> '1'
                 WHEN 63 THEN translate("pfv"."value" ->> '1', '[]', '{}')::int[] && '{1,2,3,4,5}'
                 WHEN 123 THEN "pfv"."value" @> '1'
                 WHEN 62 THEN "pfv"."value"::int = ANY (ARRAY [translate('{5}', '[]', '{}')::INT[]])

                 WHEN 59 THEN
                         NOT (("pfv"."value" ->> 'closingDate')::date + 1 <= '2023-08-10'
                             AND ("pfv"."value" ->> 'closingDate')::date + 2 > '2023-08-10')
                         OR ("pfv"."value" ->> 'closingDate') IS NULL

                 ELSE FALSE
           END) = 6
group by "p"."id"