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

On 06/05/2018 07:58 AM, Nicolas Seinlet wrote:
> Hi,
> 
> I've tried some tests, by generating various datas in 
> the res_currency_rate table.
> 
> If I generate res_currency_rate rows for unsused currencies, this 
> doesn't influence the execution time.
> if I generate more res_currency_rate for used currencies, this slower 
> the query.
> If I generate 100 rates, on a one per day basis, I get an execution time 
> of 4.5 seconds
> If I generate 100 rates, on a one per 3 days basis, execution time drops 
> to 4 seconds
> If I generate 100 rates, on a one per 6 days basis, execution time drops 
> to 3.8 seconds.

I took another look at the query and got to wondering about the snippet 
below:


LEFT JOIN (SELECT
                 r.currency_id, COALESCE(r.company_id, c.id) AS 
company_id, r.rate,
                 r.name AS date_start, r.date_end
            FROM
                 res_currency_rate r
            JOIN
                 res_company c
            ON
                 r.company_id IS NULL OR r.company_id = c.id
            ) cr
ON
     cr.currency_id = pp.currency_id
AND
     (cr.company_id = s.company_id OR cr.company_id IS NULL)
AND
     daterange(cr.date_start, COALESCE(cr.date_end, now()::date)) @> 
COALESCE(s.date_order::timestamp with time zone, now())::date

In particular:

FROM
                 res_currency_rate r
            JOIN
                 res_company c
            ON
                 r.company_id IS NULL OR r.company_id = c.id

Are there NULL company_id values in res_currency_rate?

If so I am trying to figure out how the JOIN to res_company would work 
in that situation.

What happens if eliminate the r.company_id IS NULL?

> 
> I've executed following tests many times, to avoid cache or buffers 
> related issues, each time after vacuuming table, ...
> 
> The execution time with a join of type daterange :
> 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>, 
> res_currency_rate.date_end) @> COALESCE(s.date_order::timestamp with 
> time zone, now())::date
> 
> is slower than the date comparison equivalent:
> 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 res_currency_rate.name 
> <http://res_currency_rate.name> <= COALESCE(s.date_order::timestamp with 
> time zone, now()) AND (res_currency_rate.date_end IS NULL OR 
> res_currency_rate.date_end > COALESCE(s.date_order::timestamp with time 
> zone, now()))
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com