Untitled
plain_text
2 months ago
2.5 kB
2
Indexable
Never
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"