Hi Teodor,
> 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
These changes look very promising. Unfortunately the proposed patches
conflict with each other regardless the order of applying:
```
error: patch failed: src/backend/utils/cache/typcache.c:356
error: src/backend/utils/cache/typcache.c: patch does not apply
```
So it's difficult to confirm case 4, not to mention the fact that we
are unable to test the patches on cfbot.
Could you please rebase the patches against the recent master branch
(in any order) and submit the result of `git format-patch` ?
--
Best regards,
Aleksander Alekseev
