[DERBY-3790] Investigate if request for update statistics can be skipped for certain kind of indexes, one instance may be unique indexes based on one column.

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.5.1.1
• Fix Version/s: 10.9.1.0
• Component/s: Store
• Bug behavior facts: Performance
• Assignee: Kristian Waagan
• Reporter: Mamta A. Satoor
• Created: 21/Jul/08 18:48
• Updated: 03/Sep/14 08:31
• Resolved: 23/May/12 11:12

Description

DERBY-269 provided a manual way to update the statisitcs. There was some discussion in that jira entry for possibly optimizing the cases where there is no need to update the statistics. I will enter the related comments from that jira entry here for reference.

**************************
Knut Anders Hatlen - 18/Jul/08 12:39 AM
If I have understood correctly, unique indexes always have up to date cardinality statistics because cardinality == row count. If that's the case, one possible optimization is to skip the unique indexes when SYSCS_UPDATE_STATISTICS is called.
**************************

**************************
Mike Matrigali - 18/Jul/08 09:48 AM
is the cardinality of a unique index 1 or is it row count?

It is also more complicated than just skipping unique indexes, it depends on the number of columns in the index because
in a multi-column index, multiple cardinalities are calculated. So for instance on an index on columns A,B,C there are
actually 3 cardinalities calculated:
A
A,B
A,B,C

I agree that the calculation of cardinality of A,B,C could/should be short circuited for a unique index.
**************************

**************************
Knut Anders Hatlen - 18/Jul/08 03:25 PM
Mike,
It looks to me as if the cardinality is the number of unique values, so I think the cardinality of a unique index is equal to its row count (for the full key, that is). You're right that we can't short circuit it if we have a multi-column index. I don't know if it's worth the extra complexity to short circuit the A,B,C case, since we'd have to scan the entire index anyway. For a single-column unique index it sounds like a good idea, though.
**************************

Issue Links

• is related to: DERBY-6045 in list multi-probe by primary key not chosen on tables with >256 rows
• is related to: DERBY-5067 Performance regression tests should populate tables before creating indexes
• is related to: DERBY-6156 Starting Derby 10.9, we do not not keep statistics for single-column unique index. Check the code to see optimizer calculates correct cost even with the missing stats for such indexes
• relates to: DERBY-6283 indexStat daemon processing tables over and over even when there are no changes in the tables in soft upgraded database.
• relates to: DERBY-4771 Continue investigation of automatic creation/update of index statistics