From ada3ab9f84dfa2df733d48d516476203a361ac63 Mon Sep 17 00:00:00 2001 From: Dan Goodliffe Date: Sun, 26 Apr 2026 16:14:50 +0100 Subject: Add indexes on all entity references in access_log --- src/schema.sql | 14 ++++++++++++++ 1 file changed, 14 insertions(+) diff --git a/src/schema.sql b/src/schema.sql index 9d39bcd..698ea3e 100644 --- a/src/schema.sql +++ b/src/schema.sql @@ -107,8 +107,22 @@ CREATE TABLE access_log( CONSTRAINT fk_access_log_content_type FOREIGN KEY (content_type) REFERENCES entities(id) ON UPDATE CASCADE ); +CREATE INDEX idx_access_log_content_type ON access_log(content_type); + +CREATE INDEX idx_access_log_hostname ON access_log(hostname); + +CREATE INDEX idx_access_log_path ON access_log(path); + +CREATE INDEX idx_access_log_query_string ON access_log(query_string); + +CREATE INDEX idx_access_log_referrer ON access_log(referrer); + CREATE INDEX idx_access_log_request_time ON access_log USING BRIN(request_time) WITH (autosummarize = TRUE); +CREATE INDEX idx_access_log_user_agent ON access_log(user_agent); + +CREATE INDEX idx_access_log_virtual_host ON access_log(virtual_host); + CREATE OR REPLACE VIEW access_log_view AS SELECT l.id, -- cgit v1.3