Re: Sort is generating rows

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.

SELECT min(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,
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
JOIN res_partner partner ON s.partner_id = partner.id
LEFT JOIN product_product p ON l.product_id = p.id
LEFT JOIN product_template t ON p.product_tmpl_id = t.id
LEFT JOIN uom_uom u ON u.id = l.product_uom
LEFT JOIN uom_uom u2 ON u2.id = t.uom_id
JOIN product_pricelist pp ON s.pricelist_id = 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,
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,
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, 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, 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)
-> 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)
-> 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)
-> 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)
-> 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)
-> 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.

2018-06-01 0:32 GMT+02:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>:

> On 05/31/2018 02:09 AM, Nicolas Seinlet wrote:
>
>> Hi,
>>
>> I have a query with a strange query plan.
>>
>> This query is roughly searching for sales, and convert them with a
>> currency rate. As currency rate changes from time to time, table contains
>> the currency, the company, the rate, the start date of availability of this
>> rate and the end date of availability.
>>
>
> My guess is to get a complete answer you are going to need to provide:
>
> 1) The complete query.
>
> 2) The complete EXPLAIN ANALYZE.
>
> More comments inline below.
>
>
>> The join is done using :
>> left join currency_rate cr on (cr.currency_id = pp.currency_id and
>> cr.company_id = s.company_id and
>> cr.date_start <= coalesce(s.date_order, now()) and
>> (cr.date_end is null or cr.date_end > coalesce(s.date_order,
>> now())))
>>
>> The tricky part is the date range on the currency rate, which is not an
>> equality.
>>
>
>
>> My question is then , is there a better way to join a table to another
>> using a date range, knowing that there's no overlap between date ranges?
>>
>
> Use date ranges?:
>
> https://www.postgresql.org/docs/10/static/functions-range.html
>
>
>
> Should we generate a virtual table with rates for all dates, and joining
>> using an equality?
>>
>> For now, the more currency rates, the slowest the query. There's not that
>> much currency rates (1k in this case), as you can only have one rate per
>> day per currency.
>>
>> Have a nice day,
>>
>> Nicolas.
>>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>