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.klaver@xxxxxxxxxxx
