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

On 5/7/21 9:06 PM, Yura Sokolov wrote:
> 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).
>

Yeah. I tried reproducing the issue, but without success ...

Not sure what's wrong, but --enable-cassert is one option. Or maybe
there's some funny behavior due to collecting timing info?

FWIW the timings on my laptop look like this:

work_mem=40MB 5065ms
work_mem=10MB 5104ms
resultcache=off 13453ms

So a very different behavior from what Pavel reported. But if I rebuild
with casserts, I get the same massive slowdown, so I guess that's it.

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company