[FIX] account.invoice.report: performance problem in currency rate computation

The SQL view implementing the "Invoice Analysis" report
JOINs the res.currency.rate table in order to obtain the
correct currency rate to convert each invoice line amount
in the same currency.

The matching of the rate needs to be done on the date
of that rate (`name` column) - the last rate preceding
the invoice date is presumed to be the right one.
However there is no simple way to make a direct JOIN between
account.invoice.line and res.currency.rate with a single
match, without using an ORDER BY clause and LIMIT 1.
This requires a costly SUBSELECT query for each invoice
line, quickly becoming prohibitive.

Through the use of PostgreSQL's Common Table Expressions
(CTE) it is possible to construct a temporary table
with the rates' start and end date of application.
This temporary table can then be used in a direct
JOIN with account.invoice.line, delivering much better
performance (no SUBSELECT needed for each invoice line)

On a database with 50k invoice lines this makes invoice
analysis return results in less than 800 ms instead of
10+ seconds.

1 files changed, 13 insertions, 8 deletions

diff --git a/addons/account/report/account_invoice_report.py b/addons/account/report/account_invoice_report.py
index 3d86d2bfe14..8be9e876f78 100644
--- a/addons/account/report/account_invoice_report.py
+++ b/addons/account/report/account_invoice_report.py
@@ -204,18 +204,23 @@ class account_invoice_report(osv.osv):
         # self._table = account_invoice_report
         tools.drop_view_if_exists(cr, self._table)
         cr.execute("""CREATE or REPLACE VIEW %s as (
+            WITH currency_rate (currency_id, rate, date_start, date_end) AS (
+                SELECT r.currency_id, r.rate, r.name AS date_start,
+                    (SELECT name FROM res_currency_rate r2
+                     WHERE r2.name > r.name AND
+                           r2.currency_id = r.currency_id
+                     ORDER BY r2.name ASC
+                     LIMIT 1) AS date_end
+                FROM res_currency_rate r
+            )
             %s
             FROM (
                 %s %s %s
             ) AS sub
-            JOIN res_currency_rate cr ON (cr.currency_id = sub.currency_id)
-            WHERE
-                cr.id IN (SELECT id
-                          FROM res_currency_rate cr2
-                          WHERE (cr2.currency_id = sub.currency_id)
-                              AND ((sub.date IS NOT NULL AND cr2.name <= sub.date)
-                                    OR (sub.date IS NULL AND cr2.name <= NOW()))
-                          ORDER BY name DESC LIMIT 1)
+            JOIN currency_rate cr ON
+                (cr.currency_id = sub.currency_id AND
+                 cr.date_start <= COALESCE(sub.date, NOW()) AND
+                 (cr.date_end IS NULL OR cr.date_end > COALESCE(sub.date, NOW())))
         )""" % (
                     self._table,
                     self._select(), self._sub_select(), self._from(), self._group_by()))