matrix/docs/database-performance-audit.md

Database performance audit

Scope and safety

This audit covers the PostgreSQL schema, indexes, constraints, views, functions, and their relationship to src/matrix/nimble/conf/nimble.qry, with particular focus on Visit MIS and Dashboard.

The catalog inspection is read-only. No indexes, views, functions, or PostgreSQL settings have been changed. The audited local database is only about 30 MB and its core business tables currently contain almost no live data, so local execution times cannot represent production. Final index selection must be validated with production statistics and EXPLAIN (ANALYZE, BUFFERS) captured for representative parameters.

Inventory

• PostgreSQL: 18.4
• User tables: 120
• Normal views: 80
• Materialized views: 2
• User functions: 220
• Indexes: 187
• Function statistics: disabled (track_functions = none)
• Statement statistics: unavailable (pg_stat_statements is not preloaded)

The raw catalog exports are under target/database-audit/ and can be regenerated with matrix.nimble.DatabaseAuditRunner.

The subsequent analysis of the complete matrix.sql schema dump confirmed that portfolio_bank_branch and colony_allocation are normal views rather than tables. Index recommendations therefore target their underlying tables (portfolio_branch, bank_branch, portfolio, colony, belt_allocation, and verifier).

Generated index script: docs/production-indexes.sql. It contains preflight checks, high-confidence Visit MIS/Dashboard indexes, foreign-key support indexes, statistics refresh, postflight validation, and a commented rollback reference.

Application-to-database mapping

Feature	Application query	Database entry point
Visit MIS	Query306	monthvisitmis(...)
Dashboard initial load	Query473	dashboard()
Dashboard partial refresh	Query525	dashboardpartial(...)

nimble.qry contains about 460 application query definitions. Its most frequently referenced core relation is main, followed by document/bank-report relations, app_user, main_mainopr, verifier, temp_reporting, and portfolio_bank_branch. This is source frequency, not runtime frequency; production statement statistics are required for workload ranking.

Visit MIS findings

Execution shape

monthvisitmis(...) loops over each requested day and performs an aggregate against visit_mis. For every verifier/operator row returned, it runs a second query to find max(doneon). This is an N+1 query pattern: cost grows with both date range and verifier count.

vervisitmis(...) has the same per-verifier max(doneon) lookup and an additional per-verifier aggregate against verifier_monthly_data. It also dynamically switches between current_visit_mis and visit_mis.

Both functions build complete HTML reports inside PL/pgSQL through repeated text concatenation. This increases database CPU and memory usage and prevents the Java/UI layer from receiving structured rows. It is not the first optimization target, but should ultimately be replaced with a set-returning/data function and JSP rendering.

View expansion

visit_mis and current_visit_mis each expand into three UNION ALL branches for residence, office, and property visits. Every branch joins:

• main_mainopr, itself a join of main and main_operations by uuid
• portfolio_bank_branch by portbranch_id
• verifier by the visit-specific verifier column
• app_user by verifier operator
• riskalerts by (uuid, visit)

Every branch also performs a correlated zone aggregation against colony_allocation by verifier_id.

verifier_monthly_data is another three-branch UNION ALL over main, filtered by receivedate, visit flag, verifier, and same-address flag.

Index gaps and mismatches

These are candidates to validate on production, not DDL to run blindly:

1. riskalerts has separate indexes on uuid and visit, although the views join using both. A composite index beginning with (uuid, visit) matches the access path better.
2. colony_allocation is a view. Its base table belt_allocation has an index on verifier_id, but lacks an index beginning with belt_id for its join to colony; the generated script adds (belt_id, verifier_id).
3. main_operations has single-column indexes on oprsendon, sdoneon, sdone, and fileclosed, but the report predicates combine time/status data after joining by uuid. Production plans are needed to decide between composite and partial indexes.
4. main has individual verifier indexes and a receivedate index. verifier_monthly_data combines each verifier with receivedate, a visit flag, and same-address status, so one-column indexes may cause excessive filtering after index access.
5. portfolio_bank_branch is a view. Its base table portfolio_branch already has a primary key on portbranch_id; additional indexes are recommended for its portfolio/active and bank-branch access paths.
6. Several existing indexes duplicate or overlap: main.bank_branch_id has two btree indexes, and main_operations.uuid has both a primary btree index and a hash index. Production usage and index size/write cost should be checked before removing anything.

Primary query rewrite

The per-verifier max(doneon) must be folded into the main grouped query as max(doneon) FILTER (...). Monthly visit count/limit should also be pre-aggregated once and joined by verifier. This changes roughly 1 + N + N queries per report/day into one grouped query per day, or preferably one query for the entire requested range.

The date loop can be replaced by a single generate_series relation joined to the source data, or by grouping source rows by reporting date. The best variant depends on the required backlog semantics and will be verified against current output before replacement.

Dashboard findings

dashboard() executes at least ten child functions serially:

1. dashvisits()
2. dashscannerhourly()
3. dashoperatorhourly('opr')
4. dashrihhourly()
5. dashoperatorhourly('edp')
6. dashhourlytobesolved()
7. dashriskandphoto()
8. dashageing()
9. dash3monthsfigures(1, 0)
10. dash3monthsfigures(0, 0)

The major repeated work is:

• dashvisits() repeatedly counts the same expanded current_visit_mis and scanning_grid views for different categories.
• dashageing() scans scanning_grid about seven times to produce age buckets.
• dashboardpartial() calls dash3monthsfigures() twice for one widget refresh.
• Dashboard views expand through main, main_operations, temp_reporting, riskalerts, user/company/branch/portfolio lookups, and report tables.

These should be consolidated using conditional aggregates (count(*) FILTER (WHERE ...)) so each source is scanned once per widget. The initial dashboard response should then call fewer functions or one data-returning function. UI polling should avoid starting a refresh while the previous request is still running.

Schema and observability findings

• The database relies heavily on views and PL/pgSQL functions, including dynamic SQL. PostgreSQL dependency metadata cannot discover relations embedded only in dynamic SQL, so function definitions must be reviewed in addition to catalog dependencies.
• A number of foreign keys lack a leading index. These matter mainly for parent updates/deletes and child-side joins; each must be ranked by actual workload rather than indexed automatically.
• Core tables contain some redundant indexes, while some logical join keys have no visible supporting index.
• track_functions = none prevents attribution of time to the 220 functions.
• pg_stat_statements is unavailable, preventing reliable ranking by total time, mean time, calls, rows, and temporary I/O.

Required production evidence

Before applying point 5, collect during a normal busy period:

1. Row counts and relation/index sizes for the Visit MIS and Dashboard base tables.
2. pg_stat_user_tables and pg_stat_user_indexes snapshots before and after the period.
3. EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS, VERBOSE) for Visit MIS source aggregates using representative branch, verifier, and date-range values.
4. Slow statement data from pg_stat_statements after enabling it through the normal PostgreSQL change process.
5. Function timing after setting track_functions = pl (or all) through the normal production change process.
6. PostgreSQL logs with log_min_duration_statement set to a suitable temporary threshold, if extensions/settings cannot be enabled immediately.

Do not run EXPLAIN ANALYZE on production-changing functions. Extract and explain their underlying SELECT statements inside a read-only transaction.

Recommended implementation order for point 5

1. Enable observability and capture a baseline.
2. Add only production-plan-confirmed indexes, using CREATE INDEX CONCURRENTLY and checking duplicate/overlapping indexes first.
3. Rewrite Visit MIS to eliminate N+1 lookups and repeated date scans while preserving report totals exactly.
4. Consolidate Dashboard counts into single-pass aggregates.
5. Return structured data from new versioned functions and render HTML in the application; retain old functions during comparison.
6. Run old and new implementations side by side for representative dates/branches and compare every total before switching.
7. Load-test with at least 20 concurrent sessions and monitor database CPU, pool wait time, query latency, temporary I/O, and heap usage.