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@xxxxxxxxxxx
