Всем привет!

Tables:

CREATE TABLE "public"."baseline" (
"baseline_pk" INTEGER NOT NULL,
"fromdatetime" TIMESTAMP WITH TIME ZONE NOT NULL,
"todatetime" TIMESTAMP WITH TIME ZONE NOT NULL,
CONSTRAINT "baseline_fromdatetime_ukc" UNIQUE("fromdatetime"),
CONSTRAINT "baseline_pkc" PRIMARY KEY("baseline_pk"),
CONSTRAINT "baseline_todatetime_ukc" UNIQUE("todatetime")
)

CREATE TABLE "public"."task" (
"task_pk" INTEGER NOT NULL,
"sourcesystem_fk" INTEGER NOT NULL,
CONSTRAINT "task_pkc" PRIMARY KEY("task_pk"),
CONSTRAINT "task_sourcesystem_fkc" FOREIGN KEY ("sourcesystem_fk")
REFERENCES "public"."sourcesystem"("sourcesystem_pk")
ON DELETE RESTRICT
ON UPDATE RESTRICT
NOT DEFERRABLE
)

CREATE TABLE "public"."plantaskrelation" (
"plantaskrelation_pk" INTEGER NOT NULL,
"plan_fk" INTEGER NOT NULL,
"from_baseline_fk" INTEGER NOT NULL,
"to_baseline_fk" INTEGER NOT NULL,
"parent_task_fk" INTEGER,
"child_task_fk" INTEGER NOT NULL,
"taskrelationtype_fk" INTEGER NOT NULL,
"treenodehash" VARCHAR(246) NOT NULL,
CONSTRAINT "plantaskrelation_1_ukc" UNIQUE("plan_fk", "from_baseline_fk", "treenodehash"),
CONSTRAINT "plantaskrelation_2_ukc" UNIQUE("plan_fk", "to_baseline_fk", "parent_task_fk", "child_task_fk"),
CONSTRAINT "plantaskrelation_pkc" PRIMARY KEY("plantaskrelation_pk"),
CONSTRAINT "plantaskrelation_child_task_fkc" FOREIGN KEY ("child_task_fk")
REFERENCES "public"."task"("task_pk")
ON DELETE RESTRICT
ON UPDATE RESTRICT
NOT DEFERRABLE,
CONSTRAINT "plantaskrelation_from_baseline_fkc" FOREIGN KEY ("from_baseline_fk")
REFERENCES "public"."baseline"("baseline_pk")
ON DELETE RESTRICT
ON UPDATE RESTRICT
NOT DEFERRABLE,
CONSTRAINT "plantaskrelation_parent_task_fkc" FOREIGN KEY ("parent_task_fk")
REFERENCES "public"."task"("task_pk")
ON DELETE RESTRICT
ON UPDATE RESTRICT
NOT DEFERRABLE,
CONSTRAINT "plantaskrelation_plan_fkc" FOREIGN KEY ("plan_fk")
REFERENCES "public"."plan"("plan_pk")
ON DELETE RESTRICT
ON UPDATE RESTRICT
NOT DEFERRABLE,
CONSTRAINT "plantaskrelation_taskrelationtype_fkc" FOREIGN KEY ("taskrelationtype_fk")
REFERENCES "public"."taskrelationtype"("taskrelationtype_pk")
ON DELETE RESTRICT
ON UPDATE RESTRICT
NOT DEFERRABLE,
CONSTRAINT "plantaskrelation_to_baseline_fkc" FOREIGN KEY ("to_baseline_fk")
REFERENCES "public"."baseline"("baseline_pk")
ON DELETE RESTRICT
ON UPDATE RESTRICT
NOT DEFERRABLE
)

CREATE TABLE "public"."baselinedtask" (
"baselinedtask_pk" INTEGER NOT NULL,
"task_fk" INTEGER NOT NULL,
"from_baseline_fk" INTEGER NOT NULL,
"to_baseline_fk" INTEGER NOT NULL,
"tasktype_fk" INTEGER NOT NULL,
"primary_plan_fk" INTEGER NOT NULL,
"sourcereference" VARCHAR(40) NOT NULL,
"name" VARCHAR(254) NOT NULL,
CONSTRAINT "baselinedtask_1_ukc" UNIQUE("task_fk", "from_baseline_fk"),
CONSTRAINT "baselinedtask_2_ukc" UNIQUE("task_fk", "to_baseline_fk"),
CONSTRAINT "baselinedtask_pkc" PRIMARY KEY("baselinedtask_pk"),
CONSTRAINT "baselinedtask_from_baseline_fkc" FOREIGN KEY ("from_baseline_fk")
REFERENCES "public"."baseline"("baseline_pk")
ON DELETE RESTRICT
ON UPDATE RESTRICT
NOT DEFERRABLE,
CONSTRAINT "baselinedtask_primary_plan_fkc" FOREIGN KEY ("primary_plan_fk")
REFERENCES "public"."plan"("plan_pk")
ON DELETE RESTRICT
ON UPDATE RESTRICT
NOT DEFERRABLE,
CONSTRAINT "baselinedtask_task_fkc" FOREIGN KEY ("task_fk")
REFERENCES "public"."task"("task_pk")
ON DELETE RESTRICT
ON UPDATE RESTRICT
NOT DEFERRABLE,
CONSTRAINT "baselinedtask_to_baseline_fkc" FOREIGN KEY ("to_baseline_fk")
REFERENCES "public"."baseline"("baseline_pk")
ON DELETE RESTRICT
ON UPDATE RESTRICT
NOT DEFERRABLE
)

Задача: выбрать PlanTaskRelation (PTR), BaselinedTask (BT), Task (T) указав:
planPk, baselinePk, sourcesystemPk, такие чтоб:
PTR содержал в поле childTaskFk ссылку на T,
BT содержал в поле taskFk ссылку на T,
PTR & BT были бы внутри указанного Baseline`a (Bactive), по датам from-to, ниже виден кусочек запроса:
" AND Bactive.fromDateTime <= PTR_to.toDateTime " +
" AND Bactive.toDateTime > PTR_Bfrom.fromDateTime " +
" AND BT.fromBaselineFk = PTR_Bfrom.baselinePk " +
" AND BT.toBaselineFk = PTR_Bto.baselinePk "

у меня это всё безобразие просто связывается по датам и Pk, работает ДОЛГО!

(пример из Hibernate`a :
" SELECT DISTINCT PTR_whereChild, BT, Tparent FROM "+
" lv.ctco.taskods.domain.task.PlanTaskRelation as PTR_whereChild, " +
" lv.ctco.taskods.domain.task.PlanTaskRelation as PTR_parentChild, " +
" lv.ctco.taskods.domain.task.Task as Tparent, " +
" lv.ctco.taskods.domain.task.Task as Tchild, " +
" lv.ctco.taskods.domain.task.BaselinedTask as BT, " +
" lv.ctco.taskods.domain.common.Baseline as Bactive, " +
" lv.ctco.taskods.domain.common.Baseline as Bfrom_parentChild, " +
" lv.ctco.taskods.domain.common.Baseline as Bto_parentChild, " +
" lv.ctco.taskods.domain.common.Baseline as Bfrom_whereChild, " +
" lv.ctco.taskods.domain.common.Baseline as Bto_whereChild, " +
" lv.ctco.taskods.domain.common.Baseline as Bfrom_BT, " +
" lv.ctco.taskods.domain.common.Baseline as Bto_BT " +
" WHERE " +
// init Active Baseline by PK
" Bactive.baselinePk = :activeBaselinePk " +

// Select PTR by planFk, childTaskFk
" AND PTR_whereChild.childTaskFk = Tparent.taskPk " +
" AND PTR_whereChild.planFk = :planFk " +
// and given PTR are inside active baseline
" AND PTR_whereChild.fromBaselineFk = Bfrom_whereChild.baselinePk "+
" AND PTR_whereChild.toBaselineFk = Bto_whereChild.baselinePk "+
" AND Bactive.fromDateTime <= Bto_whereChild.toDateTime " +
" AND Bactive.toDateTime > Bfrom_whereChild.fromDateTime "+

// Select PTR by childTaskFk, parentTaskFk, planFk
" AND PTR_parentChild.childTaskFk = Tchild.taskPk " +
" AND PTR_parentChild.parentTaskFk = Tparent.taskPk " +
" AND Tchild.sourceSystem = " + SourceSystem.ATLASSIAN_JIRA.getSourceSystemPk() + " " +
" AND Tparent.sourceSystem = " + SourceSystem.MS_PROJECT.getSourceSystemPk() + " " +
" AND PTR_parentChild.planFk = :planFk " +
// and given PTR are inside active baseline
" AND PTR_parentChild.fromBaselineFk = Bfrom_parentChild.baselinePk " +
" AND PTR_parentChild.toBaselineFk = Bto_parentChild.baselinePk " +
" AND Bactive.fromDateTime <= Bto_parentChild.toDateTime " +
" AND Bactive.toDateTime > Bfrom_parentChild.fromDateTime " +

// link ActiveBaseline and ParentTask to BaselinedTask (we will need it later to create TaskModel)
// Select BT by taskFk, primaryPlanFk
" AND BT.taskFk = Tparent.taskPk " +
" AND BT.primaryPlanFk = :planFk " +
// and given BT are inside activeBaseline
" AND Bactive.fromDateTime <= Bto_BT.toDateTime " +
" AND Bactive.toDateTime > Bfrom_BT.fromDateTime " +
" AND BT.fromBaselineFk = Bfrom_BT.baselinePk " +
" AND BT.toBaselineFk = Bto_BT.baselinePk "
)

Вопрос: варианты оптимизации?