Report: Redundant / Overlapping Index 

The report is produced by util/idxoverlap.p
(invoked from ssg/sausage20.i dba_report alongside the other
DB-analysis reports).

One file is written per database, named <shortname>.idxoverlap.rpt and
placed in $RPTDIR. A companion file <shortname>.redundant.rpt is
written at the same time, listing only the indexes flagged as fully
redundant plus a “wasting N blocks” total.

Invocation

The program is normally run as part of dba_report. It can also be invoked
standalone:

[m]bpro <dbname> -p util/idxoverlap.p > idxoverlap.err &

If pt_shortname is unset it defaults to ldbname(1). If pt_rptdir is
unset it falls back to $RPTDIR, then to ..

How the overlap analysis works

For each user table (_hidden = no, optionally filtered by tblFilter,
which defaults to *):

1. Build temp-tables — ttIdxHdr (one row per index, capturing name,
   primary/unique/word flags, and depth) and ttIdxDet (one row per index
   key component). Short indexes get blank placeholder components so every
   index is the same depth (jx) within a table.
2. Sort by leading components — the program walks the components level
   by level (depth 1 … jx). At each level it groups indexes by the field
   name at that depth, assigning each group an idxOrder. Indexes that
   share the same field at depth 1 cluster together; if they also share at
   depth 2, they stay together; and so on. The result is that any indexes
   sharing leading components end up adjacent in the report.
3. Count overlap — for each index, count how many of its key components
   share a (idxKeyNum, idxFamily) pair with at least one other index in
   the table. That count is idxOverlap, appended to the index description
   as a visual cue.
4. Classify — for each index, compare idxDepth against idxOverlap:
   • idxDepth = idxOverlap → fully redundant (every component
     overlaps another index)
   • idxOverlap > 0 and < idxDepth → partially redundant
   • idxOverlap = 0 → stands alone

Layout

Per table:

<tablename> ====================================================================================================================

<idxName1> <flags> <overlap>  <depth>  <idxName2> <flags> <overlap>  <depth>  ...   (up to 4 indexes per row)
------------------------------  ------------------------------  ------------------------------
  <fam> <field1>                   <fam> <field1>                   <fam> <field1>
  <fam> <field2>                   <fam> <field2>                   <fam> <field2>
  ...

Each table starts with its name padded out to 132 columns of =. Then the
table’s indexes are tiled four wide (the constant n at the top of
idxoverlap.p); if the table has more than four indexes the next set of
four is rendered below.

Index header line

For each index, the header shows:

• Name — _index._index-name.
• Flags — appended to the name:
  • p — primary index (recid( _index ) = _file._prime-index)
  • u — unique
  • w — word index
• Overlap count — the integer number of components that overlap with
  another index.
• Depth — the number of components in the index, displayed at the right
  edge of the column.

Separator line

Each index header is followed by a 30-character separator that doubles as a
classification marker:

• ------------------------------ (dashes) → stands alone (no overlap)
• ++++++++++++++++++++++++++++++ (pluses) → partial overlap with at
  least one other index
• ****************************** (asterisks) → fully redundant —
  every component overlaps another index. These are the indexes that
  should usually be dropped.

Component lines

Below each index, one line per component. Each line is prefixed by the
component’s family number (the order it received during the leading-
component sort): components from different indexes that share a family
number at the same depth are sharing that field. A trailing < marks a
descending component. Blank slots appear when an index is shorter than
the deepest index in the table.

The companion *.redundant.rpt

While generating the main report, every fully-redundant index (* row)
also gets a line in <shortname>.redundant.rpt:

<table> <indexDescription> <blocks>

The block count is read on the fly via:

grep -i '"<table>.<index>"' ./dbanalys/<shortname>.idx | awk '{print $5}'

against the most recent dbanalys index dump (if dbanalys/<shortname>.idx
exists). Missing input file → blocks left blank. The file ends with a
summary line:

N redundant indexes wasting a total of M blocks.

Side effects

• Reads dbanalys/<shortname>.idx for index block sizes (best-effort —
  absence is tolerated).
• Writes <shortname>.idxoverlap.rpt and <shortname>.redundant.rpt to
  $pt_rptdir.
• Unlike the other DB-analysis reports, idxoverlap.p does not call
  lib/uploader.p itself; the file ships as part of the broader
  dba_report flow.

Reading the report

• Look for separator rows of * — those indexes are fully redundant; the
  companion redundant.rpt summarizes how much space they waste.
• A row of + flags partial overlap. These need judgement: an index with a
  few extra trailing components on top of a shared prefix may genuinely be
  needed by a different query path, or it may be droppable in favor of the
  shorter sibling.
• The family numbers to the left of component names are the quick visual
  tell: when the same family number appears under multiple indexes at the
  same depth, those indexes share that field at that position.
• A wide block of indexes with matching leading family numbers usually
  means a table accumulated overlapping indexes over time — consolidating
  them is one of the highest-leverage tunings on most production
  databases.