openerp-india team mailing list archive

[Kellogs <jm@xxxxxxxxx>]

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