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