HEAP unique hash duplicate rejection inserts the key then deletes it, re-hashing full BLOB values (~1.8x collation hashing vs Aria)

Description

While A/B benchmarking MDEV-38975 in preview-13.1 (4411ba183b5 = branch minus all MDEV-38975 commits, vs 91f37a32f3e = branch tip) I found that DISTINCT over large BLOB values regresses at low concurrency, although it wins big at high concurrency:

Reproduced locally at 16 threads (AMD 7840HS 8c/16t): -29% / -31%. Profiled both sides with perf record -F 997 -g --call-graph fp (RelWithDebInfo, frame pointers). Both profiles are >83% inside UCA collation code, so I compared absolute per-iteration costs (997 Hz; samples ~ ms on-CPU), blob_case_c:

The new side does 1.8x the collation hashing for the identical DISTINCT. Comparisons are unchanged — the booster prefix compare costs the same on both sides.

Mechanism

1. Aria unique check (base-blob_case_c-children.txt): _ma_unique_hash hashes each incoming row once (70% incl.); duplicate candidates are found via the stored 4-byte unique hash, full value compare only on hash match (_ma_check_unique 19% incl.). No undo path.
2. HEAP (new-blob_case_c-children.txt): hp_rec_hashnr 86% incl., split between hp_write_key (58%) and hp_delete_key (38%; 41% in blob_mixed).
3. The delete comes from the documented contract of hp_write_key (hp_write.c:332-334): on HA_ERR_FOUND_DUPP_KEY "the record was still added and the caller must call hp_delete_key for it". So a duplicate insert pays: full-blob hash to insert the key (hp_write.c:485), chain compare to detect the dup, then hp_delete_key which hashes the same record in full a second time (hp_delete.c:277) just to locate the bucket to unlink from. The unlink/relink itself is cheap pointer work on hashes cached in HASH_INFO::hash_of_key.
4. blob_case_c is ~80% duplicates (10K rows, ~2K unique values of 20-50KB), so most rows pay the full-blob UCA scan roughly twice. Hence the 1.83x hashing and 1.48x total CPU.

At 64/128 threads HEAP's scalability (no shared pagecache serialization) buys the extra CPU back with interest — the same tests win 2x+ on a 88-thread box — but on an underloaded box the per-row CPU is the whole story.

Proposed fix directions

1. Probe-before-insert for HA_NOSAME hash keys: compute the hash once at the top of hp_write_key, search the chain (cheap hash_of_key pre-check, full compare only on hash match), and only if unique proceed with the linear-hash split and insertion, reusing the already-computed hash. On a dup, return HA_ERR_FOUND_DUPP_KEY with nothing inserted – the undo delete (and its second full-value hash) disappears; the dup case becomes hash + compares, same as a lookup. Contract change ripples to the write_key callers (heap_write/heap_update error paths no longer delete the failing hash key). The non-dup insert path cost is unchanged: it already pays exactly one full hash today.
2. (Already implemented in the base.) Storing the 32-bit hashnr in each HASH_INFO entry was done by de1765fb64d ("Optimize away calls to hp_rec_hashnr() by cashing hash", in main and this preview): duplicate checks, chain relinking, hp_delete_key chain surgery and linear-hash splits already compare cached hashes and never re-scan blob data. The one remaining full-value hash on the delete side is hp_delete_key hashing the incoming record to locate its bucket (hp_delete.c:277).
3. With 1 in place, the duplicate-rejection path stops calling hp_delete_key altogether, so the hp_delete.c:277 hash remains only for genuine DELETE/UPDATE of hash-keyed rows and for error-path rollbacks (e.g. multi-key dup, blob-write failure), where a precomputed hash can be passed in where one is available.

Attachments

• base-blob_case_c-children.txt / new-blob_case_c-children.txt — perf inclusive call trees, both sides
• base-blob_mixed-children.txt / new-blob_mixed-children.txt — same signature on the mixed-size workload (hp_delete_key 41% incl.)
• base-blob_case_c-flat-n.txt / new-blob_case_c-flat-n.txt — flat tables with absolute sample counts
• base-blob_case_c-flame.html / new-blob_case_c-flame.html — flamegraphs
• base-blob_case_c.log / new-blob_case_c.log / base-blob_mixed.log / new-blob_mixed.log — QPS logs of the recorded 120s runs