Hi, a currency rate can have no company, and is then applicable to currencies which have no rate specific for the company.
Le dim. 10 juin 2018 à 17:24, Adrian Klaver <adrian.kla...@aklaver.com> a écrit : > On 06/05/2018 07:58 AM, Nicolas Seinlet wrote: > > 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 took another look at the query and got to wondering about the snippet > below: > > > LEFT JOIN (SELECT > r.currency_id, COALESCE(r.company_id, c.id) AS > company_id, r.rate, > r.name AS date_start, r.date_end > FROM > res_currency_rate r > JOIN > res_company c > ON > r.company_id IS NULL OR r.company_id = c.id > ) cr > ON > cr.currency_id = pp.currency_id > AND > (cr.company_id = s.company_id OR cr.company_id IS NULL) > AND > daterange(cr.date_start, COALESCE(cr.date_end, now()::date)) @> > COALESCE(s.date_order::timestamp with time zone, now())::date > > In particular: > > FROM > res_currency_rate r > JOIN > res_company c > ON > r.company_id IS NULL OR r.company_id = c.id > > Are there NULL company_id values in res_currency_rate? > > If so I am trying to figure out how the JOIN to res_company would work > in that situation. > > What happens if eliminate the r.company_id IS NULL? > > > > > 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 <http://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 > > <http://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())) > > > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >