On 06/01/2018 02:36 AM, Nicolas Seinlet wrote:
Hi,
thanks for the answer. The query is based on a view, so here are the
view, the query as well as the query plan.
I've already taken into account remarks like date ranges.
You changed the query from the original, besides just adding the
daterange, I see an addition of a LATERAL, where there other changes?
The changes did eliminate the 300 million line sort from what I can see.
The new query takes ~9 secs is that an improvement over the old?
I took the liberty of running the EXPLAIN ANALYZE through
explain.depesz.com:
https://explain.depesz.com/s/9thl
The largest amount of time was in the Index Scan(8,706.712ms) and that
was because the scan was looped 32,732 times. I have not used LATERAL in
my own code so I looked it up:
https://www.postgresql.org/docs/10/static/sql-select.html
LATERAL
"
...
When a FROM item contains LATERAL cross-references, evaluation proceeds
as follows: for each row of the FROM item providing the cross-referenced
column(s), or set of rows of multiple FROM items providing the columns,
the LATERAL item is evaluated using that row or row set's values of the
columns. The resulting row(s) are joined as usual with the rows they
were computed from. This is repeated for each row or set of rows from
the column source table(s).
...
"
If I am following correctly that might explain some of looping seen above.
SELECT min(l.id <http://l.id>) AS id,
l.product_id,
t.uom_id AS product_uom,
sum(l.product_uom_qty / u.factor * u2.factor) AS product_uom_qty,
sum(l.qty_delivered / u.factor * u2.factor) AS qty_delivered,
sum(l.qty_invoiced / u.factor * u2.factor) AS qty_invoiced,
sum(l.qty_to_invoice / u.factor * u2.factor) AS qty_to_invoice,
sum(l.price_total / COALESCE(cr.rate, 1.0)) AS price_total,
sum(l.price_subtotal / COALESCE(cr.rate, 1.0)) AS price_subtotal,
sum(l.price_reduce * l.qty_to_invoice / COALESCE(cr.rate, 1.0)) AS
amount_to_invoice,
sum(l.price_reduce * l.qty_invoiced / COALESCE(cr.rate, 1.0)) AS
amount_invoiced,
count(*) AS nbr,
s.name <http://s.name>,
s.date_order AS date,
s.confirmation_date,
s.state,
s.partner_id,
s.user_id,
s.company_id,
date_part('epoch'::text, avg(date_trunc('day'::text, s.date_order)
- date_trunc('day'::text, s.create_date))) / (24 * 60 *
60)::numeric(16,2)::double precision AS delay,
t.categ_id,
s.pricelist_id,
s.analytic_account_id,
s.team_id,
p.product_tmpl_id,
partner.country_id,
partner.commercial_partner_id,
sum(p.weight * l.product_uom_qty / u.factor * u2.factor) AS weight,
sum(p.volume * l.product_uom_qty::double precision /
u.factor::double precision * u2.factor::double precision) AS volume
FROM sale_order_line l
JOIN sale_order s ON l.order_id = s.id <http://s.id>
JOIN res_partner partner ON s.partner_id = partner.id
<http://partner.id>
LEFT JOIN product_product p ON l.product_id = p.id <http://p.id>
LEFT JOIN product_template t ON p.product_tmpl_id = t.id <http://t.id>
LEFT JOIN uom_uom u ON u.id <http://u.id> = l.product_uom
LEFT JOIN uom_uom u2 ON u2.id <http://u2.id> = t.uom_id
JOIN product_pricelist pp ON s.pricelist_id = pp.id <http://pp.id>
LEFT JOIN LATERAL ( SELECT res_currency_rate.rate
FROM res_currency_rate
WHERE res_currency_rate.currency_id = pp.currency_id AND
(res_currency_rate.company_id = s.company_id OR
res_currency_rate.company_id IS NULL) AND
daterange(res_currency_rate.name <http://res_currency_rate.name>,
COALESCE(res_currency_rate.date_end, now()::date)) @>
COALESCE(s.date_order::timestamp with time zone, now())::date
LIMIT 1) cr ON true
GROUP BY l.product_id, l.order_id, t.uom_id, t.categ_id, s.name
<http://s.name>, s.date_order, s.confirmation_date, s.partner_id,
s.user_id, s.state, s.company_id, s.pricelist_id, s.analytic_account_id,
s.team_id, p.product_tmpl_id, partner.country_id,
partner.commercial_partner_id;
explain analyse select team_id,partner_id,sum(price_total) from
sale_report group by team_id,partner_id;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=1344575.91..1344986.97 rows=3654 width=40)
(actual time=8934.915..8944.487 rows=43 loops=1)
Group Key: sale_report.team_id, sale_report.partner_id
-> Sort (cost=1344575.91..1344667.26 rows=36539 width=40) (actual
time=8934.686..8937.833 rows=32732 loops=1)
Sort Key: sale_report.team_id, sale_report.partner_id
Sort Method: quicksort Memory: 3323kB
-> Subquery Scan on sale_report (cost=1339157.70..1341806.77
rows=36539 width=40) (actual time=8870.269..8923.114 rows=32732 loops=1)
-> GroupAggregate (cost=1339157.70..1341441.38
rows=36539 width=395) (actual time=8870.268..8920.155 rows=32732 loops=1)
Group Key: l.product_id, l.order_id, t.uom_id,
t.categ_id, s.name <http://s.name>, s.date_order, s.confirmation_date,
s.partner_id, s.user_id, s.state, s.company_id, s.pricelist_id,
s.analytic_account_id, s.team_id, p.product_tmpl_id, partner.country_id,
partner.commercial_partner_id
-> Sort (cost=1339157.70..1339249.04 rows=36539
width=92) (actual time=8870.247..8875.191 rows=32732 loops=1)
Sort Key: l.product_id, l.order_id,
t.uom_id, t.categ_id, s.name <http://s.name>, s.date_order,
s.confirmation_date, s.partner_id, s.user_id, s.state, s.company_id,
s.pricelist_id, s.analytic_account_id, s.team_id, p.product_tmpl_id,
partner.country_id, partner.commercial_partner_id
Sort Method: quicksort Memory: 5371kB
-> Nested Loop Left Join
(cost=695.71..1336388.56 rows=36539 width=92) (actual
time=13.468..8797.655 rows=32732 loops=1)
-> Hash Left Join
(cost=695.43..3338.19 rows=36539 width=88) (actual time=13.323..65.600
rows=32732 loops=1)
Hash Cond: (l.product_id = p.id
<http://p.id>)
-> Hash Join
(cost=656.36..2796.71 rows=36539 width=76) (actual time=13.236..49.047
rows=32732 loops=1)
Hash Cond: (l.order_id =
s.id <http://s.id>)
-> Seq Scan on
sale_order_line l (cost=0.00..1673.39 rows=36539 width=17) (actual
time=0.019..7.338 rows=32732 loops=1)
-> Hash
(cost=550.72..550.72 rows=8451 width=67) (actual time=13.184..13.184
rows=8382 loops=1)
Buckets: 16384
Batches: 1 Memory Usage: 947kB
-> Hash Join
(cost=37.69..550.72 rows=8451 width=67) (actual time=0.164..10.135
rows=8382 loops=1)
Hash Cond:
(s.pricelist_id = pp.id <http://pp.id>)
-> Hash Join
(cost=13.97..420.42 rows=8451 width=63) (actual time=0.151..7.064
rows=8382 loops=1)
Hash
Cond: (s.partner_id = partner.id <http://partner.id>)
-> Seq
Scan on sale_order s (cost=0.00..301.51 rows=8451 width=55) (actual
time=0.005..1.807 rows=8382 loops=1)
->
Hash (cost=13.43..13.43 rows=43 width=12) (actual time=0.136..0.136
rows=43 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on res_partner partner (cost=0.00..13.43 rows=43
width=12) (actual time=0.013..0.112 rows=43 loops=1)
-> Hash
(cost=16.10..16.10 rows=610 width=8) (actual time=0.007..0.007 rows=1
loops=1)
Buckets:
1024 Batches: 1 Memory Usage: 9kB
-> Seq
Scan on product_pricelist pp (cost=0.00..16.10 rows=610 width=8)
(actual time=0.005..0.005 rows=1 loops=1)
-> Hash (cost=32.95..32.95
rows=490 width=16) (actual time=0.076..0.076 rows=43 loops=1)
Buckets: 1024 Batches: 1
Memory Usage: 11kB
-> Hash Left Join
(cost=11.88..32.95 rows=490 width=16) (actual time=0.051..0.068 rows=43
loops=1)
Hash Cond:
(p.product_tmpl_id = t.id <http://t.id>)
-> Seq Scan on
product_product p (cost=0.00..14.90 rows=490 width=8) (actual
time=0.007..0.010 rows=43 loops=1)
-> Hash
(cost=11.39..11.39 rows=39 width=12) (actual time=0.039..0.039 rows=39
loops=1)
Buckets: 1024
Batches: 1 Memory Usage: 10kB
-> Seq Scan
on product_template t (cost=0.00..11.39 rows=39 width=12) (actual
time=0.006..0.030 rows=39 loops=1)
-> Limit (cost=0.28..36.46 rows=1
width=8) (actual time=0.266..0.266 rows=1 loops=32732)
-> Index Scan using
res_currency_rate_currency_id_index on res_currency_rate
(cost=0.28..36.46 rows=1 width=8) (actual time=0.266..0.266 rows=1
loops=32732)
Index Cond: (currency_id =
pp.currency_id)
Filter: (((company_id =
s.company_id) OR (company_id IS NULL)) AND (daterange(name,
COALESCE(date_end, (now())::date)) @>
(COALESCE((s.date_order)::timestamp with time zone, now()))::date))
Rows Removed by Filter: 502
Planning time: 5.731 ms
Execution time: 8944.950 ms
(45 rows)
Have a nice day,
Nicolas.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
