postgresql - Postgres EXECUTE 比 Query 慢

Question

我遇到了一个问题，我开发了一个带有动态 ware 子句的功能。当我每次单独运行查询时，它会更快地返回值，同时运行EXECUTE它会分配时间。

"cv."SessionId" = ''f0d1c0e1-b13c-4aff-b1f7-432183b06d10'' AND cv."CampusId" = ''4f4310a3-9c36-49c2-8456-5604cb7e9a62'' AND cv."ProgramId"=''e50efffa-40a3-4eb9-a65b-65106967add7'' AND cv."ShiftId"=''414de073-ca8b-4a84-80bc-52c2030b4eeb'' AND cv."ProgramDetailId"=''8d1ce321-d015-4336-ba92-dcd2c8b6443b'' AND cv."ClassId"=''8931d744-acc9-4776-a03a-2b705038ea48'' AND cv."SectionId"=''83c507d0-8f61-4a28-b221-88625482953f'' AND cv."CollectorId"=''e58d2fbf-839a-49a1-b1ce-92e7c26a305b'' AND cv."FeeHeadId"=''5721bc8f-4e9c-49d0-a992-6b846fafb9db'' AND cv."PaidDate" >= ''2020-07-01'' AND cv."PaidDate" <= ''2020-08-07'' ORDER BY cv."ChallanNo" ASC"

这是每次更改的 where 子句参数。有的时候二 有的时候四五。以下是我要优化的功能。

declare feeheadrow record;
declare paiddate date;
declare rollno text;
declare admisformid uuid;
declare feeheadname text;
declare instNo int4;
declare stdname text;
declare fathername text;
declare refno text;
declare classid uuid;
declare campusprogid uuid;
declare stdid uuid;
declare challano text;
declare campusid uuid;
declare sessionid uuid;
declare progdetailid uuid;
declare campusname text;
declare descrip text;
declare shiftname text;
declare genderid uuid;
declare shiftid uuid;
declare remarks text;
declare sessionname text;
declare totalamount int4;
declare programid uuid;

declare collectorid uuid;

    BEGIN

DROP TABLE IF EXISTS "FeeReport";
DROP TABLE IF EXISTS "FeeHeads";

create temp table "FeeReport"("NewID" uuid, "PaidDate" Date, "RollNo" text, "AdmissionFormId" uuid, "FeeHeadName" text, "InstallmentNo" int4, "StudentName" text, "FatherName" text, "RefferenceNo" text, "ClassId" uuid, "CampusProgramId" uuid, "FeeHeadId" uuid,
"FeeAmount" int4, "PayableAmount" int4, "StudentId" uuid, "ChallanNo" text, "CampusId" uuid, "SessionId" uuid,
"ProgramDetailId" uuid, "CampusName" text, "Description" text, "ShiftName" text, "GenderId" uuid,
"ShiftId" uuid,"Remarks" text, "ScholarshipCriteriaId" uuid, "ScholarshipCriteriaName" text, "SessionName" text, "TotalAmount" int4
, "ProgramId" uuid, "CollectorId" uuid
) on commit DROP; 

create temp table "FeeHeads"("FeeHeadId" UUID, "FullName" TEXT) on commit drop;

insert into "FeeReport"
SELECT * FROM "Query"('cv.*', '"Fee"."VWFeeStatement2" AS cv', whereclause) AS
"FeeRecords" ("NewID" uuid, "PaidDate" date, "RollNo" text, "AdmissionFormId" uuid, "FeeHeadName" text, "InstallmentNo" int4, "StudentName" text, "FatherName" text, "RefferenceNo" text, "ClassId" uuid, "CampusProgramId" uuid, "FeeHeadId" uuid, "FeeAmount" int4, "PayableAmount" int4, "StudentId" uuid, "ChallanNo" text, "CampusId" uuid, "SessionId" uuid, "ProgramDetailId" uuid, "CampusName" text, "Description" text, "ShiftName" text, "GenderId" uuid, "ShiftId" uuid, "Remarks" text, "ScholarshipCriteriaId" uuid, "ScholarshipCriteriaName" text, "SessionName" text, "TotalAmount" int4,"ProgramId" uuid,"CollectorId" uuid);

insert into "FeeReport"
SELECT * FROM "Query"('cv.*', '"Fee"."VWFeeStatementOther2" AS cv', whereclause) AS
"FeeRecords" ("NewID" uuid, "PaidDate" date, "RollNo" text, "AdmissionFormId" uuid, "FeeHeadName" text, "InstallmentNo" int4, "StudentName" text, "FatherName" text, "RefferenceNo" text, "ClassId" uuid, "CampusProgramId" uuid, "FeeHeadId" uuid, "FeeAmount" int4, "PayableAmount" int4, "StudentId" uuid, "ChallanNo" text, "CampusId" uuid, "SessionId" uuid, "ProgramDetailId" uuid, "CampusName" text, "Description" text, "ShiftName" text, "GenderId" uuid, "ShiftId" uuid, "Remarks" text, "ScholarshipCriteriaId" uuid, "ScholarshipCriteriaName" text, "SessionName" text, "TotalAmount" int4,"ProgramId" uuid,"CollectorId" uuid);


insert into "FeeHeads"
SELECT DISTINCT fh."FeeHeadId", fh."FullName" FROM "Fee"."FeeHead" AS fh, "FeeReport" fr
WHERE fr."FeeHeadId" = fh."FeeHeadId";

FOR vwfeestatementrow in select distinct f."AdmissionFormId"
 from "FeeReport" f
LOOP
        select vw."PaidDate", vw."RollNo", vw."AdmissionFormId", vw."InstallmentNo", vw."StudentName", vw."FatherName" , vw."RefferenceNo", vw."ClassId", 
        vw."CampusProgramId", vw."StudentId", vw."ChallanNo", vw."CampusId" , vw."SessionId",
        vw."ProgramDetailId", vw."CampusName", vw."Description", vw."ShiftName", vw."GenderId",vw."ShiftId", vw."SessionName", vw."TotalAmount",vw."ProgramId",vw."CollectorId" 
        INTO paiddate,rollno,
        admisformid,instNo,stdname,fathername,refno,classid,
        campusprogid,stdid,challano,campusid,sessionid,progdetailid,campusname,descrip,shiftname,genderid,shiftid,sessionname,totalamount,programid,collectorid
        from "FeeReport" vw where vw."AdmissionFormId"=vwfeestatementrow."AdmissionFormId" limit 1;
        
      FOR feeheadrow IN SELECT fh."FeeHeadId",fh."FullName" FROM "FeeHeads" fh WHERE (fh."FeeHeadId" NOT IN (
      SELECT ins."FeeHeadId" FROM "FeeReport" AS ins WHERE ins."AdmissionFormId" = vwfeestatementrow."AdmissionFormId"))
        LOOP
        INSERT INTO "FeeReport"("NewID","PaidDate","RollNo","AdmissionFormId","FeeHeadName","InstallmentNo","StudentName",
        "FatherName","RefferenceNo","ClassId", "CampusProgramId","FeeHeadId",
        "FeeAmount", "PayableAmount", "StudentId", "ChallanNo", "CampusId", "SessionId",
        "ProgramDetailId", "CampusName", "Description", "ShiftName", "GenderId",
        "ShiftId","Remarks", "ScholarshipCriteriaId", "ScholarshipCriteriaName", "SessionName", "TotalAmount","ProgramId",
        "CollectorId"
        ) 
        VALUES(uuid_generate_v1(),paiddate,rollno,vwfeestatementrow."AdmissionFormId",
        feeheadrow."FullName",instNo,stdname,fathername
        ,refno,classid,campusprogid,feeheadrow.
        "FeeHeadId",0,0,stdid,challano,campusid,
        sessionid,progdetailid,campusname,descrip,shiftname,genderid,shiftid,'',null,'',
        sessionname,totalamount,programid,collectorid
        );
        
        END LOOP;

END LOOP;

-- DELETE FROM "Student" AS st WHERE st."AdmissionFormId" IN (SELECT af."AdmissionFormId" FROM "Fee"."StudentFeeStructure" AS sf, "Admission"."AdmissionForm" AS af WHERE sf."AdmissionFormId" = af."AdmissionFormId" AND af."CampusProgramId" = campusprogramid  AND sf."ConcessionDetailId" IS NOT NULL);

    RETURN query Select fr.* from "FeeReport" as fr ORDER BY fr."ChallanNo", fr."FeeHeadName";
END```

标签： postgresqlfunctionexecute