type cache cleanup improvements

Teodor Sigaev <teodor@sigaev.ru>

From: Teodor Sigaev <teodor@sigaev.ru>

To: Pgsql Hackers <pgsql-hackers@postgresql.org>

Date: 2024-02-29T10:26:07Z

Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

Maintain RelIdToTypeIdCacheHash in TypeCacheOpcCallback()
- bb78e4267817 18.0 landed
Fix concurrrently in typcache_rel_type_cache.sql
- aa1e898dea66 18.0 landed
Avoid looping over all type cache entries in TypeCacheRelCallback()
- b85a9d046efd 18.0 landed
- c14d4acb8134 18.0 landed
Update header comment for lookup_type_cache()
- c1500a1ba7e1 18.0 landed
Revert: Avoid looping over all type cache entries in TypeCacheRelCallback()
- 8daa62a10c91 18.0 landed
Introduce hash_search_with_hash_value() function
- d0f020037e19 18.0 landed
Optimize InvalidateAttoptCacheCallback() and TypeCacheTypCallback()
- 40064a8ee1b3 18.0 landed
Refactor initial hash lookup in dynahash.c
- cc5ef90edd80 17.0 landed
Rationalize and improve error messages for some jsonpath items
- 92d2ab7554f9 17.0 cited
Avoid race in RelationBuildDesc() affecting CREATE INDEX CONCURRENTLY.
- fdd965d074d4 15.0 cited
Represent Lists as expansible arrays, not chains of cons-cells.
- 1cff1b95ab6d 13.0 cited

Attachments

hash_seq_init_with_hash_value.patch (text/x-patch) patch
mapRelType.patch (text/x-patch) patch
custom_types_and_array.sql (application/sql)

Hi!

I'd like to suggest two independent patches to improve performance of type cache 
cleanup. I found a case where type cache cleanup was a reason for low 
performance. In short, customer makes 100 thousand temporary tables in one 
transaction.

1 mapRelType.patch
   It just adds a local map between relation and its type as it was suggested in 
comment above TypeCacheRelCallback(). Unfortunately, using syscache here was 
impossible because this call back could be called outside transaction and it 
makes impossible catalog lookups.

2 hash_seq_init_with_hash_value.patch
  TypeCacheTypCallback() loop over type hash  to find entry with given hash 
value. Here there are two problems: 1) there isn't  interface to dynahash to 
search entry with given hash value and 2) hash value calculation algorithm is 
differ from system cache. But coming hashvalue is came from system cache. Patch 
is addressed to both issues. It suggests hash_seq_init_with_hash_value() call 
which inits hash sequential scan over the single bucket which could contain 
entry with given hash value, and hash_seq_search() will iterate only over such 
entries. Anf patch changes hash algorithm to match syscache. Actually, patch 
makes small refactoring of dynahash, it makes common function hash_do_lookup() 
which does initial lookup in hash.

Some artificial performance test is in attachment, command to test is 'time psql 
< custom_types_and_array.sql', here I show only last rollback time and total 
execution time:
1) master 92d2ab7554f92b841ea71bcc72eaa8ab11aae662
Time: 33353,288 ms (00:33,353)
psql < custom_types_and_array.sql  0,82s user 0,71s system 1% cpu 1:28,36 total

2) mapRelType.patch
Time: 7455,581 ms (00:07,456)
psql < custom_types_and_array.sql  1,39s user 1,19s system 6% cpu 41,220 total

3) hash_seq_init_with_hash_value.patch
Time: 24975,886 ms (00:24,976)
psql < custom_types_and_array.sql  1,33s user 1,25s system 3% cpu 1:19,77 total

4) both
Time: 89,446 ms
psql < custom_types_and_array.sql  0,72s user 0,52s system 10% cpu 12,137 total


-- 
Teodor Sigaev                                   E-mail: teodor@sigaev.ru
                                                    WWW: http://www.sigaev.ru/