Queries to last cache with large IN list predicates seem slow

[hiltontj]

The performance team has observed that when querying the last cache with IN clauses with ~100 elements leads to response times on the order of ~30ms.

For example,

SELECT * FROM last_cache('table_name', 'cache_name') where key_columns IN ('random value 1', ... 'random value 100');

This issue is for tracking whether or not this can be improved.

[MaduMitha-Ravi]

Adding the stats and details in here.

Observations - Single and Multiple series (10 and 100 series) on PRO

• Single series and 10 series last value cache based query the results are returned in less than 10 ms as expected
• For 100 series we observe results returned is less than 40 ms
• Resource usage does not yield any red flags (memory is 22%)

Note: Not necessarily a problem, I believe Paul did mention for 100 series the expected latency is 20ms (+10ms for every 100 series).

[pauldix]

To be fair, the numbers I mentioned were just guesses at what I thought good performance might look like. We should still look into this though :)