Re: Sort is generating rows

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