Begin of the insertion**Acceptance criteria**End of the insertion Begin of the deletion The initial implementation of the filtering for historic data would lead to poor performance on larger production data. The SQL below is an exemplary excerpt of the statements currently issued to get historic data. It would be issued if an administrator wants to compare the current work packages with the state of a month back without changing the default filter ("Status open"):End of the deletion

Begin of the deletion ```SQL
SELECT
'2023-01-06 07:57:14 UTC' as timestamp,
work_package_journals.*,
journals.journable_id as id,
journables.created_at as created_at,
journals.updated_at as updated_at
FROM
( SELECT distinct on (journable_id)End of the deletion * Begin of the insertion AddEnd of the insertion Begin of the deletion FROM "journals"
WHERE "journals"."created_at" <= '2023-01-06 07:57:14.153220'
AND journals.journable_type = 'WorkPackage'
ORDER BY "journals"."journable_id" ASC,
"journals"."created_at" DESC
) journals,
"work_package_journals",
(SELECT id, created_at FROM "work_packages") AS journables
WHERE
"journables"."id" = "journals"."journable_id"
AND "journals"."data_id" = "work_package_journals"."id"
AND "project_id" IN (
SELECT "projects".id
FROM "projects"
INNER JOIN "enabled_modules"
ON "projects"."id" = "enabled_modules"."project_id"
AND "enabled_modules"."name" IN ('work_package_tracking')
AND "projects"."active" = TRUE
WHERE "projects"."active" = TRUE)
AND "status_id" IN (SELECT id FROM statuses WHERE NOT is_closed)
```

OnEnd of the deletion a Begin of the insertion`tstzrange` columnEnd of the insertion Begin of the deletion larger database the statement will take over a minuteEnd of the deletion to Begin of the deletion execute. The performance improves when applying a limit and ordering by journable\_id but decreases again when using a different column.

Attempts to improveEnd of the deletion the Begin of the deletion performance by adding indices failed.

The statement suffers from two performance hotspots:

* Calculating theEnd of the deletion journals Begin of the insertiontable.
End of the insertion Begin of the deletion relevant for the filtering (most recent journal before the timestamp queried for) done in the `journals` subselect.
End of the deletion * Begin of the insertionThe lower boundEnd of the insertion Begin of the deletion Joining the `journals` with the other tables: `work_package_journals` and `journables`

BothEnd of the deletion of Begin of the deletion the hotspots can be addressed by creating a materialized viewEnd of the deletion that Begin of the insertioncolumn isEnd of the insertion Begin of the deletion preparesEnd of the deletion the Begin of the insertioncreated\_at valueEnd of the insertion Begin of the deletion journals to be read more easily:

```SQL
CREATE MATERIALIZED VIEW
work_package_journals_prepared
AS
SELECT
journals.journable_id as id,
journals.updated_at as updated_at,
journals.active_range,
journals.notes,
journals.end_at,
journals.created_at as created_at,
journables.created_at as first_created_at,
work_package_journals.type_id,
work_package_journals.project_id,
work_package_journals.subject,
work_package_journals.description,
work_package_journals.due_date,
work_package_journals.category_id,
work_package_journals.status_id,
work_package_journals.assigned_to_id,
work_package_journals.priority_id,
work_package_journals.version_id,
work_package_journals.author_id,
work_package_journals.done_ratio,
work_package_journals.estimated_hours,
work_package_journals.start_date,
work_package_journals.parent_id,
work_package_journals.responsible_id,
work_package_journals.budget_id,
work_package_journals.story_points,
work_package_journals.remaining_hours,
work_package_journals.derived_estimated_hours,
work_package_journals.schedule_manually,
work_package_journals.duration,
work_package_journals.ignore_non_working_days
FROM
journals,
"work_package_journals",
(SELECT id, created_at FROM "work_packages") AS journables
WHERE
"journables"."id" = "journals"."journable_id"
AND
"journals"."data_id" = "work_package_journals"."id"
AND
"journals"."data_type" = 'Journal::WorkPackageJournal'
AND
journals.journable_type = 'WorkPackage'
```

Using the excerpt above, the materialized view would only contain the work package journals up to a year ago. That includes journals created within that period but also journals which were replaced within that period. The excerpt makes useEnd of the deletion of Begin of the deletion an amendment toEnd of the deletion the Begin of the deletion `journals` table `active_range` including the `created_at` date of theEnd of the deletion journal Begin of the insertion
* The upperEnd of the insertion Begin of the deletion as its lowerEnd of the deletion bound Begin of the insertionof that colum isEnd of the insertion Begin of the deletion andEnd of the deletion the Begin of the insertioncreated\_at valueEnd of the insertion Begin of the deletion `created_at` dateEnd of the deletion of the Begin of the insertionjournal&#39;s successor.
* That value can thus only be written once theEnd of the insertion Begin of the deletion journalsEnd of the deletion successor Begin of the insertionis created. 
* End of the insertion Begin of the deletion as its upper boundary (not included).End of the deletion If Begin of the insertionthere isEnd of the insertion no successor Begin of the insertion(yet),End of the insertion Begin of the deletion exists,End of the deletion the upper Begin of the insertionbound willEnd of the insertion Begin of the deletion boundary wouldEnd of the deletion be Begin of the insertionempty.
* AdaptEnd of the insertion Begin of the deletion NULL. Adding that column would need changes toEnd of the deletion the Begin of the insertiontimestamp filteringEnd of the insertion Begin of the deletion journal creating sql but might be beneficial in other scenarios as well. If the column is not added, the SQL for the materialized view would needEnd of the deletion to Begin of the insertionuseEnd of the insertion Begin of the deletion be altered creatingEnd of the deletion the Begin of the insertion`tstzrange`
* (If possible) AdaptEnd of the insertion Begin of the deletion view would be possible nonetheless.

Indices can be added on that materialized view optimizing performance.

EmployingEnd of the deletion the Begin of the insertionwork package query results to no longer haveEnd of the insertion Begin of the deletion view, the filtering statement from above can be simplifiedEnd of the deletion a Begin of the insertionwhole subqueryEnd of the insertion Begin of the deletion lot:

```SQL
SELECT
'2023-01-23 07:57:14 UTC' as timestamp,
wp_journals.*

FROM "work_package_journals_prepared" wp_journals
WHERE wp_journals.active_range @> timestampEnd of the deletion with Begin of the insertionall filter criteriaEnd of the insertion Begin of the deletion time zone '2023-01-23 07:57:14 UTC'
AND project_id IN (SELECT id FROM "projects" WHERE "projects"."id"= project_id AND "projects"."active" = TRUE)
AND status_id IN (SELECT id from statuses WHERE NOT is_closed)
```

The performance also improves drasticallyEnd of the deletion but Begin of the insertionrather have onlyEnd of the insertion Begin of the deletion is still not optimal. It takes about 2 seconds to execute.

If order &amp; limit are applied to statements, the performance of both improve. The initial statement takes about 2.5 s and theEnd of the deletion one Begin of the insertionsubquery comparingEnd of the insertion Begin of the deletion based on the view 100ms.

This improvement comesEnd of the deletion at Begin of the insertionallEnd of the insertion Begin of the deletion a cost asEnd of the deletion the Begin of the insertionrelevant timestamps e.g `"journals"."validity_period" **@>** timestamp **with** time **zone** '2020-05-12 04:00:00' **OR** "journals".validity_period **@>** timestamp **with** time **zone** '2023-06-07 08:52:05.515455'`
* Whether thisEnd of the insertion Begin of the deletion materialized view needs to be maintained. Refreshing the view on the used dataset takes about 6 minutes (where the `active_range`End of the deletion is Begin of the deletion already calculated). Because of this, it will not beEnd of the deletion possible Begin of the insertionwill needEnd of the insertion to Begin of the insertionbe double checked and dependsEnd of the insertion Begin of the deletion trigger refreshing the viewEnd of the deletion on Begin of the insertionhowEnd of the insertion Begin of the deletion every update toEnd of the deletion the Begin of the insertionindividual filters are written.End of the insertion Begin of the deletion journals/work\_package/work\_package\_journals tables. But this would presumably not be necessary. Instead the update could be done in regular intervals e.g. nightly or twice a day. The baseline comparison feature would only have to raise an error or notification when filtering for a time frame the materialized view does not cover.End of the deletion