246 lines
11 KiB
SQL
246 lines
11 KiB
SQL
-- Cygnus PostgreSQL index migration
|
|
-- Derived from matrix.sql, nimble.qry, Visit MIS, and Dashboard functions/views.
|
|
--
|
|
-- IMPORTANT
|
|
-- 1. Run each CREATE INDEX as a separate statement. Do not wrap this file in BEGIN/COMMIT.
|
|
-- 2. CREATE INDEX CONCURRENTLY minimizes blocking but still consumes CPU, I/O and disk.
|
|
-- 3. Run during a lower-traffic window and monitor replication lag and free disk space.
|
|
-- 4. IF NOT EXISTS checks the name only. Run the preflight query first to inspect equivalent indexes.
|
|
-- 5. These statements do not change table columns, constraints, or stored data.
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Preflight: current sizes and existing definitions
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
SELECT
|
|
n.nspname AS schema_name,
|
|
t.relname AS table_name,
|
|
i.relname AS index_name,
|
|
pg_size_pretty(pg_relation_size(i.oid)) AS index_size,
|
|
pg_get_indexdef(i.oid) AS definition
|
|
FROM pg_index x
|
|
JOIN pg_class t ON t.oid = x.indrelid
|
|
JOIN pg_class i ON i.oid = x.indexrelid
|
|
JOIN pg_namespace n ON n.oid = t.relnamespace
|
|
WHERE n.nspname = 'public'
|
|
AND t.relname IN (
|
|
'riskalerts', 'belt_allocation', 'portfolio_branch', 'main',
|
|
'main_operations', 'temp_reporting'
|
|
)
|
|
ORDER BY t.relname, i.relname;
|
|
|
|
SELECT
|
|
relname,
|
|
n_live_tup,
|
|
n_dead_tup,
|
|
seq_scan,
|
|
seq_tup_read,
|
|
idx_scan,
|
|
pg_size_pretty(pg_total_relation_size(relid)) AS total_size
|
|
FROM pg_stat_user_tables
|
|
WHERE relname IN (
|
|
'riskalerts', 'belt_allocation', 'portfolio_branch', 'main',
|
|
'main_operations', 'temp_reporting'
|
|
)
|
|
ORDER BY pg_total_relation_size(relid) DESC;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Phase 1: high-confidence join and filter indexes
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
-- visit_mis/current_visit_mis join riskalerts using both columns. The dump has
|
|
-- separate uuid and visit indexes, which do not provide this composite lookup.
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_riskalerts_uuid_visit
|
|
ON public.riskalerts USING btree (uuid, visit);
|
|
|
|
-- colony_allocation is a view joining colony to belt_allocation by belt_id.
|
|
-- Only belt_allocation(verifier_id) exists in the supplied schema.
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_belt_allocation_belt_verifier
|
|
ON public.belt_allocation USING btree (belt_id, verifier_id);
|
|
|
|
-- portfolio_bank_branch expands through portfolio_branch. These support its
|
|
-- joins and the common portfolio/isactive lookup used by nimble.qry.
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_portfolio_branch_portfolio_active
|
|
ON public.portfolio_branch USING btree (portfolio_id, isactive, portbranch_id);
|
|
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_portfolio_branch_bank_branch
|
|
ON public.portfolio_branch USING btree (bank_branch_id);
|
|
|
|
-- Dashboard hourly_tobesolved_cases filters on tobesolvedon and then reads
|
|
-- sdone/sdoneon. The existing temp_reporting indexes cover uuid and isclosed.
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_temp_reporting_tobesolvedon_open
|
|
ON public.temp_reporting USING btree (tobesolvedon)
|
|
INCLUDE (sdone, sdoneon, portfolio_id)
|
|
WHERE tobesolvedon IS NOT NULL;
|
|
|
|
-- scan_mis and Dashboard read today's completed scans. This avoids scanning all
|
|
-- main_operations rows having the same low-cardinality sdone value.
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_main_operations_scan_doneon
|
|
ON public.main_operations USING btree (sdoneon, sdoneby, uuid)
|
|
WHERE sdone = 1 AND sdoneon IS NOT NULL;
|
|
|
|
-- Visit MIS expands residence/office/property separately. These partial indexes
|
|
-- remove deleted/non-applicable rows before branch/verifier matching. They also
|
|
-- retain uuid for the main_operations join.
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_main_visit_res_branch_verifier
|
|
ON public.main USING btree (branch_id, resiverifier, uuid)
|
|
WHERE isdeleted = 0 AND rv = 1;
|
|
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_main_visit_off_branch_verifier
|
|
ON public.main USING btree (branch_id, offverifier, uuid)
|
|
WHERE isdeleted = 0 AND ov = 1;
|
|
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_main_visit_prop_branch_verifier
|
|
ON public.main USING btree (branch_id, propverifier, uuid)
|
|
WHERE isdeleted = 0 AND pv = 1;
|
|
|
|
-- vervisitmis queries verifier_monthly_data by verifier and receivedate. These
|
|
-- are partial and therefore smaller than unrestricted composite indexes.
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_main_monthly_res_verifier_date
|
|
ON public.main USING btree (resiverifier, receivedate)
|
|
WHERE isdeleted = 0 AND rv = 1 AND sradd = 0;
|
|
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_main_monthly_off_verifier_date
|
|
ON public.main USING btree (offverifier, receivedate)
|
|
WHERE isdeleted = 0 AND ov = 1 AND soadd = 0;
|
|
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_main_monthly_prop_verifier_date
|
|
ON public.main USING btree (propverifier, receivedate)
|
|
WHERE isdeleted = 0 AND pv = 1 AND spadd = 0;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Phase 2: foreign-key support indexes
|
|
-- ---------------------------------------------------------------------------
|
|
-- These do not directly solve Visit MIS/Dashboard latency. They prevent child
|
|
-- table scans during parent updates/deletes and support related joins.
|
|
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_case_documents_uuid
|
|
ON public.case_documents USING btree (uuid);
|
|
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_contact_mapping_contact_id
|
|
ON public.contact_mapping USING btree (contact_id);
|
|
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_contacts_history_contact_id
|
|
ON public.contacts_history USING btree (contact_id);
|
|
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_document_data_uuid
|
|
ON public.document_data USING btree (uuid);
|
|
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_field_mapping_field_id
|
|
ON public.field_mapping USING btree (field_id);
|
|
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_field_mapping_template_id
|
|
ON public.field_mapping USING btree (template_id);
|
|
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_field_value_field_id
|
|
ON public.field_value USING btree (field_id);
|
|
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_onlinecasedetails_uuid
|
|
ON public.onlinecasedetails USING btree (uuid);
|
|
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_options_mapping_opt_value_id
|
|
ON public.options_mapping USING btree (opt_value_id);
|
|
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_permission_page_id
|
|
ON public.permission USING btree (page_id);
|
|
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_phoneno_history_uuid
|
|
ON public.phoneno_history USING btree (uuid);
|
|
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_tagdoc_history_docid
|
|
ON public.tagdoc_history USING btree (docid);
|
|
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_tagged_docs_uuid
|
|
ON public.tagged_docs USING btree (uuid);
|
|
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_template_mapping_portfolio_id
|
|
ON public.template_mapping USING btree (portfolio_id);
|
|
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_template_mapping_template_id
|
|
ON public.template_mapping USING btree (template_id);
|
|
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_tracker_template_mapping_portfolio
|
|
ON public.tracker_template_mapping USING btree (portfolio_id);
|
|
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_tracker_template_mapping_tracker
|
|
ON public.tracker_template_mapping USING btree (tracker_id);
|
|
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_tracker_sheet_layout_sheet
|
|
ON public.tracker_template_sheet_layout USING btree (sheet_id);
|
|
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_tracker_template_sheets_tracker
|
|
ON public.tracker_template_sheets USING btree (tracker_id);
|
|
|
|
-- Refresh planner statistics after all builds. ANALYZE does not rewrite tables,
|
|
-- but it consumes resources, so run it after the index builds at low traffic.
|
|
ANALYZE public.riskalerts;
|
|
ANALYZE public.belt_allocation;
|
|
ANALYZE public.portfolio_branch;
|
|
ANALYZE public.temp_reporting;
|
|
ANALYZE public.main_operations;
|
|
ANALYZE public.main;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Postflight validation
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
SELECT
|
|
indexrelname,
|
|
idx_scan,
|
|
idx_tup_read,
|
|
idx_tup_fetch,
|
|
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
|
|
FROM pg_stat_user_indexes
|
|
WHERE indexrelname LIKE 'idx_%'
|
|
AND relname IN (
|
|
'riskalerts', 'belt_allocation', 'portfolio_branch', 'main',
|
|
'main_operations', 'temp_reporting'
|
|
)
|
|
ORDER BY relname, indexrelname;
|
|
|
|
-- Check for invalid indexes left by an interrupted concurrent build.
|
|
SELECT n.nspname, c.relname AS index_name
|
|
FROM pg_index i
|
|
JOIN pg_class c ON c.oid = i.indexrelid
|
|
JOIN pg_namespace n ON n.oid = c.relnamespace
|
|
WHERE n.nspname = 'public'
|
|
AND NOT i.indisvalid;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Emergency rollback reference -- intentionally commented out
|
|
-- ---------------------------------------------------------------------------
|
|
-- Uncomment and run only the index that must be removed. Do not execute this
|
|
-- section after a successful migration unless a measured regression occurs.
|
|
--
|
|
-- DROP INDEX CONCURRENTLY IF EXISTS public.idx_riskalerts_uuid_visit;
|
|
-- DROP INDEX CONCURRENTLY IF EXISTS public.idx_belt_allocation_belt_verifier;
|
|
-- DROP INDEX CONCURRENTLY IF EXISTS public.idx_portfolio_branch_portfolio_active;
|
|
-- DROP INDEX CONCURRENTLY IF EXISTS public.idx_portfolio_branch_bank_branch;
|
|
-- DROP INDEX CONCURRENTLY IF EXISTS public.idx_temp_reporting_tobesolvedon_open;
|
|
-- DROP INDEX CONCURRENTLY IF EXISTS public.idx_main_operations_scan_doneon;
|
|
-- DROP INDEX CONCURRENTLY IF EXISTS public.idx_main_visit_res_branch_verifier;
|
|
-- DROP INDEX CONCURRENTLY IF EXISTS public.idx_main_visit_off_branch_verifier;
|
|
-- DROP INDEX CONCURRENTLY IF EXISTS public.idx_main_visit_prop_branch_verifier;
|
|
-- DROP INDEX CONCURRENTLY IF EXISTS public.idx_main_monthly_res_verifier_date;
|
|
-- DROP INDEX CONCURRENTLY IF EXISTS public.idx_main_monthly_off_verifier_date;
|
|
-- DROP INDEX CONCURRENTLY IF EXISTS public.idx_main_monthly_prop_verifier_date;
|
|
-- DROP INDEX CONCURRENTLY IF EXISTS public.idx_case_documents_uuid;
|
|
-- DROP INDEX CONCURRENTLY IF EXISTS public.idx_contact_mapping_contact_id;
|
|
-- DROP INDEX CONCURRENTLY IF EXISTS public.idx_contacts_history_contact_id;
|
|
-- DROP INDEX CONCURRENTLY IF EXISTS public.idx_document_data_uuid;
|
|
-- DROP INDEX CONCURRENTLY IF EXISTS public.idx_field_mapping_field_id;
|
|
-- DROP INDEX CONCURRENTLY IF EXISTS public.idx_field_mapping_template_id;
|
|
-- DROP INDEX CONCURRENTLY IF EXISTS public.idx_field_value_field_id;
|
|
-- DROP INDEX CONCURRENTLY IF EXISTS public.idx_onlinecasedetails_uuid;
|
|
-- DROP INDEX CONCURRENTLY IF EXISTS public.idx_options_mapping_opt_value_id;
|
|
-- DROP INDEX CONCURRENTLY IF EXISTS public.idx_permission_page_id;
|
|
-- DROP INDEX CONCURRENTLY IF EXISTS public.idx_phoneno_history_uuid;
|
|
-- DROP INDEX CONCURRENTLY IF EXISTS public.idx_tagdoc_history_docid;
|
|
-- DROP INDEX CONCURRENTLY IF EXISTS public.idx_tagged_docs_uuid;
|
|
-- DROP INDEX CONCURRENTLY IF EXISTS public.idx_template_mapping_portfolio_id;
|
|
-- DROP INDEX CONCURRENTLY IF EXISTS public.idx_template_mapping_template_id;
|
|
-- DROP INDEX CONCURRENTLY IF EXISTS public.idx_tracker_template_mapping_portfolio;
|
|
-- DROP INDEX CONCURRENTLY IF EXISTS public.idx_tracker_template_mapping_tracker;
|
|
-- DROP INDEX CONCURRENTLY IF EXISTS public.idx_tracker_sheet_layout_sheet;
|
|
-- DROP INDEX CONCURRENTLY IF EXISTS public.idx_tracker_template_sheets_tracker;
|