← Back to team overview

openerp-india team mailing list archive

[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
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