Hi,
I've tried some tests, by generating various datas in the res_currency_rate table.
If I generate res_currency_rate rows for unsused currencies, this doesn't influence the execution time.
if I generate more res_currency_rate for used currencies, this slower the query.
If I generate 100 rates, on a one per day basis, I get an execution time of 4.5 seconds
If I generate 100 rates, on a one per 3 days basis, execution time drops to 4 seconds
If I generate 100 rates, on a one per 6 days basis, execution time drops to 3.8 seconds.
I've executed following tests many times, to avoid cache or buffers related issues, each time after vacuuming table, ...
The execution time with a join of type daterange :
res_currency_rate.currency_id = pp.currency_id AND (res_currency_rate.company_id = s.company_id OR res_currency_rate.company_id IS NULL) AND daterange(res_currency_rate.name, res_currency_rate.date_end) @> COALESCE(s.date_order::timestamp with time zone, now())::date
is slower than the date comparison equivalent:
res_currency_rate.currency_id = pp.currency_id AND (res_currency_rate.company_id = s.company_id OR res_currency_rate.company_id IS NULL) AND res_currency_rate.name <= COALESCE(s.date_order::timestamp with time zone, now()) AND (res_currency_rate.date_end IS NULL OR res_currency_rate.date_end > COALESCE(s.date_order::timestamp with time zone, now()))