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