Re: plan with result cache is very slow when work_mem is not enough

Pavel Stehule писал 2021-05-07 21:45:
>> 
>> Samples: 119K of event 'cycles', 4000 Hz, Event count (approx.):
>> Overhead  Shared Object             Symbol
>> 79.20%  postgres                  [.] cache_reduce_memory
>> 1.94%  [kernel]                  [k] native_write_msr_safe
>> 1.63%  [kernel]                  [k] update_cfs_shares
>> 1.00%  [kernel]                  [k] trigger_load_balance
>> 0.97%  [kernel]                  [k] timerqueue_add
>> 0.51%  [kernel]                  [k] task_tick_fair
>> 0.51%  [kernel]                  [k] task_cputime
>> 0.50%  [kernel]                  [k] perf_event_task_tick
>> 0.50%  [kernel]                  [k] update_curr
>> 0.49%  [kernel]                  [k] hrtimer_active
>> 
>> Regards
>> 
>> Pavel

It is strange to see cache_reduce_memory itself consumes a lot of CPU.
It doesn't contain CPU hungry code.
It calls prepare_probe_slot, that calls some tuple forming. Then
it calls resultcache_lookup that may call to ResultCacheHash_hash
and ResultCacheHash_equal. And finally it calls remove_cache_entry.
I suppose remove_cache_entry should consume most of CPU time since
it does deallocations.
And if you compile with --enable-cassert, then remove_cache_entry
iterates through whole cache hashtable, therefore it reaches
quadratic complexity easily (or more correct M*N, where M - size
of a table, N - eviction count).

regards,
Yura Sokolov