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 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 _______________________________________________ Mailing list: https://launchpad.net/~openerp-expert-accounting Post to : [email protected] Unsubscribe : https://launchpad.net/~openerp-expert-accounting More help : https://help.launchpad.net/ListHelp
