openerp-expert-accounting team mailing list archive
-
openerp-expert-accounting team
-
Mailing list archive
-
Message #01770
[Bug 1000195] Re: Performance issue in invoice analysis
This is a real design bug.
To analyse any query, please run:
EXPLAIN ANALYSE yourSQL query
i.e.
EXPLAIN ANALYSE
SELECT min(account_invoice_report.id) AS id, count(account_invoice_report.id) AS categ_id_count,"account_invoice_report".categ_id, sum("account_invoice_report"."nbr") AS nbr, sum("account_invoice_report"."product_qty") AS product_qty, sum("account_invoice_report"."price_total") AS price_total FROM "account_invoice_report" WHERE ((((account_invoice_report."date" <= '2012-05-18') AND (account_invoice_report."date" >= '2012-01-01')) AND ((account_invoice_report."state" not in ('draft','cancel')) OR account_invoice_report."state" IS NULL)) AND ((account_invoice_report."type" = 'out_invoice') OR (account_invoice_report."type" = 'out_refund'))) GROUP BY "account_invoice_report".categ_id
On my server, even with on 3000 clients ...
the query returns a result in 20 seconds.
"HashAggregate (cost=267661920.65..267661925.15 rows=200 width=32) (actual time=20619.984..20619.986 rows=2 loops=1)"
" -> GroupAggregate (cost=2363456.42..267585641.15 rows=3051180 width=184) (actual time=20459.610..20617.810 rows=1257 loops=1)"
" -> Sort (cost=2363456.42..2371084.37 rows=3051180 width=184) (actual time=20459.384..20459.784 rows=1265 loops=1)"
" Sort Key: ail.product_id, ai.date_invoice, ai.id, cr.rate, (to_char((ai.date_invoice)::timestamp with time zone, 'YYYY'::text)), (to_char((ai.date_invoice)::timestamp with time zone, 'MM'::text)), (to_char((ai.date_invoice)::timestamp with time zone, 'YYYY-MM-DD'::text)), ai.partner_id, ai.payment_term, ai.period_id, u.name, ai.currency_id, ai.journal_id, ai.fiscal_position, ai.user_id, ai.company_id, ai.type, ai.state, pt.categ_id, ai.date_due, ai.address_contact_id, ai.address_invoice_id, ai.account_id, ai.partner_bank_id, ai.residual, ai.amount_total, u.uom_type, u.category_id"
" Sort Method: quicksort Memory: 701kB"
" -> Merge Join (cost=0.00..1492526.05 rows=3051180 width=184) (actual time=11.478..20452.364 rows=1265 loops=1)"
" Merge Cond: (ail.invoice_id = ai.id)"
" -> Nested Loop Left Join (cost=0.00..1475.72 rows=3084 width=86) (actual time=0.067..25.090 rows=2646 loops=1)"
" Join Filter: (u.id = ail.uos_id)"
" -> Nested Loop Left Join (cost=0.00..1058.26 rows=3084 width=68) (actual time=0.047..15.025 rows=2646 loops=1)"
" -> Index Scan using account_invoice_line_invoice_id_index on account_invoice_line ail (cost=0.00..170.50 rows=3084 width=64) (actual time=0.034..3.749 rows=2646 loops=1)"
" -> Index Scan using product_template_pkey on product_template pt (cost=0.00..0.28 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=2646)"
" Index Cond: (pt.id = ail.product_id)"
" -> Materialize (cost=0.00..1.14 rows=9 width=26) (actual time=0.000..0.001 rows=9 loops=2646)"
" -> Seq Scan on product_uom u (cost=0.00..1.09 rows=9 width=26) (actual time=0.004..0.010 rows=9 loops=1)"
" -> Materialize (cost=0.00..1416063.69 rows=1565388 width=102) (actual time=1.818..20416.193 rows=1264 loops=1)"
" -> Nested Loop (cost=0.00..1412150.22 rows=1565388 width=102) (actual time=1.812..20415.160 rows=445 loops=1)"
" Join Filter: (SubPlan 6)"
" -> Index Scan using account_invoice_pkey on account_invoice ai (cost=0.00..145.14 rows=354 width=92) (actual time=0.867..4.586 rows=445 loops=1)"
" Filter: ((date_invoice <= '2012-05-18'::date) AND (date_invoice >= '2012-01-01'::date) AND (((state)::text <> ALL ('{draft,cancel}'::text[])) OR (state IS NULL)) AND (((type)::text = 'out_invoice'::text) OR ((type)::text = 'out_refund'::text)))"
" -> Materialize (cost=0.00..206.66 rows=8844 width=18) (actual time=0.000..0.798 rows=8763 loops=445)"
" -> Seq Scan on res_currency_rate cr (cost=0.00..162.44 rows=8844 width=18) (actual time=0.009..2.100 rows=8763 loops=1)"
" SubPlan 6"
" -> Limit (cost=0.01..0.86 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=3899535)"
" -> Result (cost=0.01..184.56 rows=216 width=4) (actual time=0.004..0.004 rows=1 loops=3899535)"
" One-Time Filter: ((($3 IS NOT NULL) AND ($4 <= $3)) OR (($3 IS NULL) AND ($4 <= now())))"
" -> Seq Scan on res_currency_rate cr2 (cost=0.01..184.56 rows=216 width=4) (actual time=0.007..0.007 rows=1 loops=2354355)"
" Filter: (currency_id = $2)"
" SubPlan 1"
" -> Seq Scan on product_uom (cost=0.00..1.14 rows=1 width=3) (never executed)"
" Filter: (((uom_type)::text = 'reference'::text) AND (category_id = $0))"
" SubPlan 2"
" -> Aggregate (cost=16.62..16.63 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=1257)"
" -> Nested Loop (cost=0.00..16.61 rows=2 width=4) (actual time=0.006..0.007 rows=4 loops=1257)"
" -> Index Scan using account_invoice_pkey on account_invoice a (cost=0.00..8.27 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1257)"
" Index Cond: (id = $1)"
" -> Index Scan using account_invoice_line_invoice_id_index on account_invoice_line l (cost=0.00..8.32 rows=2 width=8) (actual time=0.002..0.002 rows=4 loops=1257)"
" Index Cond: (l.invoice_id = $1)"
" SubPlan 3"
" -> Aggregate (cost=26.11..26.14 rows=1 width=12) (actual time=0.054..0.054 rows=1 loops=1265)"
" -> Nested Loop Left Join (cost=0.00..26.10 rows=6 width=12) (actual time=0.008..0.022 rows=22 loops=1265)"
" Join Filter: (a.id = l.invoice_id)"
" -> Nested Loop (cost=0.00..17.68 rows=3 width=8) (actual time=0.005..0.009 rows=5 loops=1265)"
" -> Index Scan using account_invoice_pkey on account_invoice a (cost=0.00..8.27 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1265)"
" Index Cond: (id = $1)"
" -> Index Scan using account_move_line_move_id_index on account_move_line aml (cost=0.00..9.38 rows=3 width=8) (actual time=0.002..0.003 rows=5 loops=1265)"
" Index Cond: (aml.move_id = a.move_id)"
" -> Materialize (cost=0.00..8.33 rows=2 width=12) (actual time=0.001..0.001 rows=4 loops=6590)"
" -> Index Scan using account_invoice_line_invoice_id_index on account_invoice_line l (cost=0.00..8.32 rows=2 width=12) (actual time=0.002..0.003 rows=4 loops=1260)"
" Index Cond: (invoice_id = $1)"
" SubPlan 4"
" -> Aggregate (cost=26.11..26.14 rows=1 width=8) (actual time=0.037..0.037 rows=1 loops=1265)"
" -> Nested Loop Left Join (cost=0.00..26.10 rows=6 width=8) (actual time=0.009..0.021 rows=22 loops=1265)"
" Join Filter: (a.id = l.invoice_id)"
" -> Nested Loop (cost=0.00..17.68 rows=3 width=12) (actual time=0.005..0.008 rows=5 loops=1265)"
" -> Index Scan using account_invoice_pkey on account_invoice a (cost=0.00..8.27 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=1265)"
" Index Cond: (id = $1)"
" -> Index Scan using account_move_line_move_id_index on account_move_line aml (cost=0.00..9.38 rows=3 width=4) (actual time=0.002..0.004 rows=5 loops=1265)"
" Index Cond: (aml.move_id = a.move_id)"
" -> Materialize (cost=0.00..8.33 rows=2 width=4) (actual time=0.001..0.001 rows=4 loops=6590)"
" -> Index Scan using account_invoice_line_invoice_id_index on account_invoice_line l (cost=0.00..8.32 rows=2 width=4) (actual time=0.002..0.004 rows=4 loops=1260)"
" Index Cond: (invoice_id = $1)"
" SubPlan 5"
" -> Aggregate (cost=16.62..16.63 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=1257)"
" -> Nested Loop (cost=0.00..16.61 rows=2 width=4) (actual time=0.004..0.006 rows=4 loops=1257)"
" -> Index Scan using account_invoice_pkey on account_invoice a (cost=0.00..8.27 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1257)"
" Index Cond: (id = $1)"
" -> Index Scan using account_invoice_line_invoice_id_index on account_invoice_line l (cost=0.00..8.32 rows=2 width=8) (actual time=0.001..0.002 rows=4 loops=1257)"
" Index Cond: (l.invoice_id = $1)"
"Total runtime: 20621.529 ms"
Let's try to understand what is going on.
account_invoice_report is a view, which definition is:
-- View: account_invoice_report
CREATE OR REPLACE VIEW account_invoice_report AS
SELECT min(ail.id) AS id, ai.date_invoice AS date, to_char(ai.date_invoice::timestamp with time zone, 'YYYY'::text) AS year, to_char(ai.date_invoice::timestamp with time zone, 'MM'::text) AS month, to_char(ai.date_invoice::timestamp with time zone, 'YYYY-MM-DD'::text) AS day, ail.product_id, ai.partner_id, ai.payment_term, ai.period_id,
CASE
WHEN u.uom_type::text <> 'reference'::text THEN ( SELECT product_uom.name
FROM product_uom
WHERE product_uom.uom_type::text = 'reference'::text AND product_uom.category_id = u.category_id)
ELSE u.name
END AS uom_name, ai.currency_id, ai.journal_id, ai.fiscal_position, ai.user_id, ai.company_id, count(ail.*) AS nbr, ai.type, ai.state, pt.categ_id, ai.date_due, ai.address_contact_id, ai.address_invoice_id, ai.account_id, ai.partner_bank_id, sum(
CASE
WHEN ai.type::text = ANY (ARRAY['out_refund'::character varying, 'in_invoice'::character varying]::text[]) THEN ail.quantity / u.factor::double precision * (-1)::double precision
ELSE ail.quantity / u.factor::double precision
END) AS product_qty, sum(
CASE
WHEN ai.type::text = ANY (ARRAY['out_refund'::character varying, 'in_invoice'::character varying]::text[]) THEN ail.quantity * ail.price_unit::double precision * (-1)::double precision
ELSE ail.quantity * ail.price_unit::double precision
END) / cr.rate::double precision AS price_total, sum(
CASE
WHEN ai.type::text = ANY (ARRAY['out_refund'::character varying, 'in_invoice'::character varying]::text[]) THEN ai.amount_total * (-1)::numeric
ELSE ai.amount_total
END) / (( SELECT count(l.id) AS count
FROM account_invoice_line l
LEFT JOIN account_invoice a ON a.id = l.invoice_id
WHERE a.id = ai.id))::numeric / cr.rate AS price_total_tax,
CASE
WHEN ai.type::text = ANY (ARRAY['out_refund'::character varying, 'in_invoice'::character varying]::text[]) THEN sum(ail.quantity * ail.price_unit::double precision * (-1)::double precision)
ELSE sum(ail.quantity * ail.price_unit::double precision)
END /
CASE
WHEN ai.type::text = ANY (ARRAY['out_refund'::character varying, 'in_invoice'::character varying]::text[]) THEN sum(ail.quantity / u.factor::double precision * (-1)::double precision)
ELSE sum(ail.quantity / u.factor::double precision)
END / cr.rate::double precision AS price_average, cr.rate AS currency_rate, sum(( SELECT date_part('epoch'::text, avg(date_trunc('day'::text, aml.date_created::timestamp with time zone) - date_trunc('day'::text, l.create_date)::timestamp with time zone)) / (24 * 60 * 60)::numeric(16,2)::double precision
FROM account_move_line aml
LEFT JOIN account_invoice a ON a.move_id = aml.move_id
LEFT JOIN account_invoice_line l ON a.id = l.invoice_id
WHERE a.id = ai.id)) AS delay_to_pay, sum(( SELECT date_part('epoch'::text, avg(date_trunc('day'::text, a.date_due::timestamp with time zone) - date_trunc('day'::text, a.date_invoice::timestamp with time zone))) / (24 * 60 * 60)::numeric(16,2)::double precision
FROM account_move_line aml
LEFT JOIN account_invoice a ON a.move_id = aml.move_id
LEFT JOIN account_invoice_line l ON a.id = l.invoice_id
WHERE a.id = ai.id)) AS due_delay,
CASE
WHEN ai.type::text = ANY (ARRAY['out_refund'::character varying, 'in_invoice'::character varying]::text[]) THEN ai.residual * (-1)::numeric
ELSE ai.residual
END / (( SELECT count(l.id) AS count
FROM account_invoice_line l
LEFT JOIN account_invoice a ON a.id = l.invoice_id
WHERE a.id = ai.id))::numeric / cr.rate AS residual
FROM account_invoice_line ail
LEFT JOIN account_invoice ai ON ai.id = ail.invoice_id
LEFT JOIN product_template pt ON pt.id = ail.product_id
LEFT JOIN product_uom u ON u.id = ail.uos_id, res_currency_rate cr
WHERE (cr.id IN ( SELECT cr2.id
FROM res_currency_rate cr2
WHERE cr2.currency_id = ai.currency_id AND (ai.date_invoice IS NOT NULL AND cr.name <= ai.date_invoice OR ai.date_invoice IS NULL AND cr.name <= now())
LIMIT 1))
GROUP BY ail.product_id, ai.date_invoice, ai.id, cr.rate, to_char(ai.date_invoice::timestamp with time zone, 'YYYY'::text), to_char(ai.date_invoice::timestamp with time zone, 'MM'::text), to_char(ai.date_invoice::timestamp with time zone, 'YYYY-MM-DD'::text), ai.partner_id, ai.payment_term, ai.period_id, u.name, ai.currency_id, ai.journal_id, ai.fiscal_position, ai.user_id, ai.company_id, ai.type, ai.state, pt.categ_id, ai.date_due, ai.address_contact_id, ai.address_invoice_id, ai.account_id, ai.partner_bank_id, ai.residual, ai.amount_total, u.uom_type, u.category_id;
ALTER TABLE account_invoice_report
OWNER TO openerp;
First, there are design problems in the view.
1) In a view, you never use conversion to cast a type.
to_char(ai.date_invoice::timestamp with time zone, 'YYYY'::text) AS year
In PostgreSQL, casting is done automatically in queries, not views.
2) use of case ...
will make the database run sequential scans.
This should be replaced either with a UNION/INTERSEC or temporary table.
There is no possibility to make this query run better.
This is really an incledible query, as it tries to achieve something
relatively easy but breaks nearly ALL SQL optimization rules.
--
You received this bug notification because you are a member of OpenERP
Accounting Experts, which is subscribed to a duplicate bug report
(1021321).
https://bugs.launchpad.net/bugs/1000195
Title:
Performance issue in invoice analysis
Status in OpenERP Addons (modules):
Confirmed
Bug description:
The sale-module produces a lot of selects to the
account_invoice_report database view. I think its one of the widget in
the dashboard that does this. Anyway the performance are lousy and a
ticking bomb for many 6.1 installations.
This select takes 8000 ms with 450 invoices in the database
SELECT month,price_total FROM "account_invoice_report" WHERE account_invoice_report.id IN (450 ids)
The company creats over 200 000 invoices per year, which was OK with
series 5 of OpenERP. It will not be possible now after upgrade to 6.1.
There are many installations who creates more than 400 invoices per
year, many that creats 400 invoinces per day.
I think we need a temporary table or something instead of this
database view. Every time a salesman opens his saleview he hits this
problem.
To manage notifications about this bug go to:
https://bugs.launchpad.net/openobject-addons/+bug/1000195/+subscriptions
