I have a customer who says users have queries run slow the first try, then much faster when run again. This is an Enterprise mode cluster so I am trying to find testable ideas why this is happening (caching? projections? system load? etc). Here is an example:
vdb1=> select MAX(date) from opt.vol_curve ;
MAX
2020-08-31
(1 row)
Time: First fetch (1 row): 73814.670 ms. All rows formatted: 73814.706 ms
vdb1=> select MAX(date) from opt.vol_curve ;
MAX
2020-08-31
(1 row)
Time: First fetch (1 row): 43.206 ms. All rows formatted: 43.233 ms
vdb1=> select MAX(date) from opt.vol_curve ;
MAX
2020-08-31
(1 row)
Time: First fetch (1 row): 41.957 ms. All rows formatted: 41.987 ms
Additional info from customer:
I was given a query of the form
select * from table limit 3
And was trying to reproduce their slowness. Somehow I got query_requests as the test, so its not a good example.
I was examining that to look for overlapping queries and such while trying to troubleshoot.
People generally say things like “The first run if slow and then its fast after that”.
I would expect if the system is quite, a query limited to a few rows would be fast.
If there are no joins, would projection design really have much impact?
Resource pool may be an area of attach as we mostly use ‘general’.
