openerp-india team mailing list archive
-
openerp-india team
-
Mailing list archive
-
Message #13465
[Bug 1000195] Re: Performance issue in invoice analysis
EXPLAIN ANALYSE
SELECT *
FROM account_invoice_report
LIMIT 1
returns a result in ... 110 seconds.
If this is a usual design problem in OpenERP, I would suggest that you invest in SSD disc and boost PostgreSQL memory.
When a database has a bad designe, it is always necessary to run SSD disc to make sequential scans much faster.
The result of the EXPLAIN ANALYSE:
"Limit (cost=16545936.29..16546023.23 rows=1 width=554) (actual time=110458.111..110458.111 rows=1 loops=1)"
" -> Subquery Scan on account_invoice_report (cost=16545936.29..1202110162.52 rows=13637448 width=554) (actual time=110458.109..110458.109 rows=1 loops=1)"
" -> GroupAggregate (cost=16545936.29..1201973788.04 rows=13637448 width=184) (actual time=110458.107..110458.107 rows=1 loops=1)"
" -> Sort (cost=16545936.29..16580029.91 rows=13637448 width=184) (actual time=110457.872..110457.873 rows=2 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: 1660kB"
" -> Nested Loop (cost=0.00..12505977.72 rows=13637448 width=184) (actual time=285.840..110434.834 rows=3032 loops=1)"
" Join Filter: (SubPlan 6)"
" -> Nested Loop Left Join (cost=0.00..1642.35 rows=3084 width=174) (actual time=0.093..71.748 rows=3085 loops=1)"
" -> Nested Loop Left Join (cost=0.00..754.58 rows=3084 width=170) (actual time=0.081..39.953 rows=3085 loops=1)"
" Join Filter: (u.id = ail.uos_id)"
" -> Merge Right Join (cost=0.00..337.13 rows=3084 width=152) (actual time=0.063..27.645 rows=3085 loops=1)"
" Merge Cond: (ai.id = ail.invoice_id)"
" -> Index Scan using account_invoice_pkey on account_invoice ai (cost=0.00..125.44 rows=1576 width=92) (actual time=0.021..2.644 rows=1578 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.024..9.737 rows=3085 loops=1)"
" -> Materialize (cost=0.00..1.14 rows=9 width=26) (actual time=0.000..0.001 rows=9 loops=3085)"
" -> Seq Scan on product_uom u (cost=0.00..1.09 rows=9 width=26) (actual time=0.004..0.008 rows=9 loops=1)"
" -> Index Scan using product_template_pkey on product_template pt (cost=0.00..0.28 rows=1 width=8) (actual time=0.005..0.008 rows=1 loops=3085)"
" Index Cond: (pt.id = ail.product_id)"
" -> Materialize (cost=0.00..206.66 rows=8844 width=18) (actual time=0.000..0.759 rows=8763 loops=3085)"
" -> Seq Scan on res_currency_rate cr (cost=0.00..162.44 rows=8844 width=18) (actual time=0.008..2.570 rows=8763 loops=1)"
" SubPlan 6"
" -> Limit (cost=0.01..0.86 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=27033855)"
" -> Result (cost=0.01..184.56 rows=216 width=4) (actual time=0.003..0.003 rows=0 loops=27033855)"
" 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=12014463)"
" 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.012..0.012 rows=1 loops=1)"
" -> Nested Loop (cost=0.00..16.61 rows=2 width=4) (actual time=0.008..0.009 rows=2 loops=1)"
" -> Index Scan using account_invoice_pkey on account_invoice a (cost=0.00..8.27 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=1)"
" 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.003 rows=2 loops=1)"
" Index Cond: (l.invoice_id = $1)"
" SubPlan 3"
" -> Aggregate (cost=26.11..26.14 rows=1 width=12) (actual time=0.059..0.059 rows=1 loops=1)"
" -> Nested Loop Left Join (cost=0.00..26.10 rows=6 width=12) (actual time=0.024..0.033 rows=6 loops=1)"
" Join Filter: (a.id = l.invoice_id)"
" -> Nested Loop (cost=0.00..17.68 rows=3 width=8) (actual time=0.008..0.011 rows=3 loops=1)"
" -> Index Scan using account_invoice_pkey on account_invoice a (cost=0.00..8.27 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=1)"
" 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.001..0.003 rows=3 loops=1)"
" Index Cond: (aml.move_id = a.move_id)"
" -> Materialize (cost=0.00..8.33 rows=2 width=12) (actual time=0.005..0.005 rows=2 loops=3)"
" -> 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.003..0.004 rows=2 loops=1)"
" Index Cond: (invoice_id = $1)"
" SubPlan 4"
" -> Aggregate (cost=26.11..26.14 rows=1 width=8) (actual time=0.078..0.078 rows=1 loops=1)"
" -> Nested Loop Left Join (cost=0.00..26.10 rows=6 width=8) (actual time=0.044..0.054 rows=6 loops=1)"
" Join Filter: (a.id = l.invoice_id)"
" -> Nested Loop (cost=0.00..17.68 rows=3 width=12) (actual time=0.026..0.029 rows=3 loops=1)"
" -> Index Scan using account_invoice_pkey on account_invoice a (cost=0.00..8.27 rows=1 width=16) (actual time=0.014..0.014 rows=1 loops=1)"
" 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.007..0.010 rows=3 loops=1)"
" Index Cond: (aml.move_id = a.move_id)"
" -> Materialize (cost=0.00..8.33 rows=2 width=4) (actual time=0.003..0.004 rows=2 loops=3)"
" -> 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.007..0.008 rows=2 loops=1)"
" Index Cond: (invoice_id = $1)"
" SubPlan 5"
" -> Aggregate (cost=16.62..16.63 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=1)"
" -> Nested Loop (cost=0.00..16.61 rows=2 width=4) (actual time=0.006..0.007 rows=2 loops=1)"
" -> 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=1)"
" 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.001 rows=2 loops=1)"
" Index Cond: (l.invoice_id = $1)"
"Total runtime: 110459.471 ms"
The query is running slow because of
GROUP BY .... 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)
This obliges PostgreSQL to run a huge sequential scan on each query,
just to build the data corresponding to yyyy (year), mm (month), etc ...
I suspect if this was hard-coded in the table, the query would run
faster.
--
You received this bug notification because you are a member of OpenERP
Indian Team, which is subscribed to OpenERP Addons.
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
References