Re: Sort is generating rows - Mailing list pgsql-general

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@aklaver.com