Are the IndAdd and IndLookup properties reversed?

[Breck_Carter]

Here's what they look like for a 16.0.0.2052 database busy doing SELECT and UPDATE operations, no INSERTs...

SELECT DB_PROPERTY ( 'IndAdd' ), DB_PROPERTY ( 'IndLookup' );

DB_PROPERTY('IndAdd'),DB_PROPERTY('IndLookup')
'401941','150'

SELECT Number, PropName, Value 
  FROM sa_conn_properties() 
 WHERE PropName IN ( 'IndAdd', 'IndLookup' )
 ORDER BY Number, PropName;

Number,PropName,Value
3,'IndAdd',80
3,'IndLookup',0
4,'IndAdd',9990
4,'IndLookup',0
5,'IndAdd',9990
5,'IndLookup',0
6,'IndAdd',9990
6,'IndLookup',0
7,'IndAdd',9990
7,'IndLookup',0
8,'IndAdd',9990
8,'IndLookup',0
9,'IndAdd',9990
9,'IndLookup',0
10,'IndAdd',9990
10,'IndLookup',0
...

CREATE TABLE DBA.inventory ( -- 1,000,000 rows, 28M total = 27M table + 40k ext + 960k index, 30 bytes per row
   item_id         /* PK        */ INTEGER NOT NULL DEFAULT autoincrement,
   item_count                      INTEGER NOT NULL,
   item_name                       VARCHAR ( 1024 ) NOT NULL,
   updated_at                      TIMESTAMP NOT NULL DEFAULT timestamp,
   last_modified   /*         X */ TIMESTAMP NOT NULL DEFAULT timestamp,
   CONSTRAINT ASA78 PRIMARY KEY ( -- 356k
      item_id )
 );
-- Parents of DBA.inventory
-- none --
-- Children
-- none --
CREATE INDEX inventory_ml ON DBA.inventory ( -- 604k
   last_modified );

  DO WHILE lb_continuing

   ll_pkey = Rand ( 100 ) * 10000 + il_thread_index;

   ls_sql = "UPDATE inventory SET item_count = item_count + 1 WHERE item_id = " + String ( ll_pkey )

   EXECUTE IMMEDIATE :ls_sql USING itr_sqlca;

   IF SQLCA.SQLCODE <> 0 THEN

    UPDATE thread_status 
     SET thread_status.status = 'error',
       SQLDBCode = :SQLCA.SQLDBCode,
       SQLErrText = :SQLCA.SQLErrText
     WHERE thread_status.thread_index = :il_thread_index
     USING itr_sqlca;

    COMMIT USING itr_sqlca;

    lb_continuing = FALSE

   END IF   

[Former Member]

I am seeing index lookups and adds getting counted in versions 12.0.1 and 16 when I query GroupO.Products in the sample databases that are shipped with each version. And they seem to be doing so appropriately for me.

Index lookups can fail to count depending upon what plans are getting executed ... is that a possibility in your case? [table scans for everyone ... anyone?] FWIW I can suggest that your updates are likely not adding to the lookup count much if that is your expectation; I can run inserts and updates here without any.

It may help if we know the schemas and queries you are counting here.

[Breck_Carter]

The application is an artificial benchmark which is executing no (0, zero, nada) INSERT operations, just UPDATE. All the connections are doing the same thing so the counts are all the same: IndAdd 9990. The "9990" makes sense, but "IndAdd" does not... how do UPDATE operations contribute to IndAdd?

The UPDATE operations all use the primary key, so IndLookup 0 also makes no sense... IndLookup 9990 would make sense.

See the question for the schema and a snippet of code that is heavily executed.

[Breck_Carter]

...and now I think I see it.

Somehow a stupid extra redundant bogus last_modified column with DEFAULT TIMESTAMP and an index has been added to the table... which means every steenking UPDATE is going to delete and create an index entry.

Does that make sense? The effect, not the existence, of the column with index.

---

After dropping the index (not the column) the rate of IndAdd operations dropped from over 16,000 per second down to about 100 per second.

...the moral of the story being, unexpectedly large IndAdd values may indicate UPDATE operations that change indexed columns, something that may not be desirable from a performance point of view.

[Former Member]

That's pretty consistent with my tests too.

As to "...how do UPDATE operations contribute to IndAdd?" they should do so only if the update changed a value in an indexed column. Of course trigger actions (,etc) could also be contributing.

If you are contributing to the IndAdd counter that way you don't also contribute to the IndLookup. That seems to be the current implementation. {I haven't found a case where any insert/update or delete contributes to that counter and the code seems to support that partitioning.}

It might make more sense if the intent of these counters were better documented. I take the Add to be a measure of index maintenance and the Lookup to be mostly for aiding in the analysis of index utilization for query performance purposes; which would map to a measure of cost vs benefit from indexing.

[Breck_Carter]

If memory serves, both numbers are almost always vanishingly small even under heavy workloads, EXCEPT in unexplained cases where IndAdd is astonishingly high.

Thinking back, it is quite possible all of those unexplained cases could have been UPDATE statements that affect indexed columns, something one might want to avoid if one is interested in Maybach-style performance.

FWIW the following has been added to the Foxhound Help for "Index Adds": "Large values may be caused by updates that are changing index columns."

[VolkerBarth]

Just to clarify: So "IndAdd" would also count dropped entries? Would an UPDATE of an index entry count twice?

Your take seems to make a good enhancement to the docs, particularly as that refers to the usage ("What does it tell?") and not only the technical source.

[Breck_Carter]

It would be nice to have better descriptions of many performance-related statistics. In the meantime, yes, since there is no "IndDel" statistic it may be safe to assume that "IndAdd" really means "index modification"... said the blindfolded man about the elephant 🙂