From 6aa13394a12c1931990b91e62210cba72706920d Mon Sep 17 00:00:00 2001
From: fusion32 <marcopuzziello@gmail.com>
Date: Mon, 27 Oct 2025 21:44:13 -0300
Subject: adjust character death indexes for common queries

---
 postgres/z-003-character-deaths-indexes.sql | 20 ++++++++++++++++++++
 1 file changed, 20 insertions(+)
 create mode 100644 postgres/z-003-character-deaths-indexes.sql

(limited to 'postgres/z-003-character-deaths-indexes.sql')

diff --git a/postgres/z-003-character-deaths-indexes.sql b/postgres/z-003-character-deaths-indexes.sql
new file mode 100644
index 0000000..1347b72
--- /dev/null
+++ b/postgres/z-003-character-deaths-indexes.sql
@@ -0,0 +1,20 @@
+-- NOTE(fusion): This file contains index adjustments for the `CharacterDeaths`
+-- table. It is not strictly necessary as there are no modified tables but should
+-- help with queries such as the most recent deaths/kills from a specific character
+-- or overall.
+--  It's inside a transaction to avoid errors from leaving the database in some
+-- partial state. The changes are already present in the latest `schema.sql`, so
+-- trying to apply them on a newly created database will result in errors.
+--==============================================================================
+
+BEGIN;
+
+DROP INDEX CharacterDeathsCharacterIndex;
+DROP INDEX CharacterDeathsOffenderIndex;
+
+CREATE INDEX CharacterDeathsCharacterIndex ON CharacterDeaths(CharacterID, Timestamp);
+CREATE INDEX CharacterDeathsOffenderIndex  ON CharacterDeaths(OffenderID, Timestamp);
+CREATE INDEX CharacterDeathsTimeIndex      ON CharacterDeaths(Timestamp);
+
+COMMIT;
+
-- 
cgit v1.2.3

