Job index-missing-and-unused-review-applied-and-measured · revised 11 October 2026
Review the indexes in one database and apply the agreed changes, measured
The indexes of one PostgreSQL or MySQL database are reviewed against its real queries; up to five agreed changes are applied on staging and measured before and after.
You might be seeing
- Tables carry many indexes and nobody knows which are used
- Several different queries scan large tables where an index looks like it should exist
- Inserts and updates have slowed as indexes were added over the years
No passwords, keys, card details or admin invites needed to start.
What usually happened
Indexes accumulate by habit. Some serve no query and cost write time and disk; others are missing for the statements that now matter. Usage counters are easy to misread: they reset after a crash, a restore or a point-in-time recovery, they miss monthly jobs, they cover only the server you read them from, and a low count on a primary key or unique index says nothing about whether it can go. Deciding what to add or drop needs the real statements, the plans and a measurement, not a rule of thumb.
Who it’s for: An engineering manager or founder responsible for a database that has grown organically and has indexes nobody can account for.
Usually starts when: Writes feel heavy, storage keeps growing, or a handful of queries are slow, and the team suspects both missing indexes and indexes that no query uses.
The result: You receive a ranked list of index findings for one database, and up to five of the changes you approve are applied on a staging copy and measured against the statements that motivated them, with a script to undo each.
Check whether this job fits
These checks tell us whether the evidence for an index review exists. They need no access to your data.
Checks you can run yourself
See how old your index usage counters are
On PostgreSQL, ask your engineer to run this on each server that receives queries (the primary and any replica or reporting host), during a quiet moment. It only reads statistics.
SELECT d.datname, d.stats_reset, pg_postmaster_start_time() AS server_started FROM pg_stat_database d WHERE d.datname = current_database();Look for: The date of the last statistics reset and the date the server last started. PostgreSQL's manual says all counters are reset after a crash, after starting from a base backup and after point-in-time recovery, but it does not say the reset date above moves in those cases, so an old date can sit beside young index counters. Ask whether any crash, restore or recovery happened since the older of the two dates, and send the dates, not the data. The last-scan time per index needs PostgreSQL 16 or later.
What you get
- A written findings list, each item with the statements it relates to, the evidence and a recommendation of apply, watch or leave
- Create and drop scripts for each approved change, written to avoid blocking writes where the engine allows
- Before and after timings and plans for each applied change, and the write cost measured on the affected tables
- A short note on how long the usage counters had been collecting on each server and what would invalidate them
Included
- One PostgreSQL or MySQL database and the set of statements you supply from its slow query log or statement statistics
- A review of existing indexes against those statements and the usage counters from every server that receives queries, with the limits of the counters stated
- A ranked list of findings: indexes to add, indexes to consider dropping, and duplicates or near-duplicates
- Up to five approved changes applied on staging and measured before and after, each with an undo script
Not included
- Applying any change to production; you do that after a restore point you have tested
- Rewriting queries, changing the schema or partitioning tables
- More than five applied changes, or more than one database
- Dropping any index that enforces a primary key or unique constraint
- Guaranteeing production results from staging measurements
How we know it’s done
Agreed with you before work starts. Each check produces evidence you keep.
Every finding names the statements or constraints it relates to, the usage evidence with its collection period, and a recommendation of apply, watch or leave. Every drop recommendation rests on counters from every server that receives queries; a finding with counters from fewer is marked watch.
Evidence: The findings list, which you can compare with your own statement and counter exports from each server.
For each applied change, the statement that motivated it is measured on the staging copy before and after, with the plan, over at least five runs.
Evidence: Before and after timings and plans for each change as plain text.
Write cost on each affected table is measured before and after, and each undo script restores the previous index set when run on staging.
Evidence: Write-cost comparison and a staging log of each undo.
No recommended drop affects an index that enforces a primary key or unique constraint.
Evidence: The list of dropped indexes with the constraint check for each.
Sign-off. You review the findings and the staging measurements, approve in writing and decide what to apply in production. Payment follows sign-off, not production deployment.
If it fails. If an applied change does not meet the improvement agreed for it on the staging copy, it is withdrawn from the applied list and not charged. If the evidence is too thin for a recommendation, we say so rather than guess.
When it fits, and when we stop
It fits when
- PostgreSQL or MySQL on a version you can name, with statement statistics or the slow query log available for a representative period; the last-scan time (last_idx_scan) exists only from PostgreSQL 16, so on an older PostgreSQL we use the scan counts and the reset dates alone
- A staging copy with realistic row counts and no real personal data
- You can export the usage counters and the list of slow statements without sharing customer values, and the counters from every server that receives queries: the primary and any read replica or reporting database. An index is only called unused when every such server shows no use; where counters from one of them are missing, the finding is marked watch, not drop
- An engineer on your side will review the scripts and choose the production window
We stop and tell you if
- The statistics or slow query log have been collected for too short a time to be representative, and the period cannot be extended
- The counters were reset recently and there is no other evidence of which indexes are used
- The database is not PostgreSQL or MySQL
- The main finding is that the workload needs a redesign rather than index changes; we say so and stop
What could go wrong
Each applied change has a script that reverses it: a created index is dropped, and a dropped index is recreated from its saved definition. We apply nothing to production, so your production indexes are untouched until you choose a window.
Scroll the table sideways to read it all.
| Risk | How we handle it |
|---|---|
| An index that looks unused serves a monthly or yearly job and is recommended for removal. | Each drop recommendation states the evidence period and any scheduled jobs you named; where the period is short the item is marked watch, not drop. |
| An index used only by a read replica or reporting server looks unused on the primary and is recommended for removal. | A drop recommendation needs counters from every server that receives queries, each with its reset date; where one is missing the finding is marked watch. |
| A new index slows writes more than it helps reads. | We measure write cost on staging for each applied change and report it next to the read gain. |
| Building an index in production blocks writes. | Scripts use the engine's non-blocking build where it exists and say when it does not; your engineer picks the window. |
A second reviewer checks that no index backing a primary key or unique constraint is recommended for dropping, that every drop recommendation states the evidence period, and that measurements were repeated. Your engineer approves each change before it is applied.
How we deliver
We arrange the work and independent review, then show you the result against the agreed checks. You keep authority over your systems.
- Agree the database, the evidence period, the statements to weigh and the staging copy in writing
- Read the usage counters from every server that receives queries, with their reset dates, and match indexes to the statements and constraints they serve
- Rank findings: add, consider dropping, duplicate; mark each with the evidence and its limits
- You approve up to five changes; apply each on staging using a non-blocking build where the engine allows, and measure before and after
- Measure write cost on each affected table and confirm each undo script restores the previous state
- Independent review of findings and measurements, then hand over the scripts and notes
This is a one-off job, not emergency cover or a subscription. We confirm eligibility, the total price, a start window and a delivery date before you accept. Work starts only after agreed inputs, secure access and any permissions are in place. Hosting and database charges are excluded unless the written quote includes them. No charge or booking is created by an enquiry.
Need to keep it working?
Discuss a monthly slow-statement and growth review if you want index usage watched after this one-off pass.
Ongoing work is separately scoped and quoted: no monitoring, response-time guarantee or automatic subscription is included in this job.
Explore an ongoing engineering lane, or mention the responsibility you need in your enquiry.
What you can check
This is a new service. We have not delivered this job for a client yet.
Other ways to get this done
- PostgreSQL's own guide to examining index usage explains running ANALYZE first, using realistic data, and testing with sequential scans disabled. www.postgresql.org
- MySQL's sys schema includes a view of indexes with no recorded events; its manual says it is most useful once the server has run long enough for the workload to be representative. dev.mysql.com
Questions
Can you tell me which indexes are safe to drop?
We tell you which show no use over a stated period and what could make that misleading. The decision to drop stays with you, and a primary key or unique index is never on the drop list.
Why only five applied changes?
Each applied change is measured on its own so you can see what it earned. A longer list is quoted separately.
Why do you need the counters from our replica too?
Usage counters belong to the server that ran the queries. An index that only a read replica or a reporting database uses shows no scans on the primary, so dropping it on that evidence could break a report. Without every server's counters, a possible drop is marked watch.
Do you need our production access to read the counters?
No. Your engineer exports the counters and statement summary and sends them with literal values removed.
Send an enquiry
Send us
- The engine and version, and how long its statistics or slow query log have been collecting
- Roughly how many tables and indexes the database has, and the size of the largest tables
- Whether the server has restarted, crashed or been restored from a backup recently
- Whether any read replica, reporting database or analytics tool also runs queries against these tables
- Do not send credentials, a dump or real values in the first enquiry
Later, once you agree
- An export of the statement statistics or slow-query summary, with literal values removed
- The index definitions and usage counters from every server that receives queries (the primary and any read replica or reporting host), exported by your engineer, each labelled with the server and its last reset or restart date
- A staging database restored from a backup, with personal data removed or replaced
- Who approves each finding and who applies it later
You own the database, the code and every credential. We work on a copy you prepare, such as a staging database restored from a backup with personal data removed or replaced, and we hand work back as a pull request or a script. We never ask for production passwords, and we do not connect to your production database. You apply any change to production yourself, after a restore point that you have tested.
Email fallback: open your mail app
If website submission is unavailable, review and send the fallback email yourself. An email fallback is not a website receipt. Or write to hello@syntheticindustry.ai with “index-missing-and-unused-review-applied-and-measured” as the subject.