Re: Sort is generating rows

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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>